Re: Planner should use index on a LIKE 'foo%' query

От: Steinar H. Gunderson
Тема: Re: Planner should use index on a LIKE 'foo%' query
Дата: ,
Msg-id: 20080628191931.GB13013@uio.no
(см: обсуждение, исходный текст)
Ответ на: Planner should use index on a LIKE 'foo%' query  (Moritz Onken)
Ответы: Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken)
Список: pgsql-performance

Скрыть дерево обсуждения

Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )
 Re: Planner should use index on a LIKE 'foo%' query  ("Steinar H. Gunderson", )
  Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )
   Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )
    Re: Planner should use index on a LIKE 'foo%' query  (Matthew Wakeling, )
     Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )
      Re: Planner should use index on a LIKE 'foo%' query  (Matthew Wakeling, )
       Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )
 Re: Planner should use index on a LIKE 'foo%' query  (Dimitri Fontaine, )
 Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )
  Re: Planner should use index on a LIKE 'foo%' query  (Matthew Wakeling, )
   Re: Planner should use index on a LIKE 'foo%' query  (Moritz Onken, )

On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
>  SELECT distinct url from item where url like 'http://www.micro%' limit
> 10;

Here, the planner knows the pattern beforehand, and can see that it's a
simple prefix.
> select *
>  from result
>  where exists
>    (select * from item where item.url LIKE result.url || '%' limit 1)
> limit 10;

Here it cannot (what if result.url was '%foo%'?).

Try using something like (item.url >= result.url && item.url <= result.url ||
'z'), substituting an appropriately high character for 'z'.

> The only explaination is that I don't use a constant when comparing the
> values. But actually it is a constant...

It's not a constant at planning time.

Also note that you'd usually want to use IN instead of a WHERE EXISTS.

/* Steinar */
--
Homepage: http://www.sesse.net/


В списке pgsql-performance по дате сообщения:

От: Gregory Stark
Дата:
Сообщение: Re: Subquery WHERE IN or WHERE EXISTS faster?
От: Ulrich
Дата:
Сообщение: Re: Subquery WHERE IN or WHERE EXISTS faster?