Re: join super lento

On 2/24/2016 4:30 AM, Alvaro Herrera wrote:
> Hellmuth Vargas escribió:
>
>> Ejecute el EXPLAIN ANALYZE y lo termine a los 4 minutos sin resultados
>
> Cuando tengas tiempo (por ej. justo antes de irte de la oficina), deja
> la consulta corriendo varias horas para tener un EXPLAIN ANALYZE. No
> sirve de nada que lo estés cortando a los 4 minutos, porque la
> información que obtengas puede ser útil para poder encontrar la solución
> al problema.

Hellmuth, el problema como te estoy diciendo son los datos...

Mira este link https://wiki.postgresql.org/wiki/SQL_MERGE

1.0 ) puedes hacer un proceso de limpieza...
2.0 ) puedes hacer un merge ( Oracle insert/update ) en postgresql se
llama UPSERT, Alvaro podría responder mejor a esta función que creo
ayudaría a Hellmuth a tener un sistema más limpio (los datos que se
insertan usando el web service o un acceso externo).

Mantengo un sistema de GPS que me insertan los mismos datos en la noche
( la única forma de no tener duplicados es con un merge ) y teniendo
datos únicos se insertan 2.5M de registros diarios.

Ahora lo que puedes hacer ( como tus v2 que son una copia de producción
) es hacer el insert sacando los datos de producción por día y con un
distinct. Algo como "insert into V2 (select distinct ...)"

PS2: Oracle me crea una vista en el plan de ejecución ( con el uso de
distinct ) el efecto es increíble de rápido. ( el SGA de Oracle es de
1.8G ) y la ram de la maquina virtual es de 8G 2CPU

http://dba.stackexchange.com/questions/73448/what-is-this-view-being-used-in-my-query

** De hecho creo que tu consulta reestructurada es lo que Oracle esta
haciendo ( Oracle reordena las consultas cuando hay un distinct ), solo
te falto agregar el distinct en la primera consulta y en postgresql es
más rápido que Oracle :D

Adjunto el plan de ejecución de tu consulta :D con mis datos.

> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=1864.15..1964.15 rows=10000 width=894) (actual time=120.230..120.254 rows=5 loops=1)
> Group Key: t.descripcionmovimiento, t.fechamto, t.horamto, t.fechaact, t.horaact, t.usuario, t.identificacionusuario, t.tipomovimientosusuario, c.fecha, c.nombre, c.regionalath, c.regionaltdv, c.tiposolicitud, 1
> -> Merge Left Join (cost=532.61..1514.15 rows=10000 width=894) (actual time=44.693..104.844 rows=10000 loops=1)
> Merge Cond: ((t.fechamto = c.fecha) AND (t.identificacionusuario = c.identificacion))
> -> Index Only Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2 t (cost=0.29..921.82 rows=10000 width=522) (actual time=0.036..15.375 rows=10000 loops=1)
> Index Cond: (fechamto >= '2016-02-18'::date)
> Heap Fetches: 10000
> -> Sort (cost=532.33..534.83 rows=1000 width=380) (actual time=44.643..55.494 rows=8001 loops=1)
> Sort Key: c.fecha, c.identificacion
> Sort Method: quicksort Memory: 25kB
> -> HashAggregate (cost=462.50..472.50 rows=1000 width=380) (actual time=44.607..44.615 rows=5 loops=1)
> Group Key: c.fecha, c.nombre, c.regionalath, c.regionaltdv, c.tiposolicitud, c.identificacion
> -> Seq Scan on ath_cajerosv2 c (cost=0.00..312.50 rows=10000 width=380) (actual time=0.022..22.432 rows=10000 loops=1)
> Filter: (fecha >= '2016-02-18'::date)
> Rows Removed by Filter: 5000
> Planning time: 0.494 ms
> Execution time: 120.538 ms
> (17 rows)

la maquina virtual postgresql es un gentoo con 22G ram y 4 CPU ( ambos
con discos SSD ), tendre que leer por que oracle hace eso cuando hay un
distinct y postgresql no ... eso me llamo la atención ( La verdad usar
distinct lo evito, es indicativo de que algo esta mal en los datos o el
modelo). Adjunto ambos planes de ejecución. Los datos generados son los
mismos en ambos, para asegurarme de tener las mismas condiciones conecte
Oracle con postgresql usando ODBC para mover los datos.

ORACLE:
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 910052663
>
> ----------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 4 | 1268 | 20 (10)| 00:00:01 |
> | 1 | HASH UNIQUE | | 4 | 1268 | 20 (10)| 00:00:01 |
> | 2 | NESTED LOOPS | | 7488 | 2318K| 19 (6)| 00:00:01 |
> | 3 | VIEW | VW_DTP_1B35BA0F | 45 | 13410 | 19 (6)| 00:00:01 |
> | 4 | HASH UNIQUE | | 45 | 1305 | 19 (6)| 00:00:01 |
> |* 5 | INDEX FAST FULL SCAN| IDX_ATH_TECNICOSV2_COMP | 12000 | 339K| 18 (0)| 00:00:01 |
> |* 6 | INDEX RANGE SCAN | IDX_ATH_CAJEROSV2_COMP | 166 | 3154 | 0 (0)| 00:00:01 |
> ----------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 5 - filter("fechamto">TO_DATE(' 2016-02-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
> 6 - access("ITEM_1"="C"."fecha" AND "ITEM_2"="C"."identificacion")
> filter("fecha">TO_DATE(' 2016-02-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
>
> Note
> -----
> - dynamic statistics used: dynamic sampling (level=2)
> - 3 Sql Plan Directives used for this statement
>
> 25 rows selected.
>
>
> Query Plan
> --------------------------------------------------------------------------------
> SELECT STATEMENT [ALL_ROWS] Cost = 20
> HASH UNIQUE
> NESTED LOOPS
> VIEW VW_DTP_1B35BA0F
> HASH UNIQUE
> INDEX FAST FULL SCAN IDX_ATH_TECNICOSV2_COMP [ANALYZED]
> INDEX RANGE SCAN IDX_ATH_CAJEROSV2_COMP [ANALYZED]

Postgresql:

> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=0.57..15536.49 rows=790124 width=894) (actual time=0.174..50305.352 rows=16000000 loops=1)
> Merge Cond: ((c.fecha = t.fechamto) AND (c.identificacion = t.identificacionusuario))
> -> Index Only Scan using idx_ath_cajerosv2_comp on ath_cajerosv2 c (cost=0.29..842.56 rows=8000 width=380) (actual time=0.075..24.652 rows=8000 loops=1)
> Index Cond: (fecha > to_date('20160218'::text, 'YYYYMMDD'::text))
> Heap Fetches: 8000
> -> Materialize (cost=0.29..846.76 rows=8000 width=522) (actual time=0.078..16326.950 rows=15998001 loops=1)
> -> Index Only Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2 t (cost=0.29..826.76 rows=8000 width=522) (actual time=0.060..14.203 rows=8000 loops=1)
> Index Cond: (fechamto > to_date('20160218'::text, 'YYYYMMDD'::text))
> Heap Fetches: 8000
> Planning time: 1.924 ms
> Execution time: 66083.943 ms
> (11 rows)

Si postgresql podiera hacer lo mismo que Oracle esta haciendo como
feature ( reestructurar el SQL cuando hay un distinct ) sería genial :D,
pero por otra parte escribir código SQL eficiente y saber lo que estas
haciendo no tiene precio... ignoro cual aproach aquí es el mejor....

-
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