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)