pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feednon index use on LIKE on a non pattern string
4+ messages / 3 participants
[nested] [flat]
* non index use on LIKE on a non pattern string
@ 2012-06-08 11:11 Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Guillaume Cottenceau @ 2012-06-08 11:11 UTC (permalink / raw)
To: pgsql-performance
Hello,
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 %.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: non index use on LIKE on a non pattern string
@ 2012-06-08 11:51 Cédric Villemain <cedric@2ndquadrant.com>
parent: Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Cédric Villemain @ 2012-06-08 11:51 UTC (permalink / raw)
To: pgsql-performance; +Cc: Guillaume Cottenceau <gc@mnc.ch>
> 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
Also you probably want to have a look at
http://www.postgresql.org/docs/9.1/static/indexes-opclass.html
about your index definition (add the "text_pattern_ops" when required)
--
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: non index use on LIKE on a non pattern string
@ 2012-06-08 13:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Cédric Villemain <cedric@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2012-06-08 13:57 UTC (permalink / raw)
To: cedric@2ndquadrant.com; +Cc: pgsql-performance@postgresql.org, Guillaume Cottenceau <gc@mnc.ch>
=?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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: non index use on LIKE on a non pattern string
@ 2012-06-08 14:31 Cédric Villemain <cedric@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Cédric Villemain @ 2012-06-08 14:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@postgresql.org, Guillaume Cottenceau <gc@mnc.ch>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-06-08 14:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-08 11:11 non index use on LIKE on a non pattern string Guillaume Cottenceau <gc@mnc.ch>
2012-06-08 11:51 ` Cédric Villemain <cedric@2ndquadrant.com>
2012-06-08 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-06-08 14:31 ` Cédric Villemain <cedric@2ndquadrant.com>
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