From: Cédric Villemain <cedric@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, Guillaume Cottenceau <gc@mnc.ch>
Subject: Re: non index use on LIKE on a non pattern string
Date: Fri, 8 Jun 2012 16:31:10 +0200
Message-ID: <201206081631.14321.cedric@2ndquadrant.com> (raw)
In-Reply-To: <11641.1339163827@sss.pgh.pa.us>
References: <m3mx4eys6o.fsf@mnc.ch>
<201206081351.54589.cedric@2ndquadrant.com>
<11641.1339163827@sss.pgh.pa.us>
Le vendredi 8 juin 2012 15:57:07, Tom Lane a écrit :
> =?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 ...
Oops, maybe I shuffled with this
* xxx_pattern_ops indexes can now be used for simple equality comparisons,
not only for LIKE (Tom)
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Attachments:
signature.asc (application/pgp-signature, 489B) download
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: cedric@2ndquadrant.com, tgl@sss.pgh.pa.us, gc@mnc.ch
Subject: Re: non index use on LIKE on a non pattern string
In-Reply-To: <201206081631.14321.cedric@2ndquadrant.com>
* 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