Skip site navigation (1) Skip section navigation (2)

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

From: Richard Yen <dba(at)richyen(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: query plan with index having a btrim is different for strings of different length
Date: 2008-12-10 19:28:43
Message-ID: E8AE9590-4E30-49C0-B11F-D7131009A095@richyen.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Dec 9, 2008, at 3:27 PM, Tom Lane wrote:

> Richard Yen <dba(at)richyen(dot)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 

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-12-10 19:34:33
Subject: Re: query plan with index having a btrim is different for strings of different length
Previous:From: Greg SmithDate: 2008-12-10 19:13:03
Subject: Re: Experience with HP Smart Array P400 and SATA drives?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group