From: "Dan Langille" <dan@langille.org>
To: pgsql-sql@postgresql.org
Date: Tue, 26 Nov 2002 00:07:29 -0500
MIME-Version: 1.0
Subject: Re: subselect instead of a view...
Message-ID: <3DE2BB41.18930.D5AA1552@localhost>
In-reply-to: <3DE2A10D.5202.D543B95A@localhost>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

On 25 Nov 2002 at 22:15, Dan Langille wrote:

> I know this can be done better, I just can't figure out how. I keep
> thinking of a subselect but I'm totally blocked.  It must be bed time.

It's odd what reading the paper, relaxing with a book, and then 
trying to sleep can generate.  There I was, almost dropping off, when 
I realised I needed this:

SELECT E.name, EP.pathname, E.id, EP2.pathname, CL.id
   FROM element             E,
        element_pathnames   EP,
        watch_list_element  WLE,
        element_pathnames   EP2,
        element             E2,
        commit_log_elements CLE,
        commit_log          CL
  WHERE WLE.watch_list_id = 3724
    and WLE.element_id    = E.id
    and E.id              = EP.id
    and EP2.pathname like   EP.pathname || '/%'
    AND EP2.id            = E2.id
    AND E2.id             = CLE.element_id
    AND CLE.commit_log_id = CL.id;

I am still suspicous of that like.  It seems to be the performance 
killer here.  There is an index which can be used:

# explain select * from element_pathnames WHERE pathname like 'abc%';
NOTICE:  QUERY PLAN:

Index Scan using element_pathnames_pathname on element_pathnames  
(cost=0.00..5.80 rows=1 width=41)

But in the main query, it doesn't get picked up.  The explain appears 
below (and at http://www.freshports.org/tmp/explain.txt which will be 
easier to read than this text-wrapped version).  There are quite a 
few sequential scans there.  I'm confused as to why the indexes are 
not being used.  A "vacuum analyze" has been run.

Thanks.

Hash Join  (cost=266574.28..279596.82 rows=67012 width=118)
  ->  Hash Join  (cost=263685.03..272372.74 rows=67012 width=114)
        ->  Seq Scan on commit_log_elements cle  (cost=0.00..3936.75 
rows=216575 width=8)
        ->  Hash  (cost=262995.54..262995.54 rows=36997 width=106)
              ->  Hash Join  (cost=2994.62..262995.54 rows=36997 
width=106)
                    ->  Nested Loop  (cost=0.00..257416.50 rows=36997 
width=102)
                          ->  Nested Loop  (cost=0.00..647.08 rows=62 
width=61)
                                ->  Nested Loop  (cost=0.00..437.06 
rows=62 width=20)
                                      ->  Index Scan using 
watch_list_element_pkey on watch_list_element wle  (cost=0.00..229.64 
rows=62 width=4)
                                      ->  Index Scan using 
element_pkey on element e  (cost=0.00..3.34 rows=1 width=16)
                                ->  Index Scan using 
element_pathnames_pkey on element_pathnames ep  (cost=0.00..3.38 
rows=1 width=41)
                          ->  Seq Scan on element_pathnames ep2  
(cost=0.00..2355.70 rows=119570 width=41)
                    ->  Hash  (cost=2286.70..2286.70 rows=119570 
width=4)
                          ->  Seq Scan on element e2  
(cost=0.00..2286.70 rows=119570 width=4)
  ->  Hash  (cost=2543.20..2543.20 rows=58420 width=4)
        ->  Seq Scan on commit_log cl  (cost=0.00..2543.20 rows=58420 
width=4)
-- 
Dan Langille : http://www.langille.org/