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

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