pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow planner decides left-anchored LIKE can use index
2+ messages / 2 participants
[nested] [flat]
* How planner decides left-anchored LIKE can use index
@ 2007-09-07 00:06 Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 1 reply; 2+ messages in thread
From: Carlo Stonebanks @ 2007-09-07 00:06 UTC (permalink / raw)
To: pgsql-performance
Exactly when does the planner decide that a left-anchored like can use the
index?
I have replaced a WHEN lower(last_name) = 'smith'
with WHEN lower(last_name) like 'smith%'
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?
Carlo
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How planner decides left-anchored LIKE can use index
@ 2007-09-07 00:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-09-07 00:28 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-09-07 00:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-07 00:06 How planner decides left-anchored LIKE can use index Carlo Stonebanks <stonec.register@sympatico.ca>
2007-09-07 00:28 ` Tom Lane <tgl@sss.pgh.pa.us>
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