Re: directory tree query with big planner variation

Am 31.07.2006 um 17:21 schrieb Michael Stone:

> On Mon, Jul 31, 2006 at 05:06:00PM +0200, Axel Rau wrote:
>> Please reconsider your proposals with the above
>
> I'm not sure what you're getting at; could you be more specific?
Let's see...

Am 31.07.2006 um 15:30 schrieb Michael Stone:
> And then what happens if you try something like SELECT
> X.name,X.children
> FROM (SELECT [rtrim]P.path,(SELECT count(*) FROM bacula.file F
The file table is the biggest one, because it contains one row per
backup job and file (see my column description).
You need the filename table here.
> WHERE F.pathid = P.pathid
> LIMIT 2) > 1
> FROM bacula.path P
> WHERE P.path ~ '^%@/[^/]*/$'
> UNION
> SELECT FN.name,0
> 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

Tweaking your query and omitting the RTRIM/REPLACE stuff, I get:
-------------------------------
SELECT X.path,X.children
FROM (SELECT P.path,(SELECT count(*) FROM bacula.file F,
bacula.filename FN WHERE F.pathid =
P.pathid AND F.filenameid = FN.filenameid
LIMIT 2) > 1 AS children
FROM bacula.path P
WHERE P.path ~ '^/Users/axel/ports/[^/]*/$'
UNION
SELECT FN.name,0=1
FROM bacula.path P, bacula.file F, bacula.filename FN
WHERE
P.path = '/Users/axel/ports/' AND
P.pathid = F.pathid AND
F.filenameid = FN.filenameid
) AS X
WHERE X.path <> ''
GROUP BY X.path, X.children ;
path | children
------------------------------+----------
.cvsignore | f
/Users/axel/ports/CVS/ | t
/Users/axel/ports/archivers/ | t
INDEX | f
Makefile | f
README | f
(6 rows)

Time: 35.221 ms
-------------------------------
While my version returns:
-------------------------------
name | children
------------+----------
.cvsignore | f
archivers | t
CVS | t
INDEX | f
Makefile | f
README | f
(6 rows)

Time: 30.263 ms
------------+----------
How would you complete your version?

Axel
Axel Rau, ☀Frankfurt , Germany +49-69-951418-0