On Wed, 30 May 2012, Merlin Moncure wrote:
Hm, why aren't we getting a IOS? Just for kicks (assuming this is
test data), can we drop the index on just transitid, leaving the index
on transitid, healpixid? Is enable_indexonlyscan on? Has idt_match
been vacuumed? What kind of plan do you get when do:
Okay dropping the index on transitid solved the issue with indexonlyscan
but didn't solve the original problem. Actually the indexonlyscan made the
sequential queries faster but not the parallel ones.
Here is a side by side explain analyze again for indexonly scans
Bogged:
************
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=82262274.19..82262286.69 rows=5000 width=498) (actual
time=19635.318..19635.318 rows=0 loops=1)
Sort Key: y.x
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on y (cost=0.00..82261967.00 rows=5000 width=498) (actual
time=19635.282..19635.282 rows=0 loops=1)
Filter: ((y.x % 16::bigint) = 7)
Rows Removed by Filter: 1000000
-> Limit (cost=0.00..82246967.00 rows=1000000 width=490) (actual
time=0.076..19373.922 rows=1000000 loops=1)
-> Seq Scan on idt_photoobservation_small o
(cost=0.00..82246967.00 rows=1000000 width=490) (actual time=0.074..19179.336
rows=1000000 loops=1)
SubPlan 1
-> Index Only Scan using
idt_match_transitid_healpixid_idx on idt_match m (cost=0.00..82.17 rows=1
width=8) (actual time=0.016..0.017 rows=1 loops=1000000)
Index Cond: (transitid = o.transitid)
Heap Fetches: 0
Total runtime: 19702.727 ms
(13 rows)
***********
vs non-bogged
***********
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=82262274.19..82262286.69 rows=5000 width=498) (actual
time=6198.334..6198.334 rows=0 loops=1)
Sort Key: y.x
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on y (cost=0.00..82261967.00 rows=5000 width=498) (actual
time=6198.285..6198.285 rows=0 loops=1)
Filter: ((y.x % 16::bigint) = 7)
Rows Removed by Filter: 1000000
-> Limit (cost=0.00..82246967.00 rows=1000000 width=490) (actual
time=0.116..5932.766 rows=1000000 loops=1)
-> Seq Scan on idt_photoobservation_small o
(cost=0.00..82246967.00 rows=1000000 width=490) (actual time=0.113..5730.012
rows=1000000 loops=1)
SubPlan 1
-> Index Only Scan using
idt_match_transitid_healpixid_idx on idt_match m (cost=0.00..82.17 rows=1
width=8) (actual time=0.003..0.003 rows=1 loops=1000000)
Index Cond: (transitid = o.transitid)
Heap Fetches: 0
Total runtime: 6239.512 ms
(13 rows)
******
Cheers,
S
PS I didn't know that index on two columns together with the indexonlyscan can help
the queries based on one of those columns. Very helpful!
*****************************************************
Sergey E. Koposov, PhD, Research Associate
Institute of Astronomy, University of Cambridge
Madingley road, CB3 0HA, Cambridge, UK
Tel: +44-1223-337-551 Web: http://www.ast.cam.ac.uk/~koposov/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
