Re: LIKE, leading percent, bind parameters and indexes

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
>