On Wed, May 30, 2012 at 10:42 AM, Sergey Koposov <koposov@ast.cam.ac.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