Re: Best Fit SQL query statement

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

http://www.postgresql.org/about/donate

In response to

Responses

Browse pgsql-sql by date

  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