From: David Wilson <david.t.wilson@gmail.com>
To: Richard Yen <dba@richyen.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query plan with index having a btrim is different for strings of different length
Date: Tue, 9 Dec 2008 17:41:33 -0500
Message-ID: <e7f9235d0812091441g1fe49edbr7e1e3beded4f247b@mail.gmail.com> (raw)
In-Reply-To: <751F49DC-1142-4E9C-95C8-BC3C229B3F0B@richyen.com>
References: <751F49DC-1142-4E9C-95C8-BC3C229B3F0B@richyen.com>
On Tue, Dec 9, 2008 at 2:56 PM, Richard Yen <dba@richyen.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.t.wilson@gmail.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.t.wilson@gmail.com, dba@richyen.com
Subject: Re: query plan with index having a btrim is different for strings of different length
In-Reply-To: <e7f9235d0812091441g1fe49edbr7e1e3beded4f247b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox