Re: Redundant bitmap index scans on smallint column

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