directory tree query with big planner variation

Hi group,

this is a directory tree query for a backup system (http://
sourceforge.net/projects/bacula).
You provide a path and get back the names of the children plus a
boolean telling if the child has itself children.
The "%@" stands for the initial path:
---------------------------------------------------------------
explain analyze SELECT X.name AS name, COUNT(CH) > 1 AS children
FROM
( SELECT RTRIM( REPLACE( NLPC.path, '%@/', ''),'/') AS name,
FN.name AS CH
FROM
( SELECT P.path,P.pathid
FROM bacula.path P
WHERE P.path ~ '^%@/[^/]*/$' ) AS NLPC
LEFT OUTER JOIN bacula.file F
ON
NLPC.pathid = F.pathid
LEFT OUTER JOIN bacula.filename FN
ON
F.filenameid = FN.filenameid
GROUP BY NLPC.path, FN.name
UNION
SELECT FN.name AS name, FN.name AS CH
FROM
bacula.path P, bacula.file F, bacula.filename FN
WHERE
P.path = '%@/' AND
P.pathid = F.pathid AND
F.filenameid = FN.filenameid
) AS X
WHERE X.name <> ''
GROUP BY X.name
---------------------------------------------------------------
The 1st part of the union takes care of directories, the 2nd one of
flat files.
Application allows user navigation in a browser (clicking on a name
in one column triggers the query and fills the next browser column).
Initial path of "/Users/axel/Library/Preferences" results in:
---------------------------------------------------------------
Sort (cost=1295.24..1295.47 rows=92 width=64) (actual
time=818.987..819.871 rows=527 loops=1)
Sort Key: upper(x.name)
-> GroupAggregate (cost=1288.56..1292.24 rows=92 width=64)
(actual time=784.069..814.059 rows=527 loops=1)
-> Unique (cost=1288.56..1289.25 rows=92 width=112)
(actual time=783.931..809.708 rows=684 loops=1)
-> Sort (cost=1288.56..1288.79 rows=92 width=112)
(actual time=783.921..793.150 rows=5350 loops=1)
Sort Key: name, ch
-> Append (cost=642.03..1285.55 rows=92
width=112) (actual time=335.134..723.917 rows=5350 loops=1)
-> Subquery Scan "*SELECT*
1" (cost=642.03..643.18 rows=46 width=112) (actual
time=335.130..338.564 rows=184 loops=1)
-> HashAggregate
(cost=642.03..642.72 rows=46 width=112) (actual time=335.121..337.843
rows=184 loops=1)
-> Nested Loop Left Join
(cost=2.00..641.80 rows=46 width=112) (actual time=39.293..326.831
rows=1685 loops=1)
-> Nested Loop Left
Join (cost=0.00..502.63 rows=46 width=97) (actual
time=21.026..202.206 rows=1685 loops=1)
-> 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))
-> Index Scan
using file_path_idx on file f (cost=0.00..493.28 rows=506 width=8)
(actual time=0.473..5.119 rows=62 loops=27)
Index Cond:
("outer".pathid = f.pathid)
-> Bitmap Heap Scan on
filename fn (cost=2.00..3.01 rows=1 width=23) (actual
time=0.058..0.061 rows=1 loops=1685)
Recheck Cond:
("outer".filenameid = fn.filenameid)
-> Bitmap Index
Scan on filename_pkey (cost=0.00..2.00 rows=1 width=0) (actual
time=0.030..0.030 rows=1 loops=1685)
Index Cond:
("outer".filenameid = fn.filenameid)
-> Nested Loop (cost=2.00..641.91
rows=46 width=19) (actual time=3.349..377.758 rows=5166 loops=1)
-> Nested Loop (cost=0.00..502.62
rows=46 width=4) (actual time=3.118..97.375 rows=5200 loops=1)
-> Index Scan using
path_name_idx on path p (cost=0.00..3.01 rows=1 width=4) (actual
time=0.045..0.052 rows=1 loops=1)
Index Cond: (path = '/
Users/axel/Library/Preferences/'::text)
-> Index Scan using
file_path_idx on file f (cost=0.00..493.28 rows=506 width=8) (actual
time=3.058..76.014 rows=5200 loops=1)
Index Cond:
("outer".pathid = f.pathid)
-> Bitmap Heap Scan on filename
fn (cost=2.00..3.02 rows=1 width=23) (actual time=0.037..0.039
rows=1 loops=5200)
Recheck Cond:
("outer".filenameid = fn.filenameid)
Filter: (name <> ''::text)
-> Bitmap Index Scan on
filename_pkey (cost=0.00..2.00 rows=1 width=0) (actual
time=0.018..0.018 rows=1 loops=5200)
Index Cond:
("outer".filenameid = fn.filenameid)
Total runtime: 832.458 ms
---------------------------------------------------------------
which is ok, but initial path of "/Users/axel" results in (which is
not ok):
---------------------------------------------------------------
Sort (cost=125533.67..125534.17 rows=200 width=64) (actual
time=84273.963..84274.260 rows=181 loops=1)
Sort Key: upper(x.name)
-> GroupAggregate (cost=123493.01..125526.03 rows=200 width=64)
(actual time=84263.411..84272.427 rows=181 loops=1)
-> Unique (cost=123493.01..124169.51 rows=90201
width=112) (actual time=84263.215..84270.129 rows=522 loops=1)
-> Sort (cost=123493.01..123718.51 rows=90201
width=112) (actual time=84263.208..84265.632 rows=1432 loops=1)
Sort Key: name, ch
-> Append (cost=113172.83..116069.08
rows=90201 width=112) (actual time=83795.274..84251.660 rows=1432
loops=1)
-> Subquery Scan "*SELECT*
1" (cost=113172.83..115426.71 rows=90155 width=112) (actual
time=83795.270..83803.996 rows=410 loops=1)
-> HashAggregate
(cost=113172.83..114525.16 rows=90155 width=112) (actual
time=83795.258..83802.369 rows=410 loops=1)
-> Hash Left Join
(cost=3124.38..112722.06 rows=90155 width=112) (actual
time=56254.547..83779.903 rows=3648 loops=1)
Hash Cond:
("outer".filenameid = "inner".filenameid)
-> Merge Left Join
(cost=0.00..106216.87 rows=90155 width=97) (actual
time=54926.198..82430.621 rows=3648 loops=1)
Merge Cond:
("outer".pathid = "inner".pathid)
-> 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))
-> Index Scan
using file_path_idx on file f (cost=0.00..95191.99 rows=3020363
width=8) (actual time=17.561..74392.318 rows=2941790 loops=1)
-> Hash
(cost=2723.30..2723.30 rows=160430 width=23) (actual
time=1299.103..1299.103 rows=160430 loops=1)
-> Seq Scan on
filename fn (cost=0.00..2723.30 rows=160430 width=23) (actual
time=3.884..684.918 rows=160430 loops=1)
-> Nested Loop (cost=2.00..641.91
rows=46 width=19) (actual time=93.252..442.196 rows=1022 loops=1)
-> Nested Loop (cost=0.00..502.62
rows=46 width=4) (actual time=49.375..209.694 rows=1050 loops=1)
-> Index Scan using
path_name_idx on path p (cost=0.00..3.01 rows=1 width=4) (actual
time=29.455..29.462 rows=1 loops=1)
Index Cond: (path = '/
Users/axel/'::text)
-> Index Scan using
file_path_idx on file f (cost=0.00..493.28 rows=506 width=8) (actual
time=19.898..175.869 rows=1050 loops=1)
Index Cond:
("outer".pathid = f.pathid)
-> Bitmap Heap Scan on filename
fn (cost=2.00..3.02 rows=1 width=23) (actual time=0.206..0.208
rows=1 loops=1050)
Recheck Cond:
("outer".filenameid = fn.filenameid)
Filter: (name <> ''::text)
-> Bitmap Index Scan on
filename_pkey (cost=0.00..2.00 rows=1 width=0) (actual
time=0.087..0.087 rows=1 loops=1050)
Index Cond:
("outer".filenameid = fn.filenameid)
Total runtime: 84295.927 ms
---------------------------------------------------------------
It happened once that the planner resolved the 2nd query with the 1st
plan, but this is not reproducible.
How can I avoid the 2nd plan?

This is 8.1.4 on OpenBSD 3.9 with 2x1GHz PIII and 2GB.
Axel
Axel Rau, ☀Frankfurt , Germany +49-69-951418-0