Re: Query lenta

Hola,

con respecto a a_v (cabeceras) tienes razón se insertan (no se modifican) y llevan orden de inserción con av_d (o casi). Lo de añadir el campo fecha en detalles, también de acuerdo no es buena idea.

Si hago:

select sum(adv_q*adv_up) from a_dv where av_id::text||av_ov::text in (select av_id::text||av_ov::text from a_v where av_d >= '01/12/2013')

tarda unos 13 segundos aunque sigue haciendo un recorrido secuencial sobre a_dv (es bastante más rápida) ??
el explain es:
'Aggregate  (cost=1159621.62..1159621.63 rows=1 width=10) (actual time=25927.069..25927.070 rows=1 loops=1)'
'  ->  Hash Semi Join  (cost=7127.84..1143669.52 rows=3190420 width=10) (actual time=14337.822..25014.018 rows=673133 loops=1)'
'        Hash Cond: (((a_dv.av_id)::text || (a_dv.av_ov)::text) = ((a_v.av_id)::text || (a_v.av_ov)::text))'
'        ->  Seq Scan on a_dv  (cost=0.00..135397.40 rows=6380840 width=16) (actual time=0.096..6575.569 rows=6380667 loops=1)'
'        ->  Hash  (cost=5248.01..5248.01 rows=114546 width=6) (actual time=319.451..319.451 rows=116009 loops=1)'
'              Buckets: 4096  Batches: 8  Memory Usage: 549kB'
'              ->  Index Scan using avavd on a_v  (cost=0.42..5248.01 rows=114546 width=6) (actual time=0.030..145.135 rows=116009 loops=1)'
'                    Index Cond: (av_d >= '2013-12-01'::date)'
'Total runtime: 25927.269 ms'

No se si se podría mejorar la query ya que enlaza por los dos campos av_id y av_ov.

Resultado de las querys:
1)

'av_ov',0,2,18
'av_id',0,4,295979
'adv_ar',0,4,742
'adv_q',0,4,16
'adv_up',0,6,237
'adv_vat',0,4,6
'adv_com',0,4,4
'adv_or',0,2,28
'adv_pa',0,6,424
'adv_cd',0.9742,2,12
'adv_verde',0.00843333,6,394
'adv_tte',0.00843333,4,11

2) Las columnas relpages y reltuples no existen en pg_stats ???

3)
28101,'public','a_dv',58,133995007,112602,620385,6380667,0,0,0,6380840,0,'2014-02-11 12:11:50.527+01','','2014-02-11 12:41:47.338+01','',2,0,4,0

Saludos

________________________________
De: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
Para: FRANCISCO JOSE PALAO VILLANUEVA <fjpv_2000(at)yahoo(dot)es>
CC: "pgsql-es-ayuda(at)postgresql(dot)org" <pgsql-es-ayuda(at)postgresql(dot)org>
Enviado: Miércoles 12 de febrero de 2014 20:10
Asunto: Re: [pgsql-es-ayuda] Query lenta

2014-02-12 7:16 GMT-05:00 FRANCISCO JOSE PALAO VILLANUEVA <fjpv_2000(at)yahoo(dot)es>:
>
> el explain analyze con >= '01/12/2013'
>
> 'Aggregate  (cost=545557.78..545557.79 rows=1 width=10) (actual
> time=152389.058..152389.059 rows=1 loops=1)'
> '  ->  Hash Join  (cost=262267.42..544984.31 rows=114695 width=10) (actual
> time=144506.241..151553.263 rows=673133 loops=1)'
> '        Hash Cond: ((t1.av_id = t2.av_id) AND (t1.av_ov = t2.av_ov))'
> '        ->  Index Scan using avavd on a_v t1  (cost=0.42..5248.01
> rows=114546 width=6) (actual time=58.424..5261.424 rows=116009 loops=1)'
> '              Index Cond: (av_d >= '2013-12-01'::date)'
> '        ->  Hash  (cost=135397.40..135397.40 rows=6380840 width=16) (actual
> time=144124.583..144124.583 rows=6380667 loops=1)'
> '              Buckets: 4096  Batches: 512  Memory Usage: 662kB'
> '              ->  Seq Scan on a_dv t2  (cost=0.00..135397.40 rows=6380840
> width=16) (actual time=19.458..125134.296 rows=6380667 loops=1)'
> 'Total runtime: 152389.832 ms'
>
> el explain analyze con = '02/12/2013'
>

Saludos,

Bueno, es obvio que una búsqueda que filtre con >= va a demorar mas
que una que filtre con =

En tu caso particular, parece que en t2 hay, aproximadamente, 6
registros por cada registro en t1. Y el filtro que usas en t1
selecciono 116mil registros luego busca los correspondientes en t2 (6
registros por cada uno de estos 116mil, dan los 673mil registros que
devuelve el JOIN -el 10% de lo que hay en la tabla t2-).

Así que ahora postgres sabe que debe leer al menos el 10% de lo que
hay en t2 pero no sabe en que orden se leerán esos registros, es decir
aunque en t1 la tabla tenga físicamente los registros ordenados por
av_d (o casi ordenados, lo que ocurriría si es una tabla en la que
casi siempre insertas y rara vez actualizas) postgres no entiende que
eso implica una secuencialidad también en av_id por lo que no entiende
que puede usar el índice en t2 (porque supone que los registros pueden
estar desordenados y que terminará leyendo casi toda la tabla de todos
modos).

Supongo que para obtener el resultado que deseas puedes tratar de
reescribir la consulta con un subselect para que lea primero la tabla
t1 (no se si eso te será de ayuda) la otra alternativa es duplicar el
campo av_d en t2 y crear un índice ahí (no me gusta esa idea pero eso
te permitirá usar el índice... creo) y modificar la consulta para que
tambié

Quiero ver si no hay mas opciones, puedes mostrar por favor el
resultado de estas consultas?

select attname, null_frac, avg_width, n_distinct from pg_stats where
tablename = 'a_dv';
select relpages, reltuples::numeric from pg_stats where tablename = 'a_dv';
select * from pg_stat_user_tables where relname = 'a_dv';

--
Jaime Casanova        www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566        Cell: +593 987171157

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda