Marti Raudsepp <ma...@juffo.org> writes: > On Mon, Sep 5, 2011 at 21:01, Tom Lane <t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers