How planner decides left-anchored LIKE can use index

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: How planner decides left-anchored LIKE can use index
Date: 2007-09-07 00:06:44
Message-ID: fbq4md$3056$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-09-07 00:28:16 Re: How planner decides left-anchored LIKE can use index
Previous Message Tom Lane 2007-09-06 23:18:46 Re: Reasonable amount of indices