Re: velocidad en consulta con like

On 5/29/07, Ever Daniel Barreto Rojas <ebarreto(at)nexusit(dot)com(dot)py> wrote:

>
> la consulta que estoy realizando es esta:
> SELECT *
> FROM vw_personas
> WHERE per_nombres LIKE 'EVER D%' AND per_apellidos LIKE 'BARRET%'
>
> sobre una tabla con 4.140.619 registros, el explain analyze de la
> consulta es esta:
>
> "Sort (cost=138124.45..138124.46 rows=1 width=2252) (actual time=3770.737..3770.740 rows=1 loops=1)"
> " Sort Key: persona.per_apellidos, persona.per_nombres"
> " -> Nested Loop (cost=0.00..138124.44 rows=1 width=2252) (actual time=2663.531..3770.693 rows=1 loops=1)"
> " Join Filter: (persona.idecivil = ec.idecivil)"
> " -> Nested Loop (cost=0.00..138123.35 rows=1 width=2239) (actual time=2663.484..3770.631 rows=1 loops=1)"
> " Join Filter: (persona.idtdoc = td.idtdoc)"
> " -> Seq Scan on persona (cost=0.00..138122.29 rows=1 width=2203) (actual time=2663.411..3770.548 rows=1 loops=1)"
> " Filter: (((per_nombres)::text ~~ 'EVER D%'::text) AND ((per_apellidos)::text ~~ 'BARRET%'::text))"

Aunque Alvaro ya te ayudo a resolver esto, es interesante mirar el
EXPLAIN ANALYZE. Por ejemplo, si te fijas el costo se vuelve alto a
partir de esta linea, que hay ahi un Seq Scan on persona, mirando a
que puede hacerlo tan feo existen 2 razones:

1) la cantidad de registros, un recorrido secuencial en esta tabla
sera pesado (de ahi es de donde Alvaro saco la sugerencia del indice
=)

2) fijate en la palabra width dice 2203, este es el tamaño promedio de
las filas que vas a retornar en el select. significa que cada fila
tiene 2kB puesto que postgres maneja (a menos que se cambie al momento
de compilar) paginas de 8kB.
eso significa que tus 4.140.619 registros se leen a razon de 4 por
pagina. en los cuales puede haber tanto tuplas vivas como muertas. No
se cuantas paginas lee postgres en un solo movimiento de e/s (Alvaro?)
pero definitivamente explica la subida de costos

aun cuando ya solucionaste tu problema fijate en la linea
correspondiente del nuevo explain
-> Index Scan using idx_personas on persona (cost=0.00..9.38
rows=1 width=2203) (actual time=0.273..0.348 rows=1 loops=1)"

y comparala con el Seq Scan de una tabla con menos columnas
-> Seq Scan on tipo_documento td (cost=0.00..1.03
rows=3 width=40) (actual time=0.006..0.011 rows=3 loops=1)"

fijate en el costo verdad que aun hay diferencia? el indice
probablemente era necesario por la cantidad de registros pero aun te
afecta el tamaño de cada registro porque postgres luego de leer el
indice debe accesar a la tabla (recuperar una pagina para verificar la
tupla esta viva)

si tu consulta no retornara una sola tupla y tus datos no estan
ordenados en la tabla (de modo que tengas que leer varias paginas para
obtener la informacion) te daras cuenta de lo que hablo.

Moraleja: cuiden esos diseños:

por ejemplo, estos evidentemente pudieron estar en otra tabla.
per_dl_calle character varying(95),
per_dl_barrio character varying(45),
per_dl_edif character varying(45),
per_dl_piso character varying(12),
per_dl_pta character varying(12),
per_dl_ciudad character varying(65),
per_dl_pais character varying(65),
per_dl_ref text,
per_dp_calle character varying(95),
per_dp_barrio character varying(45),
per_dp_edif character varying(45),
per_dp_piso character varying(12),
per_dp_pta character varying(12),
per_dp_ciudad character varying(65),
per_dp_pais character varying(65),
per_dp_ref text,

y estos en otra
per_lab_telef character varying(18),
per_lab_fax character varying(18),
per_lab_cel character varying(18),
per_part_telef character varying(18),
per_part_fax character varying(18),
per_part_cel character varying(18),

solo por mencionar algunos ejemplos =)

--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook