Skip site navigation (1) Skip section navigation (2)

Re: directory tree query with big planner variation

From: Axel Rau <Axel(dot)Rau(at)Chaos1(dot)DE>
To: Michael Stone <mstone+postgres(at)mathom(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: directory tree query with big planner variation
Date: 2006-07-31 15:54:41
Message-ID: AE101B36-DB2E-4EA5-9F96-A77CD0BC79CB@Chaos1.DE (view raw or flat)
Thread:
Lists: pgsql-performance
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



In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2006-07-31 16:04:26
Subject: Re: PostgreSQL scalability on Sun UltraSparc T1
Previous:From: Merlin MoncureDate: 2006-07-31 15:52:31
Subject: Re: Performances with new Intel Core* processors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group