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.
Why doesn't this occur when b is bigint, though?
> I'm not really eager to change that, especially in view of the fact
> that a plain (non bitmap) indexscan is considerably cheaper than any
> of these alternatives in this example.
I did hit this case with a real query, with enable_indexscan allowed.
I just couldn't figure out how to make a more similar test case.
> + tuples_fetched = Max(tuples_fetched, baserel->rows);
> 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.
Regards,
Marti