<!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(at)JENMAIL01(dot)ad(dot)intershop(dot)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 # Enable autovacuum
subprocess? 'on' <br>
autovacuum_naptime = 5min # time between autovacuum runs<br>
default_statistics_target = 150 # range 1-1000<br>
<br>
<br>
</tt>
<blockquote
cite="mid:C4DAC901169B624F933534A26ED7DF3103E915FA(at)JENMAIL01(dot)ad(dot)intershop(dot)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>
JOIN articles<br>
ON (articles.context_key=contexts.context_key)<br>
WHERE (contexts.parent_key =
392210) <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>
QUERY
PLAN
<br>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
Unique (cost=418.50..418.61 rows=22 width=4) (actual
time=0.582..0.671 rows=28 loops=1)<br>
-> Sort (cost=418.50..418.55 rows=22 width=4) (actual
time=0.579..0.608 rows=28 loops=1)<br>
Sort Key: contexts.context_key<br>
Sort Method: quicksort Memory: 26kB<br>
-> Append (cost=0.00..418.01 rows=22 width=4) (actual
time=0.042..0.524 rows=28 loops=1)<br>
-> Nested Loop (cost=0.00..376.46 rows=19 width=4)
(actual time=0.040..0.423 rows=28 loops=1)<br>
-> Index Scan using parent_key_idx on
contexts (cost=0.00..115.20 rows=58 width=4) (actual time=0.021..0.082
rows=28 loops=1)<br>
Index Cond: (parent_key = 392210)<br>
-> 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=28)<br>
Index Cond: (public.articles.context_key =
contexts.context_key)<br>
Filter: public.articles.indexed<br>
-> Nested Loop (cost=0.00..41.32 rows=3 width=4)
(actual time=0.043..0.043 rows=0 loops=1)<br>
-> Index Scan using
collection_data_context_key_index on collection_data (cost=0.00..14.30
rows=6 width=4) (actual time=0.012..0.015 rows=3 loops=1)<br>
Index Cond: (collection_context_key = 392210)<br>
-> Index Scan using article_key_idx on
articles (cost=0.00..4.49 rows=1 width=4) (actual time=0.006..0.006
rows=0 loops=3)<br>
Index Cond: (public.articles.context_key =
collection_data.context_key)<br>
Filter: public.articles.indexed<br>
Total runtime: 0.812 ms<br>
<br>
<br>
<br>
</tt>
</body>
</html>