Re: hashed subplan 5000x slower than two sequential operations - Mailing list pgsql-performance

<tt>Marc Mamin wrote:</tt><blockquote cite="mid:C4DAC901169B624F933534A26ED7DF3103E915FA@JENMAIL01.ad.intershop.net"
type="cite"></blockquote><tt><br/></tt><p><tt><font size="2">Hello,<br /> are the table freshly analyzed, with a
sufficientdefault_statistics_target ?<br /></font></tt><tt><br /> autovacuum = on                            # Enable
autovacuumsubprocess?  '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@JENMAIL01.ad.intershop.net"type="cite"><p><tt><font size="2"><br />
Youmay try to get a better plan while rewriting the query as an UNION to get rid of the OR clause.<br /> Something like
(nottested):<br /></font></tt></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 />
SELECTcollection_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.55rows=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.01rows=22 width=4) (actual time=0.042..0.524 rows=28 loops=1)<br />                ->  Nested Loop 
(cost=0.00..376.46rows=19 width=4) (actual time=0.040..0.423 rows=28 loops=1)<br />                      ->  Index
Scanusing 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_idxon 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=3width=4) (actual time=0.043..0.043 rows=0 loops=1)<br />                      ->  Index Scan using
collection_data_context_key_indexon 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>