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

On Wed, May 30, 2012 at 10:42 AM, Sergey Koposov <koposov(at)ast(dot)cam(dot)ac(dot)uk> wrote:
> Here is the actual explain analyze of the query on the smaller dataset
> which I have been using for the recent testing.
>
> test=# explain analyze create table _tmp0 as select * from
>
>  ( select *,
>        (select healpixid from idt_match as m where m.transitid=o.transitid)
>                as x from idt_photoobservation_small as o offset 0
>                  ) as y where x%16=0 order by x;
>
>  QUERY PLAN
>
> p----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=63835201.73..63835214.23 rows=5000 width=498) (actual
> time=8203.041..8252.216 rows=173696 loops=1)
>   Sort Key: y.x
>   Sort Method: quicksort  Memory: 182555kB
>   ->  Subquery Scan on y  (cost=0.00..63834894.54 rows=5000 width=498)
> (actual time=0.102..7602.947 rows=173696 loops=1)
>
>         Filter: ((y.x % 16::bigint) = 0)
>         Rows Removed by Filter: 826304
>         ->  Limit  (cost=0.00..63819894.51 rows=1000002 width=490) (actual
> time=0.041..7296.401 rows=1000000 loops=1)
>               ->  Seq Scan on idt_photoobservation_small o
>  (cost=0.00..63819894.51 rows=1000002 width=490) (actual
> time=0.038..7094.555 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: 8908.631 ms

Two things:

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

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);

merlin

merlin