RE: Mejorar performance de un query.

Utilizo la 8.1.4

Acabo de realizar la prueba recreando la base de datos en un Pg ver. 8.2 y los resultados hablan por si solos (y eso que lo instalé en una máquina Windows sin ninguna configuración especial):

QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
Unique (cost=37.72..37.75 rows=1 width=224) (actual time=35.585..35.966 rows=6
8 loops=1)
-> Sort (cost=37.72..37.73 rows=1 width=224) (actual time=35.578..35.688 ro
ws=68 loops=1)
Sort Key: docto.barra, docto.copia, tb_usuario.nombre, to_char((docto.f
echa)::timestamp with time zone, 'dd-mm-yyyy'::text), docto.numero, origen.descr
ipcion, docto.origen2, docto.referencia, docto.notas
-> Nested Loop Left Join (cost=0.01..37.71 rows=1 width=224) (actual
time=0.261..35.211 rows=68 loops=1)
-> Nested Loop (cost=0.01..33.43 rows=1 width=211) (actual time
=0.218..33.859 rows=68 loops=1)
-> Nested Loop IN Join (cost=0.01..25.15 rows=1 width=202
) (actual time=0.194..32.673 rows=68 loops=1)
Join Filter: ((public.historia.copia = docto.copia) A
ND (public.historia.barra = docto.barra))
-> Nested Loop (cost=0.01..16.72 rows=1 width=226)
(actual time=0.137..2.533 rows=71 loops=1)
Join Filter: (docto.copia = public.historia.cop
ia)
-> Index Scan using idx_hist_fecha on historia
(cost=0.01..8.41 rows=1 width=34) (actual time=0.095..0.893 rows=71 loops=1)
Index Cond: ((fecha >= to_date('04-04-200
7'::text, 'dd-mm-yyyy'::text)) AND (fecha <= to_date('04-04-2007'::text, 'dd-mm-
yyyy'::text)))
Filter: ((movimiento = 'A'::bpchar) AND (
actual = 1100::numeric))
-> Index Scan using idx_docto_barra on docto
(cost=0.00..8.29 rows=1 width=192) (actual time=0.009..0.011 rows=1 loops=71)
Index Cond: (docto.barra = public.histori
a.barra)
-> Index Scan using idx_hist_fecha on historia (cos
t=0.01..8.41 rows=1 width=24) (actual time=0.039..0.345 rows=36 loops=71)
Index Cond: ((fecha >= to_date('04-04-2007'::te
xt, 'dd-mm-yyyy'::text)) AND (fecha <= to_date('04-04-2007'::text, 'dd-mm-yyyy':
:text)))
Filter: ((movimiento = 'C'::bpchar) AND (actual
= 1100::numeric))
-> Index Scan using tb_usuario_pk on tb_usuario (cost=0.0
0..8.27 rows=1 width=29) (actual time=0.005..0.007 rows=1 loops=68)
Index Cond: (public.historia.comodin = tb_usuario.cod
_usuario)
-> Index Scan using origen_pk on origen (cost=0.00..4.27 rows=1
width=33) (actual time=0.004..0.006 rows=1 loops=68)
Index Cond: (docto.origen = origen.cod_origen)
Total runtime: 36.362 ms
(22 filas)

Parece que voy a tener que actualizar mi versión, cosa que no será fácil debido a la cantidad de bases de datos e información ya registrada en ellas.

Saludos y gracias por su ayuda,
Esteban.

-----Mensaje original-----
De: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
Enviado el: Martes, 08 de Mayo de 2007 9:07
Para: Esteban Osorio
CC: pgsql-es-ayuda(at)postgresql(dot)org
Asunto: Re: [pgsql-es-ayuda] Mejorar performance de un query.

Esteban Osorio escribió:
> Hola a todos,

Que version estas usando?

Creo que aca el principal problema es el outer join, que esta bloqueando
la optimizacion del join entre historia y documento. En una version
reciente (creo que 8.2) se introdujo la posibilidad de reordenar outer
joins, lo cual es muy beneficioso en casos como este.

Otra cosa que me llama la atencion es que la estimacion de registros de
historia se equivoca en casi dos ordenes de magnitud; puede ser que eso
este haciendo que escoja mal el resto del plan.

Me refiero a este nodo de aca:

-> Index Scan using idx_hist_fecha on historia (cost=0.00..5.95 rows=1 width=34) (actual time=0.079..1.998 rows=71 loops=1)
Index Cond: ((fecha >= '2007-04-04'::date) AND (fecha <= '2007-04-04'::date))
Filter: ((movimiento = 'A'::bpchar) AND (actual = 1100::numeric))

(espera 1 tupla, recibe 71) que se repite en este otro nodo:

-> Index Scan using idx_hist_fecha on historia (cost=0.00..5.95 rows=1 width=24) (actual time=0.046..0.548 rows=36 loops=71)
Index Cond: ((fecha >= '2007-04-04'::date) AND (fecha <= '2007-04-04'::date))
Filter: ((movimiento = 'C'::bpchar) AND (actual = 1100::numeric))

(espera 1 tupla, recibe 36)

Quizas lo que podrias hacer es capturar mas datos estadisticos para
historia, a ver si mejora la estimacion. Si mal no recuerdo la sintaxis
es algo asi:

alter table historia alter column fecha set statistics 100;
analyze;

Prueba tambien cambiando la condicion
fecha BETWEEN 'una fecha' AND 'la misma fecha'

en
fecha = 'la fecha'

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support