X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 846959FB322 for ; Sun, 30 Jul 2006 22:57:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 23474-03 for ; Sun, 30 Jul 2006 22:56:57 -0300 (ADT) X-Greylist: delayed 00:06:40.563485 by SQLgrey- Received: from web88005.mail.re2.yahoo.com (web88005.mail.re2.yahoo.com [206.190.37.192]) by postgresql.org (Postfix) with SMTP id C43049FB320 for ; Sun, 30 Jul 2006 22:56:56 -0300 (ADT) Received: (qmail 87912 invoked by uid 60001); 31 Jul 2006 01:50:14 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=rogers.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=3i7VFtItxUp/Y4YgtJ3AjLujN96/TxyKxLT/YfvOTHYemJR0ZbO8Y+T2KyefSWGljYt3YEmagA4M99iK1pskiYW1encbizcUHDVRpvOqGhZp4/8u0RgDNMDQgatOfLoBfw7NSOBJidYeqeUPf+2ednPtjeeGpnE3jTZCXVOA1PQ= ; Message-ID: <20060731015014.87910.qmail@web88005.mail.re2.yahoo.com> Received: from [72.140.115.81] by web88005.mail.re2.yahoo.com via HTTP; Sun, 30 Jul 2006 21:50:14 EDT Date: Sun, 30 Jul 2006 21:50:14 -0400 (EDT) From: H Hale Subject: sub select performance due to seq scans To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1210526602-1154310614=:85425" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.909 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200607/291 X-Sequence-Number: 20151 --0-1210526602-1154310614=:85425 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 ; count ------- 11932 (1 row) capsa=# select count(*) from capsa.flatomfilesysentry ; count ------- 5977 --0-1210526602-1154310614=:85425 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 ;
 count
-------
 11932
(1 row)

capsa=# select count(*) from capsa.flatomfilesysentry ;
 count
-------
  5977




--0-1210526602-1154310614=:85425--