From: | Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Improve Hash/Merge Join estimate accuracy when all predicates are Hash/Merge clauses |
Date: | 2025-07-10 10:09:48 |
Message-ID: | 7abcff03-b38c-432f-81fa-d66abf0f061d@tantorlabs.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi hackers,
The planner currently calls approx_tuple_count() to estimate
hashjointuples and mergejointuples. That makes sense when
joinrestrictinfo contains additional clauses beyond the hash/merge
equality list. But if all join restriction clauses are exactly those
hash/merge clauses, the estimate already computed in
path->jpath.path.rows is usually more accurate (and free).
This patch reuses path->jpath.path.rows in that case and skips
approx_tuple_count().
Regression results
==================
| actual | approx | estimate
---------------------+--------+--------+-------
join.sql q1 | 5 | 1 | 5
join.sql q2 | 10 000 | 1 | 10 000
join.sql q3 | 5 | 5 | 5
join.sql q4 | 5 | 5 | 5
join.sql q5 | 5 | 1 | 5
partition_join q1 | 200 | 1 | 200
partition_join q2 | 42 | 84 | 84
partition_join q3 | 8 | 1 | 8
postgres_fdw | 2001 | 1001 | 2001
select_parallel q1 | 0 | 5 000 | 5 000
updatable_views q1 | 2 | 2 | 423
Two cases get worse: select_parallel.sql and updatable_views.sql.
Looking forward to your feedback!
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Improve-row-count-estimates-for-hash-merge-joins.patch | text/x-patch | 18.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jakub Wartak | 2025-07-10 10:15:52 | Re: Adding basic NUMA awareness |
Previous Message | Nazir Bilal Yavuz | 2025-07-10 09:59:40 | Re: Explicitly enable meson features in CI |