Re: join super lento

Hola Horario

Si, tenemos una tarea pendiente de ajustar la carga de la informacion
externa, actualmente el servidor se encuentra en la versión 9.4 por lo
tanto UPSERT no esta disponible, vamos a trabajar en ese tema. Muchas
Gracias por sus comentarios y tiempo.

El 23 de febrero de 2016, 17:39, Horacio Miranda<hmiranda(at)gmail(dot)com>
escribió:

>
>
> 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....
>

--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate