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

Поиск
Список
Период
Сортировка
От Moritz Onken
Тема Re: Planner should use index on a LIKE 'foo%' query
Дата
Msg-id 9EE32DF9-562C-45EF-829D-A877CBEC3C37@houseofdesign.de
обсуждение исходный текст
Ответ на Re: Planner should use index on a LIKE 'foo%' query  (Matthew Wakeling <matthew@flymine.org>)
Ответы Re: Planner should use index on a LIKE 'foo%' query  (Matthew Wakeling <matthew@flymine.org>)
Список pgsql-performance
Am 30.06.2008 um 12:19 schrieb Matthew Wakeling:
>
>> select count(1) from result where url in (select shorturl from item
>> where shorturl = result.url);
>
> What on earth is wrong with writing it like this?
>
> SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item
> WHERE
>   item.shorturl = result.url) AS a

I tried the this approach but it's slower than WHERE IN in my case.

>
> It seems you could benefit from the prefix project, which support
> indexing
> your case of prefix searches. Your query would then be:
>  SELECT count(*) FROM result r JOIN item i ON r.url @> i.url;
>
> The result.url column would have to made of type prefix_range, which
> casts
> automatically to text when needed.
>
> Find out more about the prefix projects at those urls:
>  http://pgfoundry.org/projects/prefix
>  http://prefix.projects.postgresql.org/README.html
>
> Regards,
> --
> dim

Thanks for that! looks interesting.

regards

В списке pgsql-performance по дате отправления:

Предыдущее
От: John Beaver
Дата:
Сообщение: Re: sequence scan problem
Следующее
От: Matthew Wakeling
Дата:
Сообщение: Re: Planner should use index on a LIKE 'foo%' query