From: | "Rodrigo Hjort" <rodrigo(dot)hjort(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: LIKE, leading percent, bind parameters and indexes |
Date: | 2006-05-25 17:18:10 |
Message-ID: | 731083980605251018m354005e8ja3f79cba6a6fc992@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I'm not used to the PG Internals. But let me see if I understood that.
The LIKE operator, when applied on a static string and it is not preceded by
'%', causes the planner to search for some indexes in the table in order to
make a index scan. Otherwise, i.e. using leading '%' on static text or bound
paremeter, makes the planner always do a sequential scan. Is that the
scenario?
--
Rodrigo Hjort
http://icewall.org/~hjort
2006/5/23, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> "Rodrigo Hjort" <rodrigo(dot)hjort(at)gmail(dot)com> writes:
> > What happens is that only the "004" block uses the index! The "002"
> code,
> > which also has no leading percent, does a sequential scan. The
> difference
> > between them is that "002" uses bind parameters.
>
> Yeah. The LIKE index optimization depends on seeing a constant LIKE
> pattern at plan time --- otherwise the planner doesn't know what
> indexscan parameters to generate. So a bound-parameter query loses.
>
> Ideas for improving this situation are welcome ... it's not an easy
> problem ...
>
> regards, tom lane
>
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2006-05-25 17:18:18 | Re: Gborg and pgfoundry |
Previous Message | David Fetter | 2006-05-25 17:17:12 | Re: Gborg and pgfoundry |