Re: sub select performance due to seq scans

Not sure if this helps solve the problem but...
(see below)

As new records are added Indexes are used for awhile and then at some point postgres switches to seq scan. It is repeatable.

Any suggestions/comments to try and solve this are welcome. Thanks

Data is as follows:
capsa.flatommemberrelation 1458 records
capsa.flatommemberrelation(srcobj) 3 distinct
capsa.flatommemberrelation(dstobj) 730 distinct
capsa.flatomfilesysentry 732 records
capsa.flatommemberrelation(objectid) 732 distinct

capsa=# set enable_seqscan=on;
SET
Time: 0.599 ms
capsa=# explain analyze select count(*) from capsa.flatomfilesysentry where objectid in (select dstobj from capsa.flatommemberrelation where srcobj='9e5943e0-219f-11db-8504-001143214409');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=196.01..196.02 rows=1 width=0) (actual time=965.420..965.422 rows=1 loops=1)
-> Nested Loop IN Join (cost=0.00..194.19 rows=728 width=0) (actual time=3.373..964.371 rows=729 loops=1)
Join Filter: ("outer".objectid = "inner".dstobj)
-> Seq Scan on flatomfilesysentry (cost=0.00..65.28 rows=728 width=16) (actual time=0.007..1.505 rows=732 loops=1)
-> Seq Scan on flatommemberrelation (cost=0.00..55.12 rows=725 width=16) (actual time=0.004..0.848 rows=366 loops=732)
Filter: (srcobj = '9e5943e0-219f-11db-8504-001143214409'::capsa_sys.uuid)
Total runtime: 965.492 ms
(7 rows)

Time: 966.806 ms

-----------------------------------------------------------------------------------------------------------
capsa=# set enable_seqscan=off;
SET
Time: 0.419 ms
capsa=# explain analyze select count(*) from capsa.flatomfilesysentry where objectid in (select dstobj from capsa.flatommemberrelation where srcobj='9e5943e0-219f-11db-8504-001143214409');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=24847.73..24847.74 rows=1 width=0) (actual time=24.859..24.860 rows=1 loops=1)
-> Nested Loop (cost=90.05..24845.91 rows=728 width=0) (actual time=2.946..23.640 rows=729 loops=1)
-> Unique (cost=88.04..91.67 rows=363 width=16) (actual time=2.917..6.671 rows=729 loops=1)
-> Sort (cost=88.04..89.86 rows=725 width=16) (actual time=2.914..3.998 rows=729 loops=1)
Sort Key: flatommemberrelation.dstobj
-> Bitmap Heap Scan on flatommemberrelation (cost=7.54..53.60 rows=725 width=16) (actual time=0.260..1.411 rows=729 loops=1)
Recheck Cond: (srcobj = '9e5943e0-219f-11db-8504-001143214409'::capsa_sys.uuid)
-> Bitmap Index Scan on capsa_flatommemberrelation_srcobj_idx (cost=0.00..7.54 rows=725 width=0) (actual time=0.244..0.244 rows=729 loops=1)
Index Cond: (srcobj = '9e5943e0-219f-11db-8504-001143214409'::capsa_sys.uuid)
-> Bitmap Heap Scan on flatomfilesysentry (cost=2.00..63.64 rows=364 width=16) (actual time=0.014..0.015 rows=1 loops=729)
Recheck Cond: (flatomfilesysentry.objectid = "outer".dstobj)
-> Bitmap Index Scan on flatomfilesysentry_pkey (cost=0.00..2.00 rows=364 width=0) (actual time=0.009..0.009 rows=1 loops=729)
Index Cond: (flatomfilesysentry.objectid = "outer".dstobj)
Total runtime: 25.101 ms
(14 rows)

Time: 26.878 ms






H Hale <hhale21(at)rogers(dot)com> wrote: Tom,

It is unique.

Indexes:
"flatomfilesysentry_pkey" PRIMARY KEY, btree (objectid)
"capsa_flatomfilesysentry_name_idx" btree (name)
Foreign-key constraints:
"objectid" FOREIGN KEY (objectid) REFERENCES capsa_sys.master(objectid) ON DELETE CASCADE


Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: H Hale writes:
> -> 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)

Well, there's our estimation failure: 3238 rows expected, one row
actual.

What is the data distribution of flatomfilesysentry.objectid?
It looks from this example like it is unique or nearly so,
but the planner evidently does not think that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings