Skip site navigation (1) Skip section navigation (2)

Re: LIKE, leading percent, bind parameters and indexes

From: "Rodrigo Hjort" <rodrigo(dot)hjort(at)gmail(dot)com>
To: "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: LIKE, leading percent, bind parameters and indexes
Date: 2006-05-25 23:41:17
Message-ID: 731083980605251641q144be605tb9067782c3d8071@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
>
> I think more exactly, the planner can't possibly know how to plan an
> indexscan with a leading '%', because it has nowhere to start.
>

The fact is that index scan is performed on LIKE expression on a string not
preceded by '%', except when bound parameter is used.

select * from table where field like 'THE NAME%'; -- index scan
select * from table where field like '%THE NAME%'; -- seq scan
select * from table where field like :bind_param; -- seq scan (always)

Regards,

Rodrigo Hjort
http://icewall.org/~hjort

In response to

Responses

pgsql-hackers by date

Next:From: DaveDate: 2006-05-26 00:23:53
Subject: Creating a case insensitive data type
Previous:From: Tom LaneDate: 2006-05-25 22:16:14
Subject: Re: XLogArchivingActive

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group