From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Sergey Koposov <koposov(at)ast(dot)cam(dot)ac(dot)uk> |
Cc: | pgsql-hackers(at)postgresql(dot)org, Robert Haas <robertmhaas(at)gmail(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net> |
Subject: | Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile |
Date: | 2012-05-30 16:26:06 |
Message-ID: | CAHyXU0zbFMF4uzApZW8DdOPqXb3Vb_UbRsCUbM4DGMsUAECnRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2012-05-30 16:43:06 | Re: too low NAPTIME_PER_CYCLE /too many wakeups in walreceiver.c |
Previous Message | Peter Geoghegan | 2012-05-30 16:19:26 | Re: Uh, I change my mind about commit_delay + commit_siblings (sort of) |