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
Cc: Marc Mamin <M.Mamin@intershop.de>
Cc: Shrirang Chitnis <Shrirang.Chitnis@hovservices.com>
Subject: Re: hashed subplan 5000x slower than two sequential operations
Date: Wed, 08 Dec 2010 12:31:53 -0800
Message-ID: <4CFFEB39.1010601@obviously.com> (raw)
In-Reply-To: <C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net>
References: <4CFFD446.6040609@obviously.com>
	<E6DB850FDAD49A459E3C217442489C9225FB9256AB@HOV-MAIL.hovservices.com>
	<C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net>

<!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>



view thread (11+ messages)  latest in thread

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

 · 

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, M.Mamin@intershop.de, Shrirang.Chitnis@hovservices.com
  Subject: Re: hashed subplan 5000x slower than two sequential operations
  In-Reply-To: <4CFFEB39.1010601@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