Re: sub select performance due to seq scans

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.