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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: [email protected]
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
