Message-ID: <44CDCB69.6070809@archonet.com>
Date: Mon, 31 Jul 2006 10:20:41 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060719)
MIME-Version: 1.0
To: H Hale <hhale21@rogers.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: sub select performance due to seq scans
References: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
In-Reply-To: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

H Hale wrote:
> I am testing a query what that has a sub-select. The query performance is very very poor as shown below due to the use of sequencial scans.  The actual row count of both tables is also shown. It appears the row count shown by explain analyze does not match the actual count. Columns dstobj, srcobj & objectid are all indexed yet postgres insists on using seq scans. Vacuum analyze makes no difference. I am using 8.1.3 on linux. 
>  
>  This is a very simple query with relatively small amount of data and  the query is taking 101482 ms.  Queries with sub-selects on both tables individually is very fast  (8 ms). 
>  
>  How do I prevent the use of seq scans?  

Hmm - something strange here.

>  capsa=# explain analyze select name from capsa.flatomfilesysentry where objectid in ( select dstobj from capsa.flatommemberrelation where srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409');
>  
>  
>                                                             QUERY PLAN
>  ---------------------------------------------------------------------------------------------------------------------------------
>   Nested Loop IN Join  (cost=0.00..1386.45 rows=5809 width=14) (actual time=2.933..101467.463 rows=5841 loops=1)
>     Join Filter: ("outer".objectid = "inner".dstobj)
>     ->  Seq Scan on flatomfilesysentry  (cost=0.00..368.09 rows=5809 width=30) (actual time=0.007..23.451 rows=5844 loops=1)
>     ->  Seq Scan on flatommemberrelation  (cost=0.00..439.05 rows=5842 width=16) (actual time=0.007..11.790 rows=2922 loops=5844)
>           Filter: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid)
>   Total runtime: 101482.256 ms

Look at that second seq-scan (on flatommemberrelation) - it's looping 
5844 times (once for each row in flatmfilesysentry). I'd expect PG to 
materialise the seq-scan once and then join (unless I'm missing 
something, the subselect just involves the one test against a constant).

I'm guessing something in your configuration is pushing your cost 
estimates far away from reality. Could you try issuing a "set 
enable_seqscan=off" and then running explain-analyse again. That will 
show us alternatives.

Also, what performance-related configuration values have you changed? 
Could you post them with a brief description of your hardware?

-- 
   Richard Huxton
   Archonet Ltd