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 A3C6E9FB35F for ; Mon, 31 Jul 2006 11:15:14 -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 92232-08 for ; Mon, 31 Jul 2006 11:14:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web88002.mail.re2.yahoo.com (web88002.mail.re2.yahoo.com [206.190.37.189]) by postgresql.org (Postfix) with SMTP id A31FF9FB35E for ; Mon, 31 Jul 2006 11:14:42 -0300 (ADT) Received: (qmail 87708 invoked by uid 60001); 31 Jul 2006 14:14:27 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=rogers.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=cqp1SlJbHhupuC2xM6bB3Zi7T4rb16F/NEMfjFrT0A+Mb11tA1H0EPlAW8YbRbde9JYSnNSgnnGJXWVNz2zScaboeNJIsM/P/k0wRToXNow3lwRs62NQe53r965AiiS8s/9/K/nwh/+fPsVMqEY2OFa9X1MQadvGr/KAmt4Bph0= ; Message-ID: <20060731141427.87706.qmail@web88002.mail.re2.yahoo.com> Received: from [216.16.241.206] by web88002.mail.re2.yahoo.com via HTTP; Mon, 31 Jul 2006 10:14:27 EDT Date: Mon, 31 Jul 2006 10:14:27 -0400 (EDT) From: H Hale Subject: Re: sub select performance due to seq scans To: pgsql-performance@postgresql.org In-Reply-To: <44CDCB69.6070809@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-867864686-1154355267=:84394" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=4.191 tagged_above=0 required=5 tests=HTML_30_40, HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: **** X-Archive-Number: 200607/310 X-Sequence-Number: 20170 --0-867864686-1154355267=:84394 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster The hardware is XEON 3GHZ P4 2GB Memory with 80GB SATA drive. Kernel.SHMMAX=128MB The following config changes have been made from the defaults... shared_buffers = 8000 # min 16 or max_connections*2, 8KB each max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 10 # 0-1000 milliseconds stats_start_collector = on stats_row_level = on autovacuum = on # enable autovacuum subprocess? autovacuum_naptime = 20 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 500 # min # of tuple updates before# vacuum autovacuum_analyze_threshold = 250 # min # of tuple updates before Here is the query plan... capsa=# set enable_seqscan=off; SET Time: 0.478 ms 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 (cost=873.32..1017581.78 rows=6476 width=14) (actual time=80.402..241.881 rows=6473 loops=1) -> Unique (cost=871.32..903.68 rows=3229 width=16) (actual time=80.315..113.282 rows=6473 loops=1) -> Sort (cost=871.32..887.50 rows=6473 width=16) (actual time=80.310..94.279 rows=6473 loops=1) Sort Key: flatommemberrelation.dstobj -> Bitmap Heap Scan on flatommemberrelation (cost=56.66..461.57 rows=6473 width=16) (actual time=2.613..14.229 rows=6473 loops=1) Recheck Cond: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid) -> Bitmap Index Scan on capsa_flatommemberrelation_srcobj_idx (cost=0.00..56.66 rows=6473 width=0) (actual time=2.344..2.344 rows=6473 loops=1) Index Cond: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid) -> Bitmap Heap Scan on flatomfilesysentry (cost=2.00..274.38 rows=3238 width=30) (actual time=0.011..0.013 rows=1 loops=6473) Recheck Cond: (flatomfilesysentry.objectid = "outer".dstobj) -> Bitmap Index Scan on flatomfilesysentry_pkey (cost=0.00..2.00 rows=3238 width=0) (actual time=0.007..0.007 rows=1 loops=6473) Index Cond: (flatomfilesysentry.objectid = "outer".dstobj) Total runtime: 251.611 ms (13 rows) Time: 252.825 ms I went back to the stock conf settings, did a vaccuum full analyze and still get the same results. Background... We have spikes of activty where both tables get rows inserted & have many updates. During this time performance drops. I have been experimenting with auto vac settings as vaccuuming was helping although query performance did not return to normal until after the activity spike. In this case ( and I not sure why yet) vac made no difference. --0-867864686-1154355267=:84394 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
The hardware is XEON 3GHZ P4 2GB Memory with 80GB SATA drive.
Kernel.SHMMAX=128MB

The following config changes have been made from the defaults...

shared_buffers = 8000            # min 16 or max_connections*2, 8KB each
max_fsm_pages = 50000            # min max_fsm_relations*16, 6 bytes each
vacuum_cost_delay = 10            # 0-1000 milliseconds
stats_start_collector = on
stats_row_level = on
autovacuum = on                # enable autovacuum subprocess?
autovacuum_naptime = 20        # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 500    # min # of tuple updates before# vacuum
autovacuum_analyze_threshold = 250    # min # of tuple updates before

Here is the query plan...

capsa=# set enable_seqscan=off;
SET
Time: 0.478 ms
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  (cost=873.32..1017581.78 rows=6476 width=14) (actual time=80.402..241.881 rows=6473 loops=1)
   ->  Unique  (cost=871.32..903.68 rows=3229 width=16) (actual time=80.315..113.282 rows=6473 loops=1)
         ->  Sort  (cost=871.32..887.50 rows=6473 width=16) (actual time=80.310..94.279 rows=6473 loops=1)
               Sort Key: flatommemberrelation.dstobj
               ->  Bitmap Heap Scan on flatommemberrelation  (cost=56.66..461.57 rows=6473 width=16) (actual time=2.613..14.229 rows=6473 loops=1)
                     Recheck Cond: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid)
                     ->  Bitmap Index Scan on capsa_flatommemberrelation_srcobj_idx  (cost=0.00..56.66 rows=6473 width=0) (actual time=2.344..2.344 rows=6473 loops=1)
                           Index Cond: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid)
   ->  Bitmap Heap Scan on flatomfilesysentry  (cost=2.00..274.38 rows=3238 width=30) (actual time=0.011..0.013 rows=1 loops=6473)
         Recheck Cond: (flatomfilesysentry.objectid = "outer".dstobj)
         ->  Bitmap Index Scan on flatomfilesysentry_pkey  (cost=0.00..2.00 rows=3238 width=0) (actual time=0.007..0.007 rows=1 loops=6473)
               Index Cond: (flatomfilesysentry.objectid = "outer".dstobj)
 Total runtime: 251.611 ms
(13 rows)

Time: 252.825 ms

I went back to the stock conf settings, did a vaccuum full analyze and still get the same results.

Background...

We have spikes of activty where both tables get rows inserted & have many updates. During this time performance drops.
I have been experimenting with auto vac settings as vaccuuming was helping although query performance
did not return to normal until after the activity spike.
In this case ( and I not sure why yet) vac made no difference.






--0-867864686-1154355267=:84394--