Re: directory tree query with big planner variation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux