Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0A002D84656 for ; Fri, 8 Jun 2012 11:31:38 -0300 (ADT) Received: from moutng.kundenserver.de ([212.227.17.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sd0Dv-0004LY-6G for pgsql-performance@postgresql.org; Fri, 08 Jun 2012 14:31:37 +0000 Received: from obelix.localnet (AToulouse-556-1-160-221.w92-162.abo.wanadoo.fr [92.162.24.221]) by mrelayeu.kundenserver.de (node=mreu0) with ESMTP (Nemesis) id 0MTL2h-1SV88S0ZOB-00S36y; Fri, 08 Jun 2012 16:31:15 +0200 From: =?iso-8859-15?q?C=E9dric_Villemain?= Reply-To: cedric@2ndquadrant.com Organization: 2ndQuadrant To: Tom Lane Subject: Re: non index use on LIKE on a non pattern string Date: Fri, 8 Jun 2012 16:31:10 +0200 User-Agent: KMail/1.13.7 (Linux/3.2.0-2-amd64; KDE/4.7.4; x86_64; ; ) Cc: pgsql-performance@postgresql.org, Guillaume Cottenceau References: <201206081351.54589.cedric@2ndquadrant.com> <11641.1339163827@sss.pgh.pa.us> In-Reply-To: <11641.1339163827@sss.pgh.pa.us> X-KMail-Markup: true MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart1339189493.zqbgRAPI6N"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <201206081631.14321.cedric@2ndquadrant.com> X-Provags-ID: V02:K0:1h42NRJcR7j8tuNyVfbzwzpd2je1CIaOE+DHBLsI8RF nhwnaFgqiCbpK674JmUiPwEn9N1Exl3HGJamDuAabL3ZxqKeqj fD2u1X3KyMADZeqDvV2bzM7cUIE04/1waYFRO1ZFJMO7cARH7q lQbVj+MOeSkvmmUVZz99bPMyPhlV1lWGwzB1oWqVHJDgeKNJw9 de4fqbfFOP5RcmIkTwKUBmLTfwXW1v3GzuNw487gqe19kKX2EP oXykvQBxZ1eNz8jwGuRNK1jgPjbbDD/i7Gsgw8Sz8og4hbzKGg gMLnSw3x8tMQZ6IK+dsjicGZ4YecQY53Xl2nnMi/3hLtSuWUQU /ECaUVj7rY6nGuFrrwFIszhB34OzuRxCxz0gPXSPT X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201206/44 X-Sequence-Number: 47070 --nextPart1339189493.zqbgRAPI6N Content-Type: multipart/alternative; boundary="Boundary-01=_uyg0PLgfXJPzIep" Content-Transfer-Encoding: 7bit --Boundary-01=_uyg0PLgfXJPzIep Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable Le vendredi 8 juin 2012 15:57:07, Tom Lane a =E9crit : > =3D?iso-8859-1?q?C=3DE9dric_Villemain?=3D writes: > >> I have noticed that with a SELECT query containing the following > >> constraint: > >>=20 > >> column LIKE ? > >>=20 > >> and an index on that column, PostgreSQL will not use the index > >> even if the parameter doesn't contain special pattern characters > >> such as %. > >=20 > > you should have a postgresql 8.3,isn't it ? > >=20 > > like is equal to "=3D" in your case, since 8.4 >=20 > No, the planner has understood about wildcard-free LIKE patterns > producing an "=3D" 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=20 * xxx_pattern_ops indexes can now be used for simple equality comparisons,= =20 not only for LIKE (Tom)=20 =2D-=20 C=E9dric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - D=E9veloppement, Expertise et Formation --Boundary-01=_uyg0PLgfXJPzIep Content-Type: text/html; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable

Le vendredi= 8 juin 2012 15:57:07, Tom Lane a =E9crit :

> =3D?is= o-8859-1?q?C=3DE9dric_Villemain?=3D <cedric@2ndquadrant.com> writes:<= /p>

> >&g= t; I have noticed that with a SELECT query containing the following

> >&g= t; constraint:

> >&g= t;

> >&g= t; column LIKE ?

> >&g= t;

> >&g= t; and an index on that column, PostgreSQL will not use the index

> >&g= t; even if the parameter doesn't contain special pattern characters

> >&g= t; such as %.

> > <= /p>

> > y= ou should have a postgresql 8.3,isn't it ?

> > <= /p>

> > = like is equal to "=3D" in your case, since 8.4

> No, th= e planner has understood about wildcard-free LIKE patterns

> produc= ing an "=3D" index condition at least since 7.3. I think what th= e

> OP is = complaining about is the problem that the pattern has to be

> actual= ly constant (ie, NOT a parameter) before it can be optimized into

> an ind= ex condition. This should be better in 9.2 ...

&nb= sp;

Oops, maybe= I shuffled with this

* xxx_pattern_ops indexes = can now be used for simple equality comparisons, not only for LIKE (Tom)

&nb= sp;

C=E9dric Vi= llemain +33 (0)6 20 30 22 52

http://2ndQ= uadrant.fr/

PostgreSQL:= Support 24x7 - D=E9veloppement, Expertise et Formation

--Boundary-01=_uyg0PLgfXJPzIep-- --nextPart1339189493.zqbgRAPI6N Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) iQEcBAABAgAGBQJP0gyuAAoJENsgH0yzVBebnnAH/0FkuMtk8jMwsGdmsEM0C57M dftAPJaVsEFDGl3kwJi3Um+88TG8Bx9/N1aFDjVtwSQtZ+q1UU8vHyYkLcqU7vVM XG7p6iL3gvYbdh1OXyW0ik7NMt5L2/lpl9sqmlsSFGw1EYDwI9YgvbLwxphm5x5w 2wGKucZI9LaQM7fe1rJA8WlYEivhOVeBO8Y07jY+iT+fA4DtSYsEpC1SxuyeX8IH 8Cglz4WnFqTAnCnIIFqyeToIgcpRBegRdY3DyN1/EuIkuN3tOsMEg22ed8M718AD vroVeGqYOkH+iIXdSpXbi13alC80Kb0/4EQKgy+xIT8y5hz7ElX1P2OZUAorJvU= =9LHP -----END PGP SIGNATURE----- --nextPart1339189493.zqbgRAPI6N--