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

On Wed, 30 May 2012, Merlin Moncure wrote:

>
> 1. Can we see an explain analyze during a 'bogged' case?

Here is the one to one comparison of the 'bogged'
**********
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=63835201.73..63835214.23 rows=5000 width=498) (actual time=18007.500..18007.500 rows=0 loops=1)
Sort Key: y.x
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on y (cost=0.00..63834894.54 rows=5000 width=498) (actual time=18007.454..18007.454 rows=0 loops=1)
Filter: ((y.x % 16::bigint) = 7)
Rows Removed by Filter: 1000000
-> Limit (cost=0.00..63819894.51 rows=1000002 width=490) (actual time=0.047..17734.570 rows=1000000 loops=1)
-> Seq Scan on idt_photoobservation_small o (cost=0.00..63819894.51 rows=1000002 width=490) (actual time=0.045..17543.902 rows=1000000 loops=1)
SubPlan 1
-> Index Scan using idt_match_transitid_idx on idt_match m (cost=0.00..63.74 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1000000)
Index Cond: (transitid = o.transitid)
Total runtime: 18056.866 ms
(12 rows)

Time: 18067.929 ms
*************************

vs non-bogged:

**************************
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=63835201.73..63835214.23 rows=5000 width=498) (actual time=6635.133..6635.133 rows=0 loops=1)
Sort Key: y.x
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on y (cost=0.00..63834894.54 rows=5000 width=498) (actual time=6635.091..6635.091 rows=0 loops=1)
Filter: ((y.x % 16::bigint) = 7)
Rows Removed by Filter: 1000000
-> Limit (cost=0.00..63819894.51 rows=1000002 width=490) (actual time=0.059..6344.683 rows=1000000 loops=1)
-> Seq Scan on idt_photoobservation_small o (cost=0.00..63819894.51 rows=1000002 width=490) (actual time=0.056..6149.429 rows=1000000 loops=1)
SubPlan 1
-> Index Scan using idt_match_transitid_idx on idt_match m (cost=0.00..63.74 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1000000)
Index Cond: (transitid = o.transitid)
Total runtime: 6669.215 ms
(12 rows)

Time: 6673.991 ms
**************************

>
> 2. Can we try to get 'index only scan' working over idt_match? That's
> should be a matter of adjusting the index so that it's:
> create index on idt_match (transitid, healpixid);

I don't think I could do that. I created the index, you asked (although
it is unclear why it would help...), tried explain, and it still used a
simple index scan. After disabling the index scan it decided to use the bitmap scan
(which isn't actually faster in the multithreaded setup. And after disabling the
bitmapscan it switched to seqscan).

Cheers,
Sergey

PS Just for the reference the current indices on idt_match are
"idt_match_idx" btree (healpixid)
"idt_match_transitid_healpixid_idx" btree (transitid, healpixid)
"idt_match_transitid_idx" btree (transitid)

*****************************************************
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/