Re: sub select performance due to seq scans

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
useof sequencial scans.  The actual row count of both tables is also shown. It appears the row count shown by explain
analyzedoes not match the actual count. Columns dstobj, srcobj & objectid are all indexed yet postgres insists on using
seqscans. 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-selectson 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.flatommemberrelationwhere 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