Re: Best Fit SQL query statement

De: hubert depesz lubaczewski [mailto:depesz(at)depesz(dot)com]

>>On Fri, Aug 10, 2007 at 04:40:34PM -0300, Fernando Hevia wrote:
>> Found your query is shorter and clearer, problem is I couldn't have it
use
>> an index. Thought it was a locale issue but adding a 2nd index with
>> varchar_pattern_ops made no difference.
>> In result, it turned out to be too slow in comparison to the function. Am
I
>> missing something?
>> rd=# explain select prefijo
>> rd-# FROM numeracion
>> rd-# WHERE '3514269565' LIKE prefijo || '%'
>> rd-# ORDER BY LENGTH(prefijo) DESC
>> rd-# LIMIT 1;

> unfortunatelly this query will be hard to optimize.
> i guess that functional approach will be the fastest, but you can try
> with something like this:
>
> select prefijo
> from numeracion
> where prefijo in (
> select substr('3514269565',1,i)
> from generate_series(1, length('3514269565')) i
> )
> order by length(prefijo) desc LIMIT 1;
>
>it should be faster then the previous approach, but it will most
>probably not be as fast as function.

Actually, I find this variant nearly as fast as the function. The
generate_series can be limited to known minimum and maximum prefix lengths
in order to speed up the query a bit more.

Works quite well.

Cheers,
Fernando.