From: | "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar> |
---|---|
To: | <depesz(at)depesz(dot)com>, "'Kiran'" <kumar(dot)m(dot)kiran(at)gmail(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Best Fit SQL query statement |
Date: | 2007-08-10 19:40:34 |
Message-ID: | 00e101c7db86$51b3d8b0$8f01010a@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2007-08-10 21:00:09 | Re: Race condition in resetting a sequence |
Previous Message | Oliver Elphick | 2007-08-10 16:12:01 | Re: [NOVICE] Install two different versions of postgres which should run in parallel |