Marti Raudsepp <marti@juffo.org> writes:
> On Mon, Sep 5, 2011 at 21:01, Tom Lane <tgl@sss.pgh.pa.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
