pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBest Fit SQL query statement
8+ messages / 4 participants
[nested] [flat]
* Best Fit SQL query statement
@ 2007-08-06 08:57 Kiran <kumar.m.kiran@gmail.com>
2007-08-06 15:15 ` Re: Best Fit SQL query statement Rodrigo De León <rdeleonp@gmail.com>
2007-08-10 07:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 8+ messages in thread
From: Kiran @ 2007-08-06 08:57 UTC (permalink / raw)
To: pgsql-sql
All,
Could anyone help me in writing Best Fit SQL statement.
Suppose we have table t1 with coloumn t1 (text) with following rows.
98456
98457
9845
9846
984
985
98
99
and if I query on 98456 the result must be 98456,
However if I query on 98455 the result must be 9845
and If I query 9849 the result must be 984
Regards,
Kiran
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
@ 2007-08-06 15:15 ` Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Rodrigo De León @ 2007-08-06 15:15 UTC (permalink / raw)
To: pgsql-sql
On Aug 6, 3:57 am, Kiran <kumar.m.ki...@gmail.com> wrote:
> and if I query on 98456 the result must be 98456,
> However if I query on 98455 the result must be 9845
> and If I query 9849 the result must be 984
SELECT MAX(t1)
FROM t1
WHERE '9849' LIKE t1 || '%';
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
@ 2007-08-10 07:59 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-10 19:40 ` Re: Best Fit SQL query statement Fernando Hevia <fhevia@ip-tel.com.ar>
1 sibling, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2007-08-10 07:59 UTC (permalink / raw)
To: Kiran <kumar.m.kiran@gmail.com>; +Cc: pgsql-sql
On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote:
> Could anyone help me in writing Best Fit SQL statement.
> Suppose we have table t1 with coloumn t1 (text) with following rows.
> 98456
> 98457
> 9845
> 9846
> 984
> 985
> 98
> 99
> and if I query on 98456 the result must be 98456,
> However if I query on 98455 the result must be 9845
> and If I query 9849 the result must be 984
select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1) desc limit 1;
should be ok.
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
2007-08-10 07:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
@ 2007-08-10 19:40 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-08-10 22:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 8+ messages in thread
From: Fernando Hevia @ 2007-08-10 19:40 UTC (permalink / raw)
To: depesz@depesz.com; 'Kiran' <kumar.m.kiran@gmail.com>; +Cc: pgsql-sql
Hi Depesz,
I was curious about your solution for Best Fit since I had mine working in a
function with a loop:
...
FOR v_len IN REVERSE v_max..v_min LOOP
v_prefix := substring(v_destino, 1, v_len);
SELECT * INTO v_result
FROM numeracion
WHERE prefijo = v_prefix;
IF FOUND THEN
RETURN :v_result;
END IF;
END LOOP;
...
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?
--- DDL ---
rd=# show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)
rd=# show client_encoding;
client_encoding
-----------------
SQL_ASCII
(1 row)
rd=# show server_encoding;
server_encoding
-----------------
SQL_ASCII
(1 row)
rd=# \d numeracion
Table "public.numeracion"
Column | Type | Modifiers
-------------+-----------------------------+---------------
cod_oper | integer |
servicio | text | not null
modalidad | text | not null
localidad | text | not null
indicativo | text | not null
bloque | text | not null
resolucion | text |
fecha | date | not null
prefijo | text | not null
largo | integer |
fecha_carga | timestamp without time zone | default now()
Indexes:
"pk_numeracion" PRIMARY KEY, btree (prefijo)
"idx_numeracion_prefijo" btree (prefijo varchar_pattern_ops)
Foreign-key constraints:
"fk_numeracion_operadores_cod_oper" FOREIGN KEY (cod_oper) REFERENCES
operadores(cod_oper)
rd=# set enable_seqscan = off;
SET
rd=# explain select prefijo
rd-# FROM numeracion
rd-# WHERE '3514269565' LIKE prefijo || '%'
rd-# ORDER BY LENGTH(prefijo) DESC
rd-# LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------
Limit (cost=100001077.54..100001077.54 rows=1 width=89)
-> Sort (cost=100001077.54..100001077.91 rows=151 width=89)
Sort Key: length(prefijo)
-> Seq Scan on numeracion (cost=100000000.00..100001072.07
rows=151 width=89)
Filter: ('3514269565'::text ~~ (prefijo || '%'::text))
Why I am getting these monstrous costs? Table had been vacuumed full just
before running the explain plan. It has ~31k rows.
Any hindsight will be greatly appreciated.
Regards,
Fernando.
-----Mensaje original-----
De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Best Fit SQL query statement
On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote:
> Could anyone help me in writing Best Fit SQL statement.
> Suppose we have table t1 with coloumn t1 (text) with following rows.
> 98456
> 98457
> 9845
> 9846
> 984
> 985
> 98
> 99
> and if I query on 98456 the result must be 98456,
> However if I query on 98455 the result must be 9845
> and If I query 9849 the result must be 984
select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1)
desc limit 1;
should be ok.
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)
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
2007-08-10 07:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
2007-08-10 19:40 ` Re: Best Fit SQL query statement Fernando Hevia <fhevia@ip-tel.com.ar>
@ 2007-08-10 22:59 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-11 01:13 ` Re: Best Fit SQL query statement Rodrigo De León <rdeleonp@gmail.com>
2007-08-14 13:59 ` Re: Best Fit SQL query statement Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 2 replies; 8+ messages in thread
From: hubert depesz lubaczewski @ 2007-08-10 22:59 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: Kiran <kumar.m.kiran@gmail.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
2007-08-10 07:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
2007-08-10 19:40 ` Re: Best Fit SQL query statement Fernando Hevia <fhevia@ip-tel.com.ar>
2007-08-10 22:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
@ 2007-08-11 01:13 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-11 09:06 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 8+ messages in thread
From: Rodrigo De León @ 2007-08-11 01:13 UTC (permalink / raw)
To: depesz@depesz.com, "Fernando Hevia" <fhevia@ip-tel.com.ar>; Kiran <kumar.m.kiran@gmail.com>; pgsql-sql
On 8/10/07, hubert depesz lubaczewski <depesz@depesz.com> wrote:
> unfortunatelly this query will be hard to optimize.
Uh, how about
SELECT MAX(t1)
FROM t1
WHERE '9849' LIKE t1 || '%';
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
2007-08-10 07:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
2007-08-10 19:40 ` Re: Best Fit SQL query statement Fernando Hevia <fhevia@ip-tel.com.ar>
2007-08-10 22:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
2007-08-11 01:13 ` Re: Best Fit SQL query statement Rodrigo De León <rdeleonp@gmail.com>
@ 2007-08-11 09:06 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 8+ messages in thread
From: hubert depesz lubaczewski @ 2007-08-11 09:06 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: Fernando Hevia <fhevia@ip-tel.com.ar>; Kiran <kumar.m.kiran@gmail.com>; pgsql-sql
On Fri, Aug 10, 2007 at 08:13:46PM -0500, Rodrigo De León wrote:
> On 8/10/07, hubert depesz lubaczewski <depesz@depesz.com> wrote:
> > unfortunatelly this query will be hard to optimize.
>
> Uh, how about
>
> SELECT MAX(t1)
> FROM t1
> WHERE '9849' LIKE t1 || '%';
it will not help much as the main burden is the where clause, which is
not indexable.
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Best Fit SQL query statement
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
2007-08-10 07:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
2007-08-10 19:40 ` Re: Best Fit SQL query statement Fernando Hevia <fhevia@ip-tel.com.ar>
2007-08-10 22:59 ` Re: Best Fit SQL query statement hubert depesz lubaczewski <depesz@depesz.com>
@ 2007-08-14 13:59 ` Fernando Hevia <fhevia@ip-tel.com.ar>
1 sibling, 0 replies; 8+ messages in thread
From: Fernando Hevia @ 2007-08-14 13:59 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: 'Kiran' <kumar.m.kiran@gmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-08-14 13:59 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-06 08:57 Best Fit SQL query statement Kiran <kumar.m.kiran@gmail.com>
2007-08-06 15:15 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-10 07:59 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-10 19:40 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-08-10 22:59 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-11 01:13 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-11 09:06 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-14 13:59 ` Fernando Hevia <fhevia@ip-tel.com.ar>
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