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

From Axel Rau
Subject Re: directory tree query with big planner variation
Date
Msg-id FA56C9FF-86FC-4AC5-B442-2C139167A331@Chaos1.DE
Whole thread Raw
In response to Re: directory tree query with big planner variation  (Michael Stone <mstone+postgres@mathom.us>)
Responses Re: directory tree query with big planner variation  (Michael Stone <mstone+postgres@mathom.us>)
List pgsql-performance
Am 31.07.2006 um 13:15 schrieb Michael Stone:

> 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 ~ '^%@/[^/]*/$'
Why does it quite well in this case:
---------------------------------------
->  Index Scan using path_name_idx on path p  (cost=0.00..3.02 rows=1
width=97) (actual time=15.480..56.935 rows=27 loops=1)
       Index Cond: ((path >= '/Users/axel/Library/
Preferences/'::text) AND (path < '/Users/axel/Library/
Preferences0'::text))
       Filter: ((path ~ '^/Users/axel/Library/Preferences/[^/]*/
$'::text) AND (rtrim("replace"(path, '/Users/axel/Library/
Preferences/'::text, ''::text), '/'::text) <> ''::text))
---------------------------------------
as compared to this case(ignoring the index on path):
---------------------------------------
->  Index Scan using path_pkey on path p  (cost=0.00..2567.57
rows=1941 width=97) (actual time=527.805..1521.911 rows=69 loops=1)
       Filter: ((path ~ '^/Users/axel/[^/]*/$'::text) AND (rtrim
("replace"(path, '/Users/axel/'::text, ''::text), '/'::text) <>
''::text))
---------------------------------------
? With all longer path names, I get the above (good)result.
Should I put the rtrim/replace on the client side?
>
> 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.
PATH holds complete pathnames of directories, FILENAME holds
filenames and pathname components.
Currently the schema is the lowest common denominator between SQLite,
MySQL and pg and the bacula people will stay with that (-;).
Axel
Axel Rau, ☀Frankfurt , Germany                       +49-69-951418-0



pgsql-performance by date:

Previous
From: Michael Stone
Date:
Subject: Re: directory tree query with big planner variation
Next
From: Martin Lesser
Date:
Subject: Partitioning / constrain exlusion not working with %-operator