pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: yonisade83@gmail.com
Subject: BUG #18854: PostgreSQL chooses a suboptimal execution plan when using a specific WHERE filter
Date: Tue, 18 Mar 2025 11:34:05 +0000
Message-ID: <18854-f5bc660117569bdf@postgresql.org> (raw )
The following bug has been logged on the website:
Bug reference: 18854
Logged by: Yoni Sade
Email address: yonisade83@gmail.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)
view thread (4+ messages) latest in thread
Message-ID: <18854-f5bc660117569bdf@postgresql.org>
Permalink: ../18854-f5bc660117569bdf@postgresql.org/
Also on: postgresql.org/message-id/18854-f5bc660117569bdf@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, yonisade83@gmail.com
Subject: Re: BUG #18854: PostgreSQL chooses a suboptimal execution plan when using a specific WHERE filter
In-Reply-To: <18854-f5bc660117569bdf@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox