pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Additional improvements to extended statistics
Date: Tue, 8 Dec 2020 13:46:57 +0100
Message-ID: <4ba455c6-a0fa-cae7-7bc3-4aa5b6cd11d4@enterprisedb.com> (raw)
In-Reply-To: <CAEZATCW=J65GUFm50RcPv-iASnS2mTXQbr=CfBvWRVhFLJ_fWA@mail.gmail.com>
References: <20200315000809.ruwpx7ktnpnzly7c@development>
	<20200321215946.epx3dfi74stlwiab@development>
	<CAEZATCW3dy1g=tEmP3oW-4FH=Lpp516xfXfQyPXiEYm4v3hJfQ@mail.gmail.com>
	<20200324010838.hbv5gzhexcv5cilg@development>
	<CAEZATCX8u9bZzcWEzqA_t7f_OQHu2oxeTUGnFHNEOXnJo35AQg@mail.gmail.com>
	<20200324143306.rz5siohm4jsoveqm@development>
	<C68FE2F1-94B5-4286-A48A-6B62D155745A@yesql.se>
	<20200703011030.smzijlphrjjodhxx@development>
	<57eb142c-d5a4-cfca-675b-0dfd9ef7c2e2@2ndquadrant.com>
	<CAEZATCVE2Jx1QB0FFazvgjKAY25tv-EWX9C5XSefEZY-gxn75A@mail.gmail.com>
	<56df91cd-c7dc-f46b-6bc3-cb36982ff9ae@enterprisedb.com>
	<CAEZATCU4dVJkW1sNgOP800=mvzQMvXTw_Y=tnkt3dg=Q+BJVdw@mail.gmail.com>
	<CAEZATCVi6Lxooa8YFq5F6tkeu9kB_o3SUqjo95_kuSs2Sq-rHQ@mail.gmail.com>
	<ec21ebff-67ff-3d80-462d-131bdde91b58@enterprisedb.com>
	<CAEZATCWFFU1NYqJ3pRDohDJZaDbv4NWXMgyRtph8zomagzeZag@mail.gmail.com>
	<CAEZATCW=J65GUFm50RcPv-iASnS2mTXQbr=CfBvWRVhFLJ_fWA@mail.gmail.com>

On 12/7/20 5:15 PM, Dean Rasheed wrote:
> On Wed, 2 Dec 2020 at 15:51, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>>
>> The sort of queries I had in mind were things like this:
>>
>>   WHERE (a = 1 AND b = 1) OR (a = 2 AND b = 2)
>>
>> However, the new code doesn't apply the extended stats directly using
>> clauselist_selectivity_or() for this kind of query because there are
>> no RestrictInfos for the nested AND clauses, so
>> find_single_rel_for_clauses() (and similarly
>> statext_is_compatible_clause()) regards those clauses as not
>> compatible with extended stats. So what ends up happening is that
>> extended stats are used only when we descend down to the two AND
>> clauses, and their results are combined using the original "s1 + s2 -
>> s1 * s2" formula. That actually works OK in this case, because there
>> is no overlap between the two AND clauses, but it wouldn't work so
>> well if there was.
>>
>> I'm pretty sure that can be fixed by teaching
>> find_single_rel_for_clauses() and statext_is_compatible_clause() to
>> handle BoolExpr clauses, looking for RestrictInfos underneath them,
>> but I think that should be left for a follow-in patch.
> 
> Attached is a patch doing that, which improves a couple of the
> estimates for queries with AND clauses underneath OR clauses, as
> expected.
> 
> This also revealed a minor bug in the way that the estimates for
> multiple statistics objects were combined while processing an OR
> clause -- the estimates for the overlaps between clauses only apply
> for the current statistics object, so we really have to combine the
> estimates for each set of clauses for each statistics object as if
> they were independent of one another.
> 
> 0001 fixes the multiple-extended-stats issue for OR clauses, and 0002
> improves the estimates for sub-AND clauses underneath OR clauses.
> 

Cool! Thanks for taking time to investigate and fixing those. Both
patches seem fine to me.

> These are both quite small patches, that hopefully won't interfere
> with any of the other extended stats patches.
> 

I haven't tried, but it should not interfere with it too much.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company





view thread (40+ messages)

Message-ID: <4ba455c6-a0fa-cae7-7bc3-4aa5b6cd11d4@enterprisedb.com>
Permalink:  ../4ba455c6-a0fa-cae7-7bc3-4aa5b6cd11d4@enterprisedb.com/
Also on:    postgresql.org/message-id/4ba455c6-a0fa-cae7-7bc3-4aa5b6cd11d4@enterprisedb.com

 · 

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-hackers@postgresql.org
  Cc: tomas.vondra@enterprisedb.com, dean.a.rasheed@gmail.com, tomas.vondra@2ndquadrant.com, daniel@yesql.se
  Subject: Re: Additional improvements to extended statistics
  In-Reply-To: <4ba455c6-a0fa-cae7-7bc3-4aa5b6cd11d4@enterprisedb.com>

* 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