Re: Redundant bitmap index scans on smallint column

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> On Mon, Sep 5, 2011 at 21:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What we have to start with is WHERE b = 0::smallint, which the planner
>> is able to prove implies the index predicate WHERE b = 0::integer,
>> so both indexes are considered. But the check for predicate redundancy
>> in choose_bitmap_and() only uses simple equality not provability,
>> so it does not recognize that the two indexes are entirely redundant.

> 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.

> Why doesn't this occur when b is bigint, though?

Looks like the bigint index is enough larger that it's not thought to be
worth the extra cost to scan. The underlying assumptions are all the
same though.

>> I tested this and it fixes this particular example, by preventing the
>> heap scan part of the plan from looking cheaper than it does with just
>> one index in use.

> Cool, this should take care of the simpler cases.

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.

regards, tom lane