BUG #18854: PostgreSQL chooses a suboptimal execution plan when using a specific WHERE filter

The following bug has been logged on the website:

Bug reference: 18854
Logged by: Yoni Sade
Email address: yonisade83(at)gmail(dot)com
PostgreSQL version: 16.8
Operating system: RDS Linux
Description:

Query has a specific WHERE filter for tiny lookup table "t8" (19 rows) which
generates an execution plan which has two "Nested Loop" steps instead of
"Hash Join" steps which makes the query finish after 90 seconds (and spiking
the server CPU when being ran by dozens of sessions simultaneously) instead
of ~3 seconds (when commenting out that specific WHERE filter clause I
mentioned below):

Query #1 (runtime: ~90 seconds)::

SELECT count(*) AS total FROM ( SELECT 1
FROM v_m
JOIN (SELECT *, generate_series(mv.start_year, mv.end_year) as year
FROM mv) t3
on v_m.m_id = t3.m_id
LEFT JOIN t9 ON t3.mm_id = t9.mm_id and t3.year = t9.year
LEFT JOIN v_mm ON t3.mm_id = v_mm.mm_id
LEFT JOIN t8 ON v_m.id = t8.mm_f_id
LEFT JOIN t7 ON t8.sm_f_id = t7.f_id
WHERE v_m.is_exists = true
AND t3.is_active = true
AND v_m.mk_id IN (7)
AND t3.year >= 2021
AND t3.year <= 2024
AND t8.sm_f_id IN (8) -- this specific clause slows down the query
GROUP BY v_m.m_id, v_m.m_h, t3.make, v_m.img, COALESCE(t9.is_new, false),
t3.year) T;

Execution plan #1:

Node Type Entity Cost Rows Time Condition
Aggregate [NULL] 44501.78 - 44501.79 1 [NULL] [NULL]
Group [NULL] 44501.48 - 44501.68 8 [NULL] [NULL]
Incremental Sort [NULL] 44501.48 - 44501.60 8 [NULL] [NULL]
Nested Loop [NULL] 29224.92 - 44501.36 8 [NULL] [NULL]
Nested Loop [NULL] 29224.92 - 44486.38 4 [NULL] [NULL]
Nested Loop [NULL] 2048.29 - 13628.25 1 [NULL] [NULL]
Nested Loop [NULL] 2047.87 - 13623.80 1 [NULL] [NULL]
Nested Loop [NULL] 1047.87 - 1060.89 1 [NULL] COALESCE(((count(*)) > 0),
FALSE)
Index Scan t1 0.27 - 8.29 1 [NULL] (mk_id = 7)
Aggregate [NULL] 1047.60 - 1049.60 200 [NULL] [NULL]
Seq Scan t2 0.00 - 993.28 10864 [NULL] is_active
Subquery Scan [NULL] 1000.00 - 12562.73 15 [NULL] "((t3.year >= 2021)
AND (t3.year <= 2024))"
Gather [NULL] 1000.00 - 12516.33 3093 [NULL] [NULL]
ProjectSet [NULL] 0.00 - 11207.03 1819000 [NULL] [NULL]
Index Only Scan t9 0.42 - 4.44 1 [NULL] "((mm_id = t3.mm_id)
AND (YEAR = t3.year))"
Hash Join [NULL] 27176.63 - 30848.49 771 [NULL] (t4.f_id = t6.mm_f_id)
Unique [NULL] 27175.38 - 28718.18 154280 [NULL] [NULL]
Sort [NULL] 27175.38 - 27561.08 154280 [NULL] [NULL]
Append [NULL] 16.07 - 3859.65 154280 [NULL] [NULL]
Hash Join [NULL] 16.07 - 773.18 38220 [NULL] (t4.f_id = t7.f_id)
Hash Join [NULL] 15.85 - 2315.07 116060 [NULL] (t5.t_id = t.t_id)
Hash [NULL] 1.24 - 1.24 1 [NULL] [NULL]
Seq Scan t8 0.00 - 1.24 1 [NULL] (sm_f_id = 8)
Materialize [NULL] 0.00 - 14.88 2 [NULL] [NULL]
Seq Scan t7 0.00 - 14.88 2 [NULL] (f_id = 8)

Query #2 (runtime: ~3 seconds):

SELECT count(*) AS total FROM ( SELECT 1
FROM v_m
JOIN (SELECT *, generate_series(mv.start_year, mv.end_year) as year
FROM mv) t3
on v_m.m_id = t3.m_id
LEFT JOIN t9 ON t3.mm_id = t9.mm_id and t3.year = t9.year
LEFT JOIN v_mm ON t3.mm_id = v_mm.mm_id
LEFT JOIN t8 ON v_m.id = t8.mm_f_id
LEFT JOIN t7 ON t8.sm_f_id = t7.f_id
WHERE v_m.is_exists = true
AND t3.is_active = true
AND v_m.mk_id IN (7)
AND t3.year >= 2021
AND t3.year <= 2024
GROUP BY v_m.m_id, v_m.m_h, t3.make, v_m.img, COALESCE(t9.is_new, false),
t3.year) T;

Execution plan #2:
Node Type Entity Cost Rows Time Condition
Aggregate [NULL] 44501.78 - 44501.79 1 [NULL] [NULL]
Group [NULL] 44501.48 - 44501.68 8 [NULL] [NULL]
Incremental Sort [NULL] 44501.48 - 44501.60 8 [NULL] [NULL]
Nested Loop [NULL] 29224.92 - 44501.36 8 [NULL] [NULL]
Nested Loop [NULL] 29224.92 - 44486.38 4 [NULL] [NULL]
Nested Loop [NULL] 2048.29 - 13628.25 1 [NULL] [NULL]
Nested Loop [NULL] 2047.87 - 13623.80 1 [NULL] [NULL]
Nested Loop [NULL] 1047.87 - 1060.89 1 [NULL] COALESCE(((count(*)) > 0),
FALSE)
Index Scan t1 0.27 - 8.29 1 [NULL] (mk_id = 7)
Aggregate [NULL] 1047.60 - 1049.60 200 [NULL] [NULL]
Seq Scan t2 0.00 - 993.28 10864 [NULL] is_active
Subquery Scan [NULL] 1000.00 - 12562.73 15 [NULL] "((t3.year >= 2021)
AND (t3.year <= 2024))"
Gather [NULL] 1000.00 - 12516.33 3093 [NULL] [NULL]
ProjectSet [NULL] 0.00 - 11207.03 1819000 [NULL] [NULL]
Index Only Scan t9 0.42 - 4.44 1 [NULL] "((mm_id = t3.mm_id)
AND (YEAR = t3.year))"
Hash Join [NULL] 27176.63 - 30848.49 771 [NULL] (t4.f_id = t6.mm_f_id)
Unique [NULL] 27175.38 - 28718.18 154280 [NULL] [NULL]
Sort [NULL] 27175.38 - 27561.08 154280 [NULL] [NULL]
Append [NULL] 16.07 - 3859.65 154280 [NULL] [NULL]
Hash Join [NULL] 16.07 - 773.18 38220 [NULL] (t4.f_id = t7.f_id)
Hash Join [NULL] 15.85 - 2315.07 116060 [NULL] (t5.t_id = t.t_id)
Hash [NULL] 1.24 - 1.24 1 [NULL] [NULL]
Seq Scan t8 0.00 - 1.24 1 [NULL] (sm_f_id = 8)
Materialize [NULL] 0.00 - 14.88 2 [NULL] [NULL]
Seq Scan t7 0.00 - 14.88 2 [NULL] (f_id = 8)