Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile - Mailing list pgsql-hackers

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)
(actualtime=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=1000000loops=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                       ->
IndexOnly 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.017rows=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)
(actualtime=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)
(actualtime=0.113..5730.012 rows=1000000 loops=1)                     SubPlan 1                       ->  Index Only
Scanusing 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=1loops=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/