On Thu, Nov 21, 2019 at 12:29:39PM +0000, Dean Rasheed wrote:
>On Tue, 19 Nov 2019 at 15:08, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>>
>> Yes, adding the condition to statext_mcv_clauselist_selectivity() would
>> make this go away, and it's about the simplest solution.
>>
>
>It's probably worth going a little further, and verifying that
>stat_clauses references at least two attributes. We do that further up
>for the original clause list, but it may not be true for the filtered
>list. For example, given a WHERE clause like
>
> c0 > 0 AND c0 < 10 AND (c0 = 0 OR c1 = 1 OR c2 = 2)
>
>and stats on (c0, c1), stat_clauses would include the first 2 clauses,
>but they only reference 1 column, so it would be preferable to not use
>the multivariate stats in that case.
>
Yeah, good point.
Not sure what to do about this:
(c0 > 0 OR c2 < 10) AND (c0 = 0 OR c1 = 1 OR c2 = 2)
In that case we match just the first OR clause, but it references two
attributes. I guess we should ignore that too and handle that later just
like the regular OR clauses.
>
>> Ideally, we'd be able to improve the statistics matching to recognize
>> it has to match all three attributes to match the clause, which in this
>> case would mean the OR clause is passed to clause_selectivity, and we do
>> some magic with extended statistics there.
>>
>> I'll see how complex / backpatchable that would be.
>>
>
>Yes, that seems like a worthwhile thing to do, but I think it goes
>beyond what would normally be back-patched. It would really be a
>feature enhancement rather than a bug fix.
>
True. I don't have a patch yet, but it's likely to be a bit more
invasive than I'd like to backpatch.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services