From: Tom Lane <tgl@sss.pgh.pa.us>
To: cedric@2ndquadrant.com
Cc: pgsql-performance@postgresql.org, Guillaume Cottenceau <gc@mnc.ch>
Subject: Re: non index use on LIKE on a non pattern string
Date: Fri, 08 Jun 2012 09:57:07 -0400
Message-ID: <11641.1339163827@sss.pgh.pa.us> (raw)
In-Reply-To: <201206081351.54589.cedric@2ndquadrant.com>
References: <m3mx4eys6o.fsf@mnc.ch>
<201206081351.54589.cedric@2ndquadrant.com>
=?iso-8859-1?q?C=E9dric_Villemain?= <cedric@2ndquadrant.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
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, cedric@2ndquadrant.com, gc@mnc.ch
Subject: Re: non index use on LIKE on a non pattern string
In-Reply-To: <11641.1339163827@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