From: Kurt De Grave <Kurt.DeGrave@student.kuleuven.ac.be>
To: pgsql-performance@postgresql.org
Subject: parameterized LIKE does not use index
Date: Wed, 22 Jun 2005 21:25:20 +0200
Message-ID: <42B9BB20.7050109@student.kuleuven.ac.be> (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. 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.
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: Kurt.DeGrave@student.kuleuven.ac.be
Subject: Re: parameterized LIKE does not use index
In-Reply-To: <42B9BB20.7050109@student.kuleuven.ac.be>
* 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