pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Bryce Nesbitt <bryce2@obviously.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Shrirang Chitnis <Shrirang.Chitnis@hovservices.com>
Subject: Re: hashed subplan 5000x slower than two sequential operations
Date: Wed, 08 Dec 2010 12:05:26 -0800
Message-ID: <4CFFE506.2010606@obviously.com> (raw)
In-Reply-To: <E6DB850FDAD49A459E3C217442489C9225FB9256AB@HOV-MAIL.hovservices.com>
References: <4CFFD446.6040609@obviously.com>
	<E6DB850FDAD49A459E3C217442489C9225FB9256AB@HOV-MAIL.hovservices.com>

Shrirang Chitnis wrote:
> Bryce,
> The two queries are different:
>    
Ah, due to a mistake.  The first version with the hashed subplan is from 
production.
The second version should have read:

====================================================================================
production=> SELECT collection_data.context_key FROM collection_data 
WHERE collection_data.collection_context_key = 392210;
       392210
       395073
      1304250
production=> explain analyze SELECT contexts.context_key
FROM contexts
     JOIN articles
     ON (articles.context_key=contexts.context_key)
WHERE (contexts.parent_key = 392210 OR contexts.context_key IN 
(392210,395073,1304250))
AND articles.indexed
;
                                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=12.32..414.41 rows=20 width=4) (actual 
time=0.112..0.533 rows=28 loops=1)
    ->  Bitmap Heap Scan on contexts  (cost=12.32..135.13 rows=62 
width=4) (actual time=0.079..0.152 rows=31 loops=1)
          Recheck Cond: ((parent_key = 392210) OR (context_key = ANY 
('{392210,392210,395073,1304250}'::integer[])))
          ->  BitmapOr  (cost=12.32..12.32 rows=62 width=0) (actual 
time=0.070..0.070 rows=0 loops=1)
                ->  Bitmap Index Scan on parent_key_idx  
(cost=0.00..3.07 rows=58 width=0) (actual time=0.029..0.029 rows=28 loops=1)
                      Index Cond: (parent_key = 392210)
                ->  Bitmap Index Scan on contexts_pkey  (cost=0.00..9.22 
rows=4 width=0) (actual time=0.037..0.037 rows=4 loops=1)
                      Index Cond: (context_key = ANY 
('{392210,392210,395073,1304250}'::integer[]))
    ->  Index Scan using article_key_idx on articles  (cost=0.00..4.49 
rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=31)
          Index Cond: (articles.context_key = contexts.context_key)
          Filter: articles.indexed
  Total runtime: 0.614 ms
(12 rows)






====================================================================================
production=> explain analyze SELECT contexts.context_key
FROM contexts
     JOIN articles
     ON (articles.context_key=contexts.context_key)
WHERE (contexts.parent_key = 392210
      OR contexts.context_key IN
         (SELECT collection_data.context_key
         FROM collection_data
          WHERE collection_data.collection_context_key = 392210)
)
AND articles.indexed
;
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=83054.41..443755.45 rows=261077 width=4) (actual 
time=3415.609..6737.863 rows=28 loops=1)
    Hash Cond: (articles.context_key = contexts.context_key)
    ->  Seq Scan on articles  (cost=0.00..345661.91 rows=522136 width=4) 
(actual time=0.038..4587.914 rows=517416 loops=1)
          Filter: indexed
    ->  Hash  (cost=69921.25..69921.25 rows=800493 width=4) (actual 
time=926.965..926.965 rows=31 loops=1)
          ->  Seq Scan on contexts  (cost=14.31..69921.25 rows=800493 
width=4) (actual time=2.113..926.794 rows=31 loops=1)
                Filter: ((parent_key = 392210) OR (hashed subplan))
                SubPlan
                  ->  Index Scan using collection_data_context_key_index 
on collection_data  (cost=0.00..14.30 rows=6 width=4) (actual 
time=0.084..0.088 rows=3 loops=1)
                        Index Cond: (collection_context_key = 392210)
  Total runtime: 6738.042 ms
(11 rows)

view thread (11+ messages)  latest in thread

Message-ID: <4CFFE506.2010606@obviously.com>
Permalink:  ../4CFFE506.2010606@obviously.com/
Also on:    postgresql.org/message-id/4CFFE506.2010606@obviously.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: bryce2@obviously.com, Shrirang.Chitnis@hovservices.com
  Subject: Re: hashed subplan 5000x slower than two sequential operations
  In-Reply-To: <4CFFE506.2010606@obviously.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox