From: Tom Lane <tgl@sss.pgh.pa.us>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: How planner decides left-anchored LIKE can use index
Date: Thu, 06 Sep 2007 20:28:16 -0400
Message-ID: <6716.1189124896@sss.pgh.pa.us> (raw)
In-Reply-To: <fbq4md$3056$1@news.hub.org>
References: <fbq4md$3056$1@news.hub.org>
"Carlo Stonebanks" <stonec.register@sympatico.ca> writes:
> There is an index on lower(last_name). I have seen the planner convert the
> LIKE to lower(last_name) >= 'smith' and lower(last_name) < 'smiti' on 8.2.4
> systems, but a slow sequence scan and filter on 8.1.9 - is this related to
> the version difference (8.1.9 vs 8.2.4) or is this related to something like
> operators/classes that have been installed?
Most likely you used C locale for the 8.2.4 installation and some other
locale for the other one.
In non-C locale you can still get the optimization if you create an
index using the text_pattern_ops opclass ... but beware that this index
is useless for the normal locale-aware operators.
regards, tom lane
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: tgl@sss.pgh.pa.us, stonec.register@sympatico.ca
Subject: Re: How planner decides left-anchored LIKE can use index
In-Reply-To: <6716.1189124896@sss.pgh.pa.us>
* 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