From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Mark Dilger <hornschnorter@gmail.com>
Cc: Adrien Nayrat <adrien.nayrat@dalibo.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
Date: Tue, 27 Mar 2018 02:36:09 +0200
Message-ID: <49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.com> (raw)
In-Reply-To: <CAEZATCUC8h6Osegq2Lw75Hc1NG=72JgNiiTQSSp9CpDCkQgakw@mail.gmail.com>
References: <20180310171906.gd6vyz6hnou2n3h7@alvherre.pgsql>
<850c3b6b-2641-496c-4efc-500c720f7260@2ndquadrant.com>
<ec49a434-b814-2cfa-f602-02a0cc9e518c@2ndquadrant.com>
<CAEZATCW9AXzf+1T44B0=rBd8a0AD+pWO_PpkhApqLBE9JS1Cyg@mail.gmail.com>
<66ba9c45-3b07-e5b6-fd11-15e75f6ee8e1@2ndquadrant.com>
<CAEZATCUC8h6Osegq2Lw75Hc1NG=72JgNiiTQSSp9CpDCkQgakw@mail.gmail.com>
Hi Dean,
Here is an updated patch (hopefully) fixing the bugs you've reported so
far. In particular, it fixes this:
1) mostly harmless memset bug in UpdateStatisticsForTypeChange
2) passing the right list (stat_clauses) to mcv_clauselist_selectivity
3) corrections to a couple of outdated comments
4) handling of NOT clauses in MCV lists (and in histograms)
The query you posted does not fail anymore, but there's a room for
improvement. We should be able to handle queries like this:
select * from foo where a=1 and not b=1;
But we don't, because we only recognize F_EQSEL, F_SCALARLTSEL and
F_SCALARGTSEL, but F_NEQSEL (which is what "not b=1" uses). Should be
simple to fix, I believe.
5) handling of mcv_lowsel in statext_clauselist_selectivity
I do believe the new behavior is correct - as I suspected, I broke this
during the last rebase, where I also moved some stuff from the histogram
part to the MCV part. I've also added the (sum of MCV frequencies), as
you suggested.
I think we could improve the estimate further by computing ndistinct
estimate, and then using that to compute average frequency of non-MCV
items. Essentially what var_eq_const does:
if (otherdistinct > 1)
selec /= otherdistinct;
Not sure how to do that when there are not just equality clauses.
BTW I think there's a bug in handling the fullmatch flag - it should not
be passed to AND/OR subclauses the way it is, because then
WHERE a=1 OR (a=2 AND b=2)
will probably set it to 'true' because of (a=2 AND b=2). Which will
short-circuit the statext_clauselist_selectivity, forcing it to ignore
the non-MCV part.
But that's something I need to look at more closely tomorrow. Another
thing I probably need to do is to add more regression tests, protecting
against bugs similar to those you found.
Thanks for the feedback so far!
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
[application/gzip] 0001-multivariate-MCV-lists-20180327.patch.gz (33.5K, ../49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.com/2-0001-multivariate-MCV-lists-20180327.patch.gz)
download
[application/gzip] 0002-multivariate-histograms-20180327.patch.gz (42.9K, ../49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.com/3-0002-multivariate-histograms-20180327.patch.gz)
download
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@2ndquadrant.com, dean.a.rasheed@gmail.com, alvherre@2ndquadrant.com, andres@anarazel.de, thomas.munro@enterprisedb.com, hornschnorter@gmail.com, adrien.nayrat@dalibo.com
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
In-Reply-To: <49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.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