From: hubert depesz lubaczewski <depesz@depesz.com>
To: Fernando Hevia <fhevia@ip-tel.com.ar>
Cc: Kiran <kumar.m.kiran@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Best Fit SQL query statement
Date: Sat, 11 Aug 2007 00:59:53 +0200
Message-ID: <20070810225953.GA28341@depesz.com> (raw)
In-Reply-To: <00e101c7db86$51b3d8b0$8f01010a@iptel.com.ar>
References: <20070810075949.GA31829@depesz.com>
<00e101c7db86$51b3d8b0$8f01010a@iptel.com.ar>
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)
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: depesz@depesz.com, fhevia@ip-tel.com.ar, kumar.m.kiran@gmail.com
Subject: Re: Best Fit SQL query statement
In-Reply-To: <20070810225953.GA28341@depesz.com>
* 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