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/~hjort2006/5/23, Tom Lane <tgl@sss.pgh.pa.us>: "Rodrigo Hjort" <rodrigo.hjort@gmail.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
