Re: directory tree query with big planner variation - Mailing list pgsql-performance

On Mon, Jul 31, 2006 at 12:48:11PM +0200, Axel Rau wrote:
>               WHERE P.path ~ '^%@/[^/]*/$' ) AS NLPC

This can't be indexed. You might try something like
WHERE P.path LIKE '%@%' AND P.path ~ '^%@/[^/]*/$'

The schema could be a lot more intelligent here. (E.g., store path
seperately from file/directory name, store type (file or directory)
seperately, etc.) Without improving the schema I don't think this will
ever be a speed demon.

Mike Stone