Re: join super lento

Hola Horacio

Muchas gracias por el tiempo y la dedicación al tema, voy respondiendo sus
preguntas:

- Los datos son insertados por un servicio externo (web service) de forma
permanente por lo tanto no tenemos control sobre los datos que vienen
- Por la misma razón del punto anterior no se puede definir una vista
materializada ni una tabla resultado pues los datos están siendo agregados
continuamente, incluso en la consulta se va modificando la fecha de
consulta.(el día anterior, en este momento debería estar en 2016-02-22)
pero estas tablas son una copia exacta con datos hasta el 20 de febrero
para no afectar la de producción.(por eso le sufijo v2)

Retire el group by y el distinct para verificar si es ese el problema:

select 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 as cant
from public.ath_tecnicosv2 t
left join public.ath_cajerosv2 c on
t.identificacionusuario=c.identificacion and t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'

Nested Loop (cost=0.17..40237.34 rows=767806 width=179)
-> Index Scan using idx_ath_cajerosv2_fecha on ath_cajerosv2 c
(cost=0.08..253.76 rows=9076 width=64)
Index Cond: (fecha >= '2016-02-18'::date)
-> Index Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2 t
(cost=0.09..4.38 rows=6 width=123)
Index Cond: ((identificacionusuario = c.identificacion) AND
(fechamto = c.fecha) AND (fechamto >= '2016-02-18'::date))

Ejecute el EXPLAIN ANALYZE y lo termine a los 4 minutos sin resultados

Pero hay algo adicional, cambio el select por count(*) para saber cuando
debería devolver y este si sale 🤔:

select count(*)
from public.ath_tecnicosv2 t
left join public.ath_cajerosv2 c on
t.identificacionusuario=c.identificacion and t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'

Aggregate (cost=7849.00..7849.01 rows=1 width=0) (actual
time=2698.392..2698.392 rows=1 loops=1)
-> Nested Loop (cost=0.17..7465.10 rows=767806 width=0) (actual
time=0.138..2060.088 rows=9736174 loops=1)
-> Index Scan using idx_ath_cajerosv2_fecha on ath_cajerosv2 c
(cost=0.08..253.76 rows=9076 width=12) (actual time=0.060..7.261 rows=9046
loops=1)
Index Cond: (fecha >= '2016-02-18'::date)
-> Index Only Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2
t (cost=0.09..0.77 rows=6 width=12) (actual time=0.006..0.127 rows=1076
loops=9046)
Index Cond: ((identificacionusuario = c.identificacion) AND
(fechamto = c.fecha) AND (fechamto >= '2016-02-18'::date))
Heap Fetches: 0
Total runtime: 2698.447 ms

y cambio el count(*) por * y saca el EXPLAIN ANALYZE pero no saca los datos
después de 4 minutos 😶:

select *
from public.ath_tecnicosv2 t
left join public.ath_cajerosv2 c on
t.identificacionusuario=c.identificacion and t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'

Nested Loop (cost=0.17..40237.34 rows=767806 width=246) (actual
time=0.060..4169.545 rows=9736174 loops=1)
-> Index Scan using idx_ath_cajerosv2_fecha on ath_cajerosv2 c
(cost=0.08..253.76 rows=9076 width=115) (actual time=0.014..5.205
rows=9046 loops=1)
Index Cond: (fecha >= '2016-02-18'::date)
-> Index Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2 t
(cost=0.09..4.38 rows=6 width=131) (actual time=0.006..0.245 rows=1076
loops=9046)
Index Cond: ((identificacionusuario = c.identificacion) AND
(fechamto = c.fecha) AND (fechamto >= '2016-02-18'::date))
Total runtime: 4567.280 ms

Entonces quise saber que tamaño de datos estaba devolviendo y eureka!!:

SELECT pg_size_pretty(SUM(tamanos))
FROM (
SELECT pg_column_size(row(b.*)) as tamanos
FROM (select 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 as cant
from public.ath_tecnicosv2 t
left join public.ath_cajerosv2 c on
t.identificacionusuario=c.identificacion and t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218' ) as b
) AS c;

pg_size_pretty
----------------
* 1974 MB*
(1 row)

Time: 6964,048 ms

Entonces valide y efectivamente la tabla cajeros tiene duplicados reescribí
así la consulta

select 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 as cant
from public.ath_tecnicosv2 t
left join (select distinct c.fecha, c.nombre, c.regionalath, c.regionaltdv,
c.tiposolicitud,c.identificacion from public.ath_cajerosv2 as c where fecha
>= '20160218') c on t.identificacionusuario=c.identificacion and
t.fechamto=c.fecha
where fechamto >= '20160218'

El tamaño de la misma:

SELECT pg_size_pretty(SUM(tamanos))
FROM (
SELECT pg_column_size(row(b.*)) as tamanos
FROM (
select 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 as cant
from public.ath_tecnicosv2 t
left join (select distinct c.fecha, c.nombre, c.regionalath, c.regionaltdv,
c.tiposolicitud,c.identificacion from public.ath_cajerosv2 as c where fecha
>= '20160218') c on t.identificacionusuario=c.identificacion and
t.fechamto=c.fecha
where fecha >= '20160218' and fechamto >= '20160218'
) as b
) AS c;

pg_size_pretty
----------------
*143 MB*
(1 row

Y el EXPLAIN ANALYZE

Nested Loop (cost=285.61..6893.83 rows=2191 width=179) (actual
time=10.962..331.457 rows=712969 loops=1)
-> HashAggregate (cost=285.53..289.16 rows=908 width=64) (actual
time=10.919..11.307 rows=683 loops=1)
-> Index Scan using idx_ath_cajerosv2_fecha on ath_cajerosv2 c
(cost=0.08..258.30 rows=9076 width=64) (actual time=0.054..5.690 rows=9046
loops=1)
Index Cond: ((fecha >= '2016-02-18'::date) AND (fecha >=
'2016-02-18'::date))
-> Index Scan using idx_ath_tecnicosv2_comp on ath_tecnicosv2 t
(cost=0.09..7.25 rows=6 width=123) (actual time=0.008..0.270 rows=1044
loops=683)
Index Cond: ((identificacionusuario = c.identificacion) AND
(fechamto = c.fecha) AND (fechamto >= '2016-02-18'::date))
Total runtime: 360.616 ms

y la consulta sale a los 1:45 minutos!!!😒

Mea culpa!! aveces con el afan de optimizar y ajustar no se analiza la
realidad de los datos y no me había percatado que habían tantos duplicados
en la tabla cajeros!! Les agradezco a todos sus comentarios y tiempo. Hoy
he aprendido un poco mas...

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

> Lo único que se me ocurre a esta altura es crear una vista materializada
> para tu consulta y que corra en la noche...
>
>
> Si quieres usar vistar mira este link.
>
>
> http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-refresh-concurrently-a-materialized-view/
>
>
> On 2/23/2016 11:54 AM, Hellmuth Vargas wrote:
>
>> Hola Horacio
>>
>> El group by es porque originalmente había un distinct porque salen
>> registros duplicados ( son registros de trazas según me dicen) por lo
>> tanto cambie el distinct por group by pues es más óptimo. Igual lo
>> retire en un principio y tampoco obtuvo resultados.
>>
>> El feb. 22, 2016 5:45 PM, "Horacio Miranda" <hmiranda(at)gmail(dot)com
>> <mailto:hmiranda(at)gmail(dot)com>> escribió:
>>
>> Pregunta tonta....
>>
>> Para que quieres hacer un group by ? cuando no hay funciones que
>> necesiten un group by ?
>>
>> Puedes correr la consulta sin el group by for favor.
>>
>> PS: ahora tengo tiempo para mirar esto y estoy viendo como crear
>> datos...
>>
>>

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