Improve Hash/Merge Join estimate accuracy when all predicates are Hash/Merge clauses

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

Browse pgsql-hackers by date

  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