Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 16FD21337B6C for ; Wed, 8 Dec 2010 16:05:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 66208-05 for ; Wed, 8 Dec 2010 20:05:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out-0.mx.aerioconnect.net (out-0-39.mx.aerioconnect.net [216.240.47.99]) by mail.postgresql.org (Postfix) with ESMTP id 256C9133655C for ; Wed, 8 Dec 2010 16:05:36 -0400 (AST) Received: from idiom.com (postfix@mx0.idiom.com [216.240.32.160]) by out-0.mx.aerioconnect.net (8.13.8/8.13.8) with ESMTP id oB8K5X2r020978; Wed, 8 Dec 2010 12:05:33 -0800 X-Client-Authorized: MaGic Cook1e X-Client-Authorized: MaGic Cook1e Received: from [192.168.1.178] (75-149-45-155-SFBA.hfc.comcastbusiness.net [75.149.45.155]) by idiom.com (Postfix) with ESMTP id DF94D2D6011; Wed, 8 Dec 2010 12:05:32 -0800 (PST) Message-ID: <4CFFE506.2010606@obviously.com> Date: Wed, 08 Dec 2010 12:05:26 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.15) Gecko/20101027 SeaMonkey/2.0.10 MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" CC: Shrirang Chitnis Subject: Re: hashed subplan 5000x slower than two sequential operations References: <4CFFD446.6040609@obviously.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.67 on 216.240.47.51 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201012/109 X-Sequence-Number: 41561 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)