Horton

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

Sergey Koposov <koposov@ast.cam.ac.uk>

View thread
From: Sergey Koposov <koposov@ast.cam.ac.uk>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@postgresql.org, Jeff Janes <jeff.janes@gmail.com>, Stephen Frost <sfrost@snowman.net>
Date: 2012-05-30T18:45:41Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Improve bulk-insert performance by keeping the current target buffer pinned

On Wed, 30 May 2012, Merlin Moncure wrote:
>
> Hm, why aren't we getting a IOS?  Just for kicks (assuming this is
> test data), can we drop the index on just transitid, leaving the index
> on transitid, healpixid?    Is enable_indexonlyscan on?  Has idt_match
> been vacuumed?  What kind of plan do you get when do:

Okay dropping the index on transitid solved the issue with indexonlyscan 
but didn't solve the original problem. Actually the indexonlyscan made the 
sequential queries faster but not the parallel ones.

Here is a side by side explain analyze again for indexonly scans

Bogged:
************
                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=82262274.19..82262286.69 rows=5000 width=498) (actual time=19635.318..19635.318 rows=0 loops=1)
    Sort Key: y.x
    Sort Method: quicksort  Memory: 25kB
    ->  Subquery Scan on y  (cost=0.00..82261967.00 rows=5000 width=498) (actual time=19635.282..19635.282 rows=0 loops=1)
          Filter: ((y.x % 16::bigint) = 7)
          Rows Removed by Filter: 1000000
          ->  Limit  (cost=0.00..82246967.00 rows=1000000 width=490) (actual time=0.076..19373.922 rows=1000000 loops=1)
                ->  Seq Scan on idt_photoobservation_small o  (cost=0.00..82246967.00 rows=1000000 width=490) (actual time=0.074..19179.336 rows=1000000 loops=1)
                      SubPlan 1
                        ->  Index Only Scan using idt_match_transitid_healpixid_idx on idt_match m  (cost=0.00..82.17 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1000000)
                              Index Cond: (transitid = o.transitid)
                              Heap Fetches: 0
  Total runtime: 19702.727 ms
(13 rows)
***********
vs non-bogged
***********
                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=82262274.19..82262286.69 rows=5000 width=498) (actual time=6198.334..6198.334 rows=0 loops=1)
    Sort Key: y.x
    Sort Method: quicksort  Memory: 25kB
    ->  Subquery Scan on y  (cost=0.00..82261967.00 rows=5000 width=498) (actual time=6198.285..6198.285 rows=0 loops=1)
          Filter: ((y.x % 16::bigint) = 7)
          Rows Removed by Filter: 1000000
          ->  Limit  (cost=0.00..82246967.00 rows=1000000 width=490) (actual time=0.116..5932.766 rows=1000000 loops=1)
                ->  Seq Scan on idt_photoobservation_small o  (cost=0.00..82246967.00 rows=1000000 width=490) (actual time=0.113..5730.012 rows=1000000 loops=1)
                      SubPlan 1
                        ->  Index Only Scan using idt_match_transitid_healpixid_idx on idt_match m  (cost=0.00..82.17 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1000000)
                              Index Cond: (transitid = o.transitid)
                              Heap Fetches: 0
  Total runtime: 6239.512 ms
(13 rows)
******

Cheers,
 	S

PS I didn't know that index on two columns together with the indexonlyscan can help 
the queries based on one of those columns. Very helpful!

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