pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kurt De Grave <kurt.degrave@pharmadm.com>
To: pgsql-performance@postgresql.org
Subject: parameterized LIKE does not use index
Date: Wed, 22 Jun 2005 20:50:13 +0200 (CEST)
Message-ID: <Pine.LNX.4.58.0506222023360.6070@matlab.pharmadm.com> (raw)


Hi,

Consider the where-clauses:

WHERE lower(col) LIKE 'abc';
WHERE lower(col) LIKE 'abc%';

these will both use a b-tree functional index in lower(col) if one exists.  
The clause

WHERE lower(col) LIKE '%abc';

can't use the index as you would expect, because of the wildcard at the
front (as mentioned in the manual).  Thus, it has to do a seqscan, on what
in my case is a very large table.  But still that's not too bad, because I
expect an overwhelming amount of the simple cases, and only very few that
start with a percentage sign. Now, what's problematic is if i replace the
literal with a parameter, like this:

WHERE lower(col) LIKE ?

It seems that the parameterized query gets compiled once, and because the
parameter is not yet known, one cannot be sure it doesn't start with a
percentage sign.  Using the parameterized version causes ALL cases to use
a seqscan.

Of course, I could modify the application and send different SQL depending
on which case we're in or just constructing a query with a literal each
time, but is there a way to add a hint to the SQL that would cause the
query to be re-planned if it's a case that could use the index?  Or can I 
convince the (Perl) driver to do so?



kurt.



view thread (7+ messages)  latest in thread

Message-ID: <Pine.LNX.4.58.0506222023360.6070@matlab.pharmadm.com>
Permalink:  ../Pine.LNX.4.58.0506222023360.6070@matlab.pharmadm.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.58.0506222023360.6070@matlab.pharmadm.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kurt.degrave@pharmadm.com
  Subject: Re: parameterized LIKE does not use index
  In-Reply-To: <Pine.LNX.4.58.0506222023360.6070@matlab.pharmadm.com>
  References: <Pine.LNX.4.58.0506222023360.6070@matlab.pharmadm.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