Re: query plan with index having a btrim is different for strings of different length

On Dec 9, 2008, at 3:27 PM, Tom Lane wrote:

> Richard Yen <dba@richyen.com> writes:
>> I've discovered a peculiarity with using btrim in an index and was
>> wondering if anyone has any input.
>
> What PG version is this?

This is running on 8.3.3

> In particular, I'm wondering if it's one of the early 8.2.x releases,
> which had some bugs in and around choose_bitmap_and() that caused
> them to sometimes make weird choices of indexes in a BitmapAnd plan
> structure ...
>
> Like David, I'm pretty dubious that the behavior has anything to do
> with
> strings being 5 characters long exactly; but it could very much depend
> on whether the string you choose is a common last name or not.  That
> would change the estimated number of matching rows and hence affect
> the
> apparent relative attractiveness of different indexes.


You guys are right.  I tried "Miller" and gave me the same result.  Is
there any way to tune this so that for the common last names, the
query run time doesn't jump from <1s to >300s?
Thanks for the help!
--Richard