From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Redundant bitmap index scans on smallint column |
Date: | 2011-09-06 16:51:07 |
Message-ID: | 9636.1315327867@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-09-06 17:24:08 | Re: BUG #5957: createdb with description and md5 auth forces to provide password twice |
Previous Message | Ants Aasma | 2011-09-06 15:40:04 | Re: [COMMITTERS] pgsql: Clean up the #include mess a little. |