Re: brin regression test intermittent failures

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> Tom Lane wrote:
>> Also, further experimentation shows that there are about 30 entries in the
>> brinopers table that give rise to seqscan plans even when we're commanding
>> a bitmap scan, presumably because those operators aren't brin-indexable.
>> They're not the problematic cases, but things like
>>
>> ((charcol)::text > 'A'::text)
>>
>> Is there a reason to have such things in the table, or is this just a
>> thinko? Or is it actually a bug that we're getting such plans?

> No, I left those there knowing that there are no plans involving brin --
> in a way, they provide some future proofing if some of those operators
> are made indexable later.

On closer investigation, I think the ones involving charcol are a flat
out bug in the test, namely failure to quote "char". Observe:

regression=# explain select ctid from brintest where charcol = 'A'::char;
QUERY PLAN
----------------------------------------------------------
Seq Scan on brintest (cost=0.00..101.88 rows=1 width=6)
Filter: ((charcol)::text = 'A'::text)
(2 rows)

regression=# explain select ctid from brintest where charcol = 'A'::"char";
QUERY PLAN
-----------------------------------------------------------------------
Bitmap Heap Scan on brintest (cost=48.02..58.50 rows=3 width=6)
Recheck Cond: (charcol = 'A'::"char")
-> Bitmap Index Scan on brinidx (cost=0.00..48.02 rows=3 width=0)
Index Cond: (charcol = 'A'::"char")
(4 rows)

Presumably we'd like to test the latter case not the former.

The other cases that I found involve cidrcol, and seem to represent
an actual bug in the brin planning logic, ie failure to disregard a
no-op cast. I'll look closer.

> I couldn't think of a way to test that the plans are actually using the
> brin index or not, but if we can do that in some way, that would be
> good.

Yeah, we can do that --- the way I found out there's a problem is to
modify the test script to check the output of EXPLAIN.

So at this point it looks like (1) chipmunk's issue might be explained
by lack of forced ORDER BY; (2) the test script could be improved to
test more carefully, and it has got an issue with "char" vs char;
(3) there might be a planner bug.

regards, tom lane