From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | "Rodrigo Hjort" <rodrigo(dot)hjort(at)gmail(dot)com> |
Cc: | "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: LIKE, leading percent, bind parameters and indexes |
Date: | 2006-05-26 04:35:20 |
Message-ID: | 877j49qu7r.fsf@stark.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
"Rodrigo Hjort" <rodrigo(dot)hjort(at)gmail(dot)com> writes:
> > I think more exactly, the planner can't possibly know how to plan an
> > indexscan with a leading '%', because it has nowhere to start.
>
> The fact is that index scan is performed on LIKE expression on a string not
> preceded by '%', except when bound parameter is used.
>
> select * from table where field like 'THE NAME%'; -- index scan
> select * from table where field like '%THE NAME%'; -- seq scan
> select * from table where field like :bind_param; -- seq scan (always)
Just for reference I found that both Oracle and MSSQL (back when last I used
it, many years ago) did use an index scan for the following case:
select * from table where field like :bind_param || '%'
At the time this seemed perfectly logical but now that I have more experience
it seems hard to justify. There's no principled reason to think this is any
more likely than a plain :bind_param to be an indexable scan.
However in practice this worked great. I rarely if ever put % characters into
the bind parameter and the index scan was exactly what I, as a user, expected.
Even if there's resistance to having this form be treated as indexable there
is certainly a use case for something like this. If not this then something
like
WHERE escape(:bind_param)||'%'
but that would be pretty hard to recognize, certainly much harder than a
simple :bind_param || '%'.
--
greg
From | Date | Subject | |
---|---|---|---|
Next Message | Hans-Jürgen Schönig | 2006-05-26 06:21:56 | Bug with UTF-8 character |
Previous Message | Josh Berkus | 2006-05-26 00:56:54 | Re: Gborg and pgfoundry |