Re: hashed subplan 5000x slower than two sequential operations

[Bryce Nesbitt <bryce2@xxxxxxxxxxxxx>]

Title: AW: hashed subplan 5000x slower than two sequential operations

Marc Mamin wrote:

Hello,
are the table freshly analyzed, with a sufficient default_statistics_target ?

autovacuum = on                            # Enable autovacuum subprocess?  'on'
autovacuum_naptime = 5min         # time between autovacuum runs
default_statistics_target = 150       # range 1-1000

You may try to get a better plan while rewriting the query as an UNION to get rid of the OR clause.
Something like (not tested):

It is way better


EXPLAIN ANALYZE SELECT contexts.context_key
FROM contexts
    JOIN articles
    ON (articles.context_key=contexts.context_key)
WHERE (contexts.parent_key = 392210)                                                         
AND articles.indexed

UNION
SELECT collection_data.context_key
FROM collection_data
JOIN articles ON (articles.context_key=collection_data.context_key)
WHERE collection_data.collection_context_key = 392210
AND articles.indexed;

                                                                                 QUERY PLAN                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=418.50..418.61 rows=22 width=4) (actual time=0.582..0.671 rows=28 loops=1)
   ->  Sort  (cost=418.50..418.55 rows=22 width=4) (actual time=0.579..0.608 rows=28 loops=1)
         Sort Key: contexts.context_key
         Sort Method:  quicksort  Memory: 26kB
         ->  Append  (cost=0.00..418.01 rows=22 width=4) (actual time=0.042..0.524 rows=28 loops=1)
               ->  Nested Loop  (cost=0.00..376.46 rows=19 width=4) (actual time=0.040..0.423 rows=28 loops=1)
                     ->  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)
                           Index Cond: (parent_key = 392210)
                     ->  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)
                           Index Cond: (public.articles.context_key = contexts.context_key)
                           Filter: public.articles.indexed
               ->  Nested Loop  (cost=0.00..41.32 rows=3 width=4) (actual time=0.043..0.043 rows=0 loops=1)
                     ->  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)
                           Index Cond: (collection_context_key = 392210)
                     ->  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)
                           Index Cond: (public.articles.context_key = collection_data.context_key)
                           Filter: public.articles.indexed
 Total runtime: 0.812 ms