Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 22034D84657 for ; Fri, 8 Jun 2012 10:57:29 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sczgq-0003iH-6x for pgsql-performance@postgresql.org; Fri, 08 Jun 2012 13:57:28 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q58Dv7av011642; Fri, 8 Jun 2012 09:57:07 -0400 (EDT) From: Tom Lane To: cedric@2ndquadrant.com cc: pgsql-performance@postgresql.org, Guillaume Cottenceau Subject: Re: non index use on LIKE on a non pattern string In-reply-to: <201206081351.54589.cedric@2ndquadrant.com> References: <201206081351.54589.cedric@2ndquadrant.com> Comments: In-reply-to =?iso-8859-1?q?C=E9dric_Villemain?= message dated "Fri, 08 Jun 2012 13:51:50 +0200" Date: Fri, 08 Jun 2012 09:57:07 -0400 Message-ID: <11641.1339163827@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201206/41 X-Sequence-Number: 47067 =?iso-8859-1?q?C=E9dric_Villemain?= 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