Re: hashed subplan 5000x slower than two sequential operations

Shrirang Chitnis wrote:
> Bryce,
> The two queries are different:
>
Ah, due to a mistake. The first version with the hashed subplan is from
production.
The second version should have read:

====================================================================================
production=> SELECT collection_data.context_key FROM collection_data
WHERE collection_data.collection_context_key = 392210;
392210
395073
1304250
production=> explain analyze SELECT contexts.context_key
FROM contexts
JOIN articles
ON (articles.context_key=contexts.context_key)
WHERE (contexts.parent_key = 392210 OR contexts.context_key IN
(392210,395073,1304250))
AND articles.indexed
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=12.32..414.41 rows=20 width=4) (actual
time=0.112..0.533 rows=28 loops=1)
-> Bitmap Heap Scan on contexts (cost=12.32..135.13 rows=62
width=4) (actual time=0.079..0.152 rows=31 loops=1)
Recheck Cond: ((parent_key = 392210) OR (context_key = ANY
('{392210,392210,395073,1304250}'::integer[])))
-> BitmapOr (cost=12.32..12.32 rows=62 width=0) (actual
time=0.070..0.070 rows=0 loops=1)
-> Bitmap Index Scan on parent_key_idx
(cost=0.00..3.07 rows=58 width=0) (actual time=0.029..0.029 rows=28 loops=1)
Index Cond: (parent_key = 392210)
-> Bitmap Index Scan on contexts_pkey (cost=0.00..9.22
rows=4 width=0) (actual time=0.037..0.037 rows=4 loops=1)
Index Cond: (context_key = ANY
('{392210,392210,395073,1304250}'::integer[]))
-> 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=31)
Index Cond: (articles.context_key = contexts.context_key)
Filter: articles.indexed
Total runtime: 0.614 ms
(12 rows)

====================================================================================
production=> explain analyze SELECT contexts.context_key
FROM contexts
JOIN articles
ON (articles.context_key=contexts.context_key)
WHERE (contexts.parent_key = 392210
OR contexts.context_key IN
(SELECT collection_data.context_key
FROM collection_data
WHERE collection_data.collection_context_key = 392210)
)
AND articles.indexed
;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=83054.41..443755.45 rows=261077 width=4) (actual
time=3415.609..6737.863 rows=28 loops=1)
Hash Cond: (articles.context_key = contexts.context_key)
-> Seq Scan on articles (cost=0.00..345661.91 rows=522136 width=4)
(actual time=0.038..4587.914 rows=517416 loops=1)
Filter: indexed
-> Hash (cost=69921.25..69921.25 rows=800493 width=4) (actual
time=926.965..926.965 rows=31 loops=1)
-> Seq Scan on contexts (cost=14.31..69921.25 rows=800493
width=4) (actual time=2.113..926.794 rows=31 loops=1)
Filter: ((parent_key = 392210) OR (hashed subplan))
SubPlan
-> Index Scan using collection_data_context_key_index
on collection_data (cost=0.00..14.30 rows=6 width=4) (actual
time=0.084..0.088 rows=3 loops=1)
Index Cond: (collection_context_key = 392210)
Total runtime: 6738.042 ms
(11 rows)