From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: <depesz@depesz.com>
Cc: "'Kiran'" <kumar.m.kiran@gmail.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Best Fit SQL query statement
Date: Tue, 14 Aug 2007 10:59:37 -0300
Organization: IP-TEL
Message-ID: <018801c7de7b$5a25aa30$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <20070810225953.GA28341@depesz.com>
Thread-Index: Acfboi9pRQxo1E+KTGi76XIZQSGX2gCQKwVw


De: hubert depesz lubaczewski [mailto:depesz@depesz.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.