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

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

From: "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>
To: "Richard Yen" <dba(at)richyen(dot)com>
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-09 22:41:33
Message-ID: e7f9235d0812091441g1fe49edbr7e1e3beded4f247b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Dec 9, 2008 at 2:56 PM, Richard Yen <dba(at)richyen(dot)com> wrote:

> In practice, the difference is 300+ seconds when $LASTNAME == 5 chars and <1
> second when $LASTNAME != 5 chars.
>
> Would anyone know what's going on here?  Is there something about the way
> btrim works, or perhaps with the way indexes are created?  It's strange that
> the query plan would change for just one case ("Jones," "Smith," "Brown,"
> etc., all cause the query plan to use that extra heap scan).

Those are likely common names, and may be showing up in the table
stats as common values, causing the planner to change things around.
Does this hold even for non-existent 5-character lastname strings?

Speaking of table statistics, might be worth upping the statistics
target on that table/column, analyzing, and seeing if you get
different results.

-- 
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2008-12-09 22:58:44
Subject: Re: Need help with 8.4 Performance Testing
Previous:From: Tom LaneDate: 2008-12-09 22:38:35
Subject: Re: Need help with 8.4 Performance Testing

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