DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=QIXhJCdAlMI22s+Ew47GE1TQyhR86b7MvYWHWw0hRWgxV29SZ799ZqSk7IOeDxmlTA
	gU2BLMQ61EetnuNXRQskw3yfCKRQewOSPER2J2Q0IsGkiRyJihobp+Io6gO21PveMMwh
	1boH3LVe2ZTIBwnNpAAr0XBeSfUPJeXOnLJQA=
Message-ID: <e7f9235d0812091441g1fe49edbr7e1e3beded4f247b@mail.gmail.com>
Date: Tue, 9 Dec 2008 17:41:33 -0500
From: "David Wilson" <david.t.wilson@gmail.com>
To: "Richard Yen" <dba@richyen.com>
Subject: Re: query plan with index having a btrim is different for strings of
	different length
Cc: pgsql-performance@postgresql.org
In-Reply-To: <751F49DC-1142-4E9C-95C8-BC3C229B3F0B@richyen.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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