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

Re: non index use on LIKE on a non pattern string

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: cedric(at)2ndquadrant(dot)com
Cc: pgsql-performance(at)postgresql(dot)org, Guillaume Cottenceau <gc(at)mnc(dot)ch>
Subject: Re: non index use on LIKE on a non pattern string
Date: 2012-06-08 13:57:07
Message-ID: 11641.1339163827@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
=?iso-8859-1?q?C=E9dric_Villemain?= <cedric(at)2ndquadrant(dot)com> writes:
>> I have noticed that with a SELECT query containing the following
>> constraint:
>> 
>> column LIKE ?
>> 
>> and an index on that column, PostgreSQL will not use the index
>> even if the parameter doesn't contain special pattern characters
>> such as %.

> you should have a postgresql 8.3,isn't it ?
>  like is equal to "=" in your case, since 8.4

No, the planner has understood about wildcard-free LIKE patterns
producing an "=" index condition at least since 7.3.  I think what the
OP is complaining about is the problem that the pattern has to be
actually constant (ie, NOT a parameter) before it can be optimized into
an index condition.  This should be better in 9.2 ...

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-06-08 14:10:58
Subject: Re: how to change the index chosen in plan?
Previous:From: Rural HunterDate: 2012-06-08 13:33:08
Subject: how to change the index chosen in plan?

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