Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

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/