Re: Redundant bitmap index scans on smallint column

On Tue, Sep 6, 2011 at 19:51, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marti Raudsepp <marti(at)juffo(dot)org> writes:
>> So it seems the more fundamental issue is that b=0 and b='0'
>> conditions are normalized differently when b is smallint.
>
> That's not a "fundamental issue", if by that you mean that it's a bug to
> be fixed.

That was a bad choice of words, I meant "fundamental reason".

> I realized that that patch is no good because it will break estimation
> for inner-indexscan cases, where the selectivity of a bitmap index might
> legitimately be better than what you'd get from the restriction clauses
> alone.  Possibly we could adapt the idea to use in choose_bitmap_and,
> but it'll take more thought.

Anyway the conditions for triggering this kind of plan are rather
obscure, so unless there is a simple solution, it's probably not worth
fixing.

You need a constant query WHERE expression and partial index that
mixes b=0 and b='0' syntax, on a non-integer column. The workaround is
also straightforward: be consistent how you write the expression.

The planner will only choose such a plan when you have a fairly large
heap and small indexes, so the heap scan is likely to dominate query
time anyway. In the original query it was a matter of 66 ms (single
bitmap scan) vs 82 ms (redundant bitmap scans)

Regards,
Marti