Re: Improved estimates for Postgres

From: Luís Roberto Weck <luisrweck(at)gmail(dot)com>
To: Tomas Vondra <tomas(at)vondra(dot)me>
Cc: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Improved estimates for Postgres
Date: 2026-11-19 17:56:19
Message-ID: CACo8VvyaN-u6eKK83VQ9qhL7+=APin=p=MoLtftFG3RNtj0w3A@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

I was looking at inequality joins and I think I found an opportunity to
improve
how they are estimated. The planner uses a fixed 1/3 (DEFAULT_INEQ_SEL) for
them, which is a sensible default when nothing is known, but these columns
often do have MCV lists and histograms, so I think we could use them. For
a value v,
scalarineqsel() already computes the fraction of the left side satisfying
"x < v" for a constant; averaging that over the right side's distribution
turns
the join into an expectation, using statistics that are already there. It
falls
back to 1/3 when a side's statistics cannot describe it, and seven tests
compare
the estimate with the actual row count.

I would appreciate feedback on the fallbacks and on the planning cost of
the extra lookups.

The code is here for now https://github.com/lrweck/postgres/pull/5

@Tomas Vondra <tomas(at)vondra(dot)me>, if you think this approach has any ground,
I'd be more than
happy to continue pursuing it! I know I need to format it as a patch and
send it to
the hackers mailing list.

*Regards,*
*--*
*Luís R. Weck*

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Zack Irvine 2026-11-19 23:34:40 Seeking independent PostgreSQL RLS and multi-tenant security review
Previous Message Luís Roberto Weck 2026-11-19 17:13:05 Re: Improved estimates for Postgres