Re: Use extended statistics to estimate (Var op Var) clauses - Mailing list pgsql-hackers

From Zhihong Yu
Subject Re: Use extended statistics to estimate (Var op Var) clauses
Date
Msg-id CALNJ-vQf_gydVipOP8Xxk5mzWda5LXDaLtdysygmJdbySBdNSg@mail.gmail.com
Whole thread Raw
In response to Re: Use extended statistics to estimate (Var op Var) clauses  (Tomas Vondra <tomas.vondra@enterprisedb.com>)
List pgsql-hackers


On Sat, Aug 28, 2021 at 6:53 AM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
Hi,

The attached patch series is modified to improve estimates for these
special clauses (Var op Var with the same var on both sides) without
extended statistics. This is done in 0001, and it seems fairly simple
and cheap.

The 0002 part is still the same patch as on 2021/07/20. Part 0003 fixes
handling of those clauses so that we don't treat them as simple, but it
does that by tweaking statext_is_compatible_clause(), as suggested by
Dean. It does work, although it's a bit more invasive than simply
checking the shape of clause in statext_mcv_clauselist_selectivity.

I do have results for the randomly generated queries, and this does
improve the situation a lot - pretty much all the queries with (a=a) or
(a<a) clauses had terrible estimates, and this fixes that.

That being said, I'm still not sure if this is an issue in real-world
applications, or whether we're solving something because of synthetic
queries generated by the randomized generator. But the checks seem
fairly cheap, so maybe it doesn't matter too much.


regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi,
For 0001-Improve-estimates-for-Var-op-Var-with-the-s-20210828.patch :

+ *     form (variable op variable) with the save variable on both sides.

typo: save -> same

Cheers

pgsql-hackers by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Use extended statistics to estimate (Var op Var) clauses
Next
From: Mark Dilger
Date:
Subject: Re: Use extended statistics to estimate (Var op Var) clauses