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

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