From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: depesz@depesz.com
Cc: 'Kiran' <kumar.m.kiran@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Best Fit SQL query statement
Date: Tue, 14 Aug 2007 10:59:37 -0300
Message-ID: <018801c7de7b$5a25aa30$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <20070810225953.GA28341@depesz.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar, depesz@depesz.com, kumar.m.kiran@gmail.com
Subject: Re: Best Fit SQL query statement
In-Reply-To: <018801c7de7b$5a25aa30$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox