Date: Sat, 11 Aug 2007 00:59:53 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
To: Fernando Hevia <fhevia@ip-tel.com.ar>
Cc: 'Kiran' <kumar.m.kiran@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: Best Fit SQL query statement
Message-ID: <20070810225953.GA28341@depesz.com>
Reply-To: depesz@depesz.com
References: <20070810075949.GA31829@depesz.com>
	<00e101c7db86$51b3d8b0$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <00e101c7db86$51b3d8b0$8f01010a@iptel.com.ar>
User-Agent: Mutt/1.5.13 (2006-08-11)

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)