Re: LIKE, leading percent, bind parameters and indexes
| От | Rodrigo Hjort |
|---|---|
| Тема | Re: LIKE, leading percent, bind parameters and indexes |
| Дата | |
| Msg-id | 731083980605251018m354005e8ja3f79cba6a6fc992@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: LIKE, leading percent, bind parameters and indexes (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: LIKE, leading percent, bind parameters and indexes
|
| Список | 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
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@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
В списке pgsql-hackers по дате отправления: