On Wed, May 30, 2012 at 10:42 AM, Sergey Koposov <kopo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
