On Tue, Nov 19, 2019 at 02:45:54PM +0100, Daniel Gustafsson wrote:
>> On 19 Nov 2019, at 14:38, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
>>
>>> On Tue, Nov 19, 2019 at 01:50:51PM +0100, Manuel Rigger wrote:
>>>
>>> when building PostgreSQL with -enable-cassert, executing the following
>>> statements result in an assertion error:
>>>
>>> CREATE TABLE t0(c0 boolean, c1 boolean, c2 boolean);
>>> INSERT INTO t0 VALUES(FALSE, FALSE, FALSE);
>>> CREATE STATISTICS s0 ON c0, c2 FROM t0;
>>> ANALYZE;
>>> SELECT * FROM t0 WHERE t0.c2 OR t0.c1 OR t0.c0;
>>
>> Yes, I can reproduce it too. mcv_get_match_bitmap expects that
>> stat_clauses will not be empty, but looks like in this situation
>> stat_clauses is indeed NIL. clauselist_selectivity_simple right before
>> actually doesn't insist on stat_clauses being non empty, probably it's
>> just too strict assert.
>
>I might be missing something, but if the clause list is NIL, wouldn't it better
>to exit earlier from statext_mcv_clauselist_selectivity rather than relax the
>Assertion since we will get a 1.0 estimate either way?
>
Hmmm, this is actually a thinko in how we match stats to clauses.
We simply extract attnums from Vars in each clause, and then pick the
statistic matching at least two of those attnums (and we pick the one
matching the most attnums, but that does not matter here).
And then we go and pick all the clauses covered by the statistic,
assuming that we'll get some matching clauses. Unfortunately, that fails
here because it's essentially just a single OR clause. And it references
attributes that are not covered by the statistic.
So we get clauses_attnums = {1,2,3}, we pick the statistic which however
only covers {1,3}, and then we fail because the clause is
c0 OR c1 OD c2
which is not actually covered by the statistic, because of c1. Kaboom!
Yes, adding the condition to statext_mcv_clauselist_selectivity() would
make this go away, and it's about the simplest solution.
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.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
