Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 0377F1337B8A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Dec 2010 16:32:22 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 32816-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  8 Dec 2010 20:32:10 +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 342AB1337B83
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Dec 2010 16:32:10 -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
	oB8KW0NN022730; Wed, 8 Dec 2010 12:32:00 -0800
X-Client-Authorized: MaGic Cook1e
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 4A5442D6014;
	Wed,  8 Dec 2010 12:31:59 -0800 (PST)
Message-ID: <4CFFEB39.1010601@obviously.com>
Date: Wed, 08 Dec 2010 12:31:53 -0800
From: Bryce Nesbitt <bryce2@obviously.com>
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: Marc Mamin <M.Mamin@intershop.de>,
	Shrirang Chitnis <Shrirang.Chitnis@hovservices.com>
Subject: Re: hashed subplan 5000x slower than two sequential
 operations
References: <4CFFD446.6040609@obviously.com>
	<E6DB850FDAD49A459E3C217442489C9225FB9256AB@HOV-MAIL.hovservices.com>
	<C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net>
In-Reply-To: 
 <C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net>
Content-Type: text/html; charset=ISO-8859-1
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.176 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723
X-Spam-Level: 
X-Archive-Number: 201012/114
X-Sequence-Number: 41566

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Marc Mamin wrote:</tt>
<blockquote
 cite="mid:C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net"
 type="cite">
  <meta http-equiv="Content-Type"
 content="text/html; charset=ISO-8859-1">
  <meta name="Generator"
 content="MS Exchange Server version 6.5.7651.59">
  <title>AW: [PERFORM] hashed subplan 5000x slower than two sequential
operations</title>
  <tt><br>
  </tt>
  <p><tt><font size="2">Hello,<br>
are the table freshly analyzed, with a sufficient
default_statistics_target ?<br>
  </font></tt></p>
</blockquote>
<tt><br>
autovacuum = on&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; # Enable autovacuum
subprocess?&nbsp; 'on' <br>
autovacuum_naptime = 5min&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # time between autovacuum runs<br>
default_statistics_target = 150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # range 1-1000<br>
<br>
<br>
</tt>
<blockquote
 cite="mid:C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net"
 type="cite">
  <p><tt><font size="2"><br>
You may try to get a better plan while rewriting the query as an UNION
to get rid of the OR clause.<br>
Something like (not tested):<br>
  </font></tt></p>
</blockquote>
<tt>It is way better<br>
<br>
<br>
EXPLAIN ANALYZE SELECT contexts.context_key<br>
FROM contexts<br>
&nbsp;&nbsp;&nbsp; JOIN articles<br>
&nbsp;&nbsp;&nbsp; ON (articles.context_key=contexts.context_key)<br>
WHERE (contexts.parent_key =
392210)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
AND articles.indexed<br>
<br>
UNION<br>
SELECT collection_data.context_key<br>
FROM collection_data<br>
JOIN articles ON (articles.context_key=collection_data.context_key)<br>
WHERE collection_data.collection_context_key = 392210<br>
AND articles.indexed;<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Unique&nbsp; (cost=418.50..418.61 rows=22 width=4) (actual
time=0.582..0.671 rows=28 loops=1)<br>
&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=418.50..418.55 rows=22 width=4) (actual
time=0.579..0.608 rows=28 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: contexts.context_key<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method:&nbsp; quicksort&nbsp; Memory: 26kB<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Append&nbsp; (cost=0.00..418.01 rows=22 width=4) (actual
time=0.042..0.524 rows=28 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp; (cost=0.00..376.46 rows=19 width=4)
(actual time=0.040..0.423 rows=28 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using parent_key_idx on
contexts&nbsp; (cost=0.00..115.20 rows=58 width=4) (actual time=0.021..0.082
rows=28 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (parent_key = 392210)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using article_key_idx on
articles&nbsp; (cost=0.00..4.49 rows=1 width=4) (actual time=0.007..0.008
rows=1 loops=28)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (public.articles.context_key =
contexts.context_key)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: public.articles.indexed<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp; (cost=0.00..41.32 rows=3 width=4)
(actual time=0.043..0.043 rows=0 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using
collection_data_context_key_index on collection_data&nbsp; (cost=0.00..14.30
rows=6 width=4) (actual time=0.012..0.015 rows=3 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (collection_context_key = 392210)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using article_key_idx on
articles&nbsp; (cost=0.00..4.49 rows=1 width=4) (actual time=0.006..0.006
rows=0 loops=3)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (public.articles.context_key =
collection_data.context_key)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: public.articles.indexed<br>
&nbsp;Total runtime: 0.812 ms<br>
<br>
<br>
<br>
</tt>
</body>
</html>