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