Re: Best Fit SQL query statement

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.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)