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

sub select performance due to seq scans

From: H Hale <hhale21(at)rogers(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: sub select performance due to seq scans
Date: 2006-07-31 01:50:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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?  
 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
 (6 rows)
 capsa=# select count(*) from capsa.flatommemberrelation ;
 (1 row)
 capsa=# select count(*) from capsa.flatomfilesysentry ;


pgsql-performance by date

Next:From: Jignesh ShahDate: 2006-07-31 07:07:07
Subject: Re: PostgreSQL scalability on Sun UltraSparc T1
Previous:From: Kjell Tore FossbakkDate: 2006-07-30 19:18:03
Subject: Re: Performance with 2 AMD/Opteron 2.6Ghz and 8gig DDR PC3200

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