Tu consulta es horrenda, creo que mejor es que nos des las tablas y nos digas que quieres consultar para hacerla de una mejor manera; porque es lenta, la principal razon es porque estas metiendo un select dentro de los campos del select principal, por cada registro consultado estas lanzando una consulta, es decir si la query arrojara 1000 registros, por cada uno de ellos estarias lanzando las querys del case when, es decir 1000 querys y si el then tiene otra query va de nuevo, por mas indices que pongas la query siempre sera lenta.
2018-03-08 10:28 GMT-05:00 jvenegasperu . <jvenegasp...@gmail.com>: > Hola a todos > > Con la sugerencia de Stephen quite el CAST y ahora tengo la consulta un > indice y el explain como lo muestro lineas abajo la consulta a bajado de 2 > minutos a 50 segundos pora favor alguna otra sugerencia? > > INDICE > create index trans_emp_fecha_hora on trans (empid,fecha,hora); > > CONSULTA > SELECT m.empid AS dni, > m.empname AS nombre_empleado, > '201802' AS periodo, > m.fecha, > dia_semana(m.fecha) AS dia_semana, > ( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora - > d.hora_ingreso) > '-00:59:00'::interval AND (m1.hora - d.hora_ingreso) <= > d.hora_salida::interval AND m1.fecha >= d.fecha_ini and m1.fecha <= > d.fecha_fin > and d.turno_id = 1 or d.turno_id = 3 > and m1.hora < d.hora_salida > GROUP BY (m1.fecha)limit 1) AS hor_ing_ma, > CASE > WHEN ((( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d.hora_ingreso) > '-00:59:00'::interval AND (m1.hora - d.hora_salida) <= > '00'::interval AND m1.fecha >= d.fecha_ini and m1.fecha <= d.fecha_fin > and d.turno_id = 1 or d.turno_id = 3 > GROUP BY (m1.fecha) limit 1)) - d.hora_ingreso) >= > '00:01:00'::time without time zone::interval THEN > > (( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d.hora_ingreso) > '-00:59:00'::interval AND (m1.hora - d.hora_salida) <= > '00'::interval AND m1.fecha >= d.fecha_ini and m1.fecha <= d.fecha_fin > and d.turno_id = 1 or d.turno_id = 3 > GROUP BY (m1.fecha) limit 1)) - d.hora_ingreso > ELSE NULL::interval > END AS tardanz_ma, > ( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora - > d.hora_salida) > '-00:59:00'::interval AND (m1.hora - d.hora_salida) <= > d.sal_max_ma::interval AND m1.fecha >= d.fecha_ini and m1.fecha <= > d.fecha_fin > and d.turno_id = 1 or d.turno_id = 3 > GROUP BY (m1.fecha) limit 1) AS hor_sal_ma, > > CASE > WHEN ((( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d.hora_salida) > '-00:59:00'::interval AND (m1.hora - d.hora_salida) <= > d.sal_max_ma::interval AND m1.fecha >= d.fecha_ini and m1.fecha <= > d.fecha_fin > and d.turno_id = 1 or d.turno_id = 3 > GROUP BY (m1.fecha) limit 1 )) - d.hora_salida) >= > '00:01:00'::time without time zone::interval THEN (( SELECT min(m1.hora) AS > min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d.hora_salida) > '-00:59:00'::interval AND (m1.hora - d.hora_salida) <= > d.sal_max_ma::interval AND m1.fecha >= d.fecha_ini and m1.fecha <= > d.fecha_fin > and d.turno_id = 1 or d.turno_id = 3 > GROUP BY (m1.fecha) limit 1 )) - d.hora_salida > ELSE NULL::interval > END AS tiem_compensar_ma, > ( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora - > d1.hora_ingreso) > '-00:59:00'::interval AND (m1.hora - d1.hora_salida) <= > '00'::interval AND m1.fecha >= d1.fecha_ini and m1.fecha <= d1.fecha_fin > and d1.turno_id = 2 or d1.turno_id = 4 > GROUP BY (m1.fecha) limit 1 ) AS hor_ing_ta, > CASE > WHEN ((( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d1.hora_ingreso) > '-00:59:00'::interval AND (m1.hora - d1.hora_salida) > <= '00'::interval AND m1.fecha >= d1.fecha_ini and m1.fecha <= d1.fecha_fin > and d1.turno_id = 2 or d1.turno_id = 4 > GROUP BY (m1.fecha) limit 1)) - d1.hora_ingreso) >= > '00:01:00'::time without time zone::interval THEN (( SELECT min(m1.hora) AS > min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d1.hora_ingreso) > '-00:59:00'::interval AND (m1.hora - d1.hora_salida) > <= '00'::interval AND m1.fecha >= d1.fecha_ini and m1.fecha <= d1.fecha_fin > and d1.turno_id = 2 or d1.turno_id = 4 > GROUP BY (m1.fecha) limit 1 )) - d1.hora_ingreso > ELSE NULL::interval > END AS tardanz_ta, > ( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora - > d1.hora_salida) > '-00:59:00'::interval AND (m1.hora - d1.hora_salida) <= > d1.sal_max_ta::interval AND m1.fecha >= d1.fecha_ini and m1.fecha <= > d1.fecha_fin > and d1.turno_id = 2 or d1.turno_id = 4 > GROUP BY (m1.fecha) limit 1 ) AS hor_sal_ta, > CASE > WHEN ((( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d1.hora_salida) > '-00:59:00'::interval AND (m1.hora - d1.hora_salida) <= > d1.sal_max_ta::interval AND m1.fecha >= d1.fecha_ini and m1.fecha <= > d1.fecha_fin > and d1.turno_id = 2 or d1.turno_id = 4 > GROUP BY (m1.fecha))) - d1.hora_salida) >= '00:01:00'::time > without time zone::interval THEN (( SELECT min(m1.hora) AS min > FROM trans m1 > WHERE m1.empid = m.empid AND m.fecha = m1.fecha AND (m1.hora > - d1.hora_salida) > '-00:59:00'::interval AND (m1.hora - d1.hora_salida) <= > d1.sal_max_ta::interval AND m1.fecha >= d1.fecha_ini and m1.fecha <= > d1.fecha_fin > and d1.turno_id = 2 or d1.turno_id = 4 > GROUP BY (m1.fecha) limit 1 )) - d1.hora_salida > ELSE NULL::interval > END AS tiem_compensar_ta > FROM trans m > LEFT JOIN rrhh_persona_horario d on > m.empid = d.documento > and d.activo = 1 > and d.turno_id = 1 or d.turno_id = 3 > LEFT JOIN rrhh_persona_horario d1 on > m.empid = d1.documento > and d1.activo = 1 > and d1.turno_id = 2 or d1.turno_id = 4 > WHERE > ((m.fecha >= d.fecha_ini and m.fecha <= d.fecha_fin ) > or (m.fecha >= d1.fecha_ini and m.fecha <= d1.fecha_fin )) > and (m.fecha >= '2018-01-26' and m.fecha <= '2018-02-25') > GROUP BY m.empid, m.empname, m.fecha, > d.fecha_ini,d.fecha_fin,d.hora_ingreso,d.hora_salida,d. > turno_id,d.sal_max_ma,d.sal_max_ta, > d1.fecha_ini,d1.fecha_fin,d1.hora_ingreso,d1.hora_salida, > d1.turno_id,d1.sal_max_ma,d1.sal_max_ta > ORDER BY m.fecha; > > EXPLAIN > > https://explain.depesz.com/s/Uy9K > > "Group (cost=18421.32..11094543.79 rows=1030 width=263) (actual > time=224.823..49373.643 rows=2164 loops=1)" > " Group Key: m.fecha, m.empid, m.empname, d.fecha_ini, d.fecha_fin, > d.hora_ingreso, d.hora_salida, d.turno_id, d.sal_max_ma, d.sal_max_ta, > d1.fecha_ini, d1.fecha_fin, d1.hora_ingreso, d1.hora_salida, d1.turno_id, > d1.sal_max_ma, d1.sal_max_ta" > " -> Sort (cost=18421.32..18423.89 rows=1030 width=103) (actual > time=194.902..196.501 rows=6464 loops=1)" > " Sort Key: m.fecha, m.empid, m.empname, d.fecha_ini, d.fecha_fin, > d.hora_ingreso, d.hora_salida, d.turno_id, d.sal_max_ma, d.sal_max_ta, > d1.fecha_ini, d1.fecha_fin, d1.hora_ingreso, d1.hora_salida, d1.turno_id, > d1.sal_max_ma, d1.sal_max_ta" > " Sort Method: quicksort Memory: 987kB" > " -> Nested Loop Left Join (cost=0.00..18369.78 rows=1030 width=103) > (actual time=3.428..163.623 rows=6464 loops=1)" > " Join Filter: (((m.empid = (d1.documento)::text) AND (d1.activo > = 1) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Join Filter: 400551" > " Filter: (((m.fecha >= d.fecha_ini) AND (m.fecha <= > d.fecha_fin)) OR ((m.fecha >= d1.fecha_ini) AND (m.fecha <= d1.fecha_fin)))" > " Rows Removed by Filter: 5090" > " -> Nested Loop Left Join (cost=0.00..8477.70 rows=4907 > width=65) (actual time=2.917..76.545 rows=6611 loops=1)" > " Join Filter: (((m.empid = (d.documento)::text) AND > (d.activo = 1) AND (d.turno_id = 1)) OR (d.turno_id = 3))" > " Rows Removed by Join Filter: 338747" > " -> Seq Scan on trans m (cost=0.00..744.69 rows=4907 > width=27) (actual time=2.783..4.569 rows=4906 loops=1)" > " Filter: ((fecha >= '2018-01-26'::date) AND (fecha > <= '2018-02-25'::date))" > " Rows Removed by Filter: 9207" > " -> Materialize (cost=0.00..4.66 rows=70 width=49) > (actual time=0.000..0.004 rows=70 loops=4906)" > " -> Seq Scan on rrhh_persona_horario d > (cost=0.00..4.31 rows=70 width=49) (actual time=0.020..0.067 rows=70 loops=1)" > " Filter: (((activo = 1) AND (turno_id = 1)) > OR (turno_id = 3))" > " Rows Removed by Filter: 62" > " -> Materialize (cost=0.00..4.62 rows=62 width=49) (actual > time=0.000..0.003 rows=62 loops=6611)" > " -> Seq Scan on rrhh_persona_horario d1 > (cost=0.00..4.31 rows=62 width=49) (actual time=0.009..0.059 rows=62 loops=1)" > " Filter: (((activo = 1) AND (turno_id = 2)) OR > (turno_id = 4))" > " Rows Removed by Filter: 70" > " SubPlan 1" > " -> Limit (cost=923.92..923.94 rows=1 width=12) (actual > time=2.406..2.406 rows=1 loops=2164)" > " -> GroupAggregate (cost=923.92..924.31 rows=21 width=12) (actual > time=2.405..2.405 rows=1 loops=2164)" > " Group Key: m1.fecha" > " -> Sort (cost=923.92..923.98 rows=24 width=12) (actual > time=2.401..2.402 rows=2 loops=2164)" > " Sort Key: m1.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1 (cost=0.29..923.37 rows=24 width=12) (actual time=1.319..2.398 > rows=2 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d.hora_ingreso) > '-00:59:00'::interval) AND ((hora - > d.hora_ingreso) <= (d.hora_salida)::interval) AND (fecha >= d.fecha_ini) AND > (fecha <= d.fecha_fin) AND (d.turno_id = 1)) OR ((d.turno_id = 3) AND (hora < > d.hora_salida)))" > " Rows Removed by Filter: 14111" > " Heap Fetches: 0" > " SubPlan 2" > " -> Limit (cost=854.99..855.01 rows=1 width=12) (actual > time=2.360..2.360 rows=1 loops=2164)" > " -> GroupAggregate (cost=854.99..856.00 rows=48 width=12) (actual > time=2.359..2.359 rows=1 loops=2164)" > " Group Key: m1_1.fecha" > " -> Sort (cost=854.99..855.17 rows=71 width=12) (actual > time=2.356..2.357 rows=1 loops=2164)" > " Sort Key: m1_1.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_1 (cost=0.29..852.80 rows=71 width=12) (actual time=1.318..2.353 > rows=1 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d.hora_ingreso) > '-00:59:00'::interval) AND ((hora - > d.hora_salida) <= '00:00:00'::interval) AND (fecha >= d.fecha_ini) AND (fecha > <= d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 3" > " -> Limit (cost=854.99..855.01 rows=1 width=12) (actual > time=2.361..2.361 rows=1 loops=684)" > " -> GroupAggregate (cost=854.99..856.00 rows=48 width=12) (actual > time=2.360..2.360 rows=1 loops=684)" > " Group Key: m1_2.fecha" > " -> Sort (cost=854.99..855.17 rows=71 width=12) (actual > time=2.357..2.357 rows=1 loops=684)" > " Sort Key: m1_2.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_2 (cost=0.29..852.80 rows=71 width=12) (actual time=0.937..2.354 > rows=1 loops=684)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d.hora_ingreso) > '-00:59:00'::interval) AND ((hora - > d.hora_salida) <= '00:00:00'::interval) AND (fecha >= d.fecha_ini) AND (fecha > <= d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 4" > " -> Limit (cost=925.55..925.57 rows=1 width=12) (actual > time=2.363..2.363 rows=1 loops=2164)" > " -> GroupAggregate (cost=925.55..926.57 rows=48 width=12) (actual > time=2.362..2.362 rows=1 loops=2164)" > " Group Key: m1_3.fecha" > " -> Sort (cost=925.55..925.73 rows=71 width=12) (actual > time=2.359..2.360 rows=1 loops=2164)" > " Sort Key: m1_3.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_3 (cost=0.29..923.37 rows=71 width=12) (actual time=1.382..2.356 > rows=1 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d.hora_salida) > '-00:59:00'::interval) AND ((hora - > d.hora_salida) <= (d.sal_max_ma)::interval) AND (fecha >= d.fecha_ini) AND > (fecha <= d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 5" > " -> Limit (cost=925.55..925.57 rows=1 width=12) (actual > time=2.365..2.365 rows=1 loops=2164)" > " -> GroupAggregate (cost=925.55..926.57 rows=48 width=12) (actual > time=2.364..2.364 rows=1 loops=2164)" > " Group Key: m1_4.fecha" > " -> Sort (cost=925.55..925.73 rows=71 width=12) (actual > time=2.361..2.361 rows=1 loops=2164)" > " Sort Key: m1_4.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_4 (cost=0.29..923.37 rows=71 width=12) (actual time=1.384..2.358 > rows=1 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d.hora_salida) > '-00:59:00'::interval) AND ((hora - > d.hora_salida) <= (d.sal_max_ma)::interval) AND (fecha >= d.fecha_ini) AND > (fecha <= d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 6" > " -> Limit (cost=925.55..925.57 rows=1 width=12) (actual > time=2.371..2.371 rows=1 loops=1412)" > " -> GroupAggregate (cost=925.55..926.57 rows=48 width=12) (actual > time=2.370..2.370 rows=1 loops=1412)" > " Group Key: m1_5.fecha" > " -> Sort (cost=925.55..925.73 rows=71 width=12) (actual > time=2.367..2.367 rows=1 loops=1412)" > " Sort Key: m1_5.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_5 (cost=0.29..923.37 rows=71 width=12) (actual time=1.023..2.364 > rows=1 loops=1412)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d.hora_salida) > '-00:59:00'::interval) AND ((hora - > d.hora_salida) <= (d.sal_max_ma)::interval) AND (fecha >= d.fecha_ini) AND > (fecha <= d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 7" > " -> Limit (cost=854.99..855.01 rows=1 width=12) (actual > time=2.354..2.354 rows=1 loops=2164)" > " -> GroupAggregate (cost=854.99..856.00 rows=48 width=12) (actual > time=2.353..2.353 rows=1 loops=2164)" > " Group Key: m1_6.fecha" > " -> Sort (cost=854.99..855.17 rows=71 width=12) (actual > time=2.350..2.350 rows=1 loops=2164)" > " Sort Key: m1_6.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_6 (cost=0.29..852.80 rows=71 width=12) (actual time=1.614..2.347 > rows=1 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d1.hora_ingreso) > '-00:59:00'::interval) AND ((hora - > d1.hora_salida) <= '00:00:00'::interval) AND (fecha >= d1.fecha_ini) AND > (fecha <= d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 8" > " -> Limit (cost=854.99..855.01 rows=1 width=12) (actual > time=2.350..2.350 rows=1 loops=2164)" > " -> GroupAggregate (cost=854.99..856.00 rows=48 width=12) (actual > time=2.349..2.349 rows=1 loops=2164)" > " Group Key: m1_7.fecha" > " -> Sort (cost=854.99..855.17 rows=71 width=12) (actual > time=2.347..2.347 rows=1 loops=2164)" > " Sort Key: m1_7.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_7 (cost=0.29..852.80 rows=71 width=12) (actual time=1.609..2.344 > rows=1 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d1.hora_ingreso) > '-00:59:00'::interval) AND ((hora - > d1.hora_salida) <= '00:00:00'::interval) AND (fecha >= d1.fecha_ini) AND > (fecha <= d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 9" > " -> Limit (cost=854.99..855.01 rows=1 width=12) (actual > time=2.374..2.375 rows=1 loops=518)" > " -> GroupAggregate (cost=854.99..856.00 rows=48 width=12) (actual > time=2.373..2.373 rows=1 loops=518)" > " Group Key: m1_8.fecha" > " -> Sort (cost=854.99..855.17 rows=71 width=12) (actual > time=2.371..2.371 rows=1 loops=518)" > " Sort Key: m1_8.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_8 (cost=0.29..852.80 rows=71 width=12) (actual time=0.956..2.367 > rows=1 loops=518)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d1.hora_ingreso) > '-00:59:00'::interval) AND ((hora - > d1.hora_salida) <= '00:00:00'::interval) AND (fecha >= d1.fecha_ini) AND > (fecha <= d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > " SubPlan 10" > " -> Limit (cost=925.55..925.57 rows=1 width=12) (actual > time=2.361..2.361 rows=0 loops=2164)" > " -> GroupAggregate (cost=925.55..926.57 rows=48 width=12) (actual > time=2.360..2.360 rows=0 loops=2164)" > " Group Key: m1_9.fecha" > " -> Sort (cost=925.55..925.73 rows=71 width=12) (actual > time=2.358..2.358 rows=0 loops=2164)" > " Sort Key: m1_9.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_9 (cost=0.29..923.37 rows=71 width=12) (actual time=1.786..2.355 > rows=0 loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d1.hora_salida) > '-00:59:00'::interval) AND ((hora - > d1.hora_salida) <= (d1.sal_max_ta)::interval) AND (fecha >= d1.fecha_ini) AND > (fecha <= d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Filter: 14113" > " Heap Fetches: 0" > " SubPlan 11" > " -> GroupAggregate (cost=925.55..926.57 rows=48 width=12) (actual > time=2.373..2.373 rows=0 loops=2164)" > " Group Key: m1_10.fecha" > " -> Sort (cost=925.55..925.73 rows=71 width=12) (actual > time=2.371..2.371 rows=0 loops=2164)" > " Sort Key: m1_10.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on trans > m1_10 (cost=0.29..923.37 rows=71 width=12) (actual time=1.798..2.368 rows=0 > loops=2164)" > " Filter: (((empid = m.empid) AND (m.fecha = fecha) AND > ((hora - d1.hora_salida) > '-00:59:00'::interval) AND ((hora - > d1.hora_salida) <= (d1.sal_max_ta)::interval) AND (fecha >= d1.fecha_ini) AND > (fecha <= d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Filter: 14113" > " Heap Fetches: 0" > " SubPlan 12" > " -> Limit (cost=925.55..925.57 rows=1 width=12) (actual > time=2.348..2.348 rows=1 loops=834)" > " -> GroupAggregate (cost=925.55..926.57 rows=48 width=12) (actual > time=2.347..2.347 rows=1 loops=834)" > " Group Key: m1_11.fecha" > " -> Sort (cost=925.55..925.73 rows=71 width=12) (actual > time=2.345..2.345 rows=1 loops=834)" > " Sort Key: m1_11.fecha" > " Sort Method: quicksort Memory: 17kB" > " -> Index Only Scan using trans_emp_fecha_hora on > trans m1_11 (cost=0.29..923.37 rows=71 width=12) (actual time=1.077..2.342 > rows=1 loops=834)" > " Filter: (((empid = m.empid) AND (m.fecha = > fecha) AND ((hora - d1.hora_salida) > '-00:59:00'::interval) AND ((hora - > d1.hora_salida) <= (d1.sal_max_ta)::interval) AND (fecha >= d1.fecha_ini) AND > (fecha <= d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" > " Rows Removed by Filter: 14112" > " Heap Fetches: 0" > "Planning time: 2.825 ms" > "Execution time: 49375.842 ms" > > > > > > 2018-03-08 8:48 GMT-05:00 jvenegasperu . <jvenegasp...@gmail.com>: > >> Sthepen >> Buen dia >> >> En efecto tengo este indice creado >> >> create index emp_fecha_hora on trans (empid,CAST (dt AS date),CAST(dt as >> time)); >> >> y este filtro lo uso varias veces >> >> m1.empid = m.empid AND m.fecha = m1.dt::date AND (m1.dt::time without >> time zone - d.hora_ingreso) > '-00:59:00'::interval >> >> en el explain en la pagina de depesz en el item 14 indica Bitmap Heap >> Scan entiendo que aqui esta usando este tipo de indice? para el filtro >> >> Estoy suponiendo que el item 14 indica que esta usando el indce pero en >> los demas casos no me podrias orientar porque no usa el indice en los demas >> alguna idea? >> >> por lo que me dices supongo que hacer el CAST al crear el indice estaria >> mal? >> >> al hacer el filtro en la consulta esta mal esto m1.dt::time.? >> >> ambas cosas estan mal? >> >> probare creando un campo mas en la tabla donde ya este la hora y no >> necesitar el cast >> >> >> >> >> >> >> 2018-03-08 8:16 GMT-05:00 Stephen Amell <stephenam...@inbox.lv>: >> >>> Hola Jose, >>> >>> Pase el explain por https://explain.depesz.com/s/rdZT para verlo un >>> poco mas grafico, en la solapa stats podes ver en que se va el consumo. >>> Ahí veo mucho seq scan, que cuando lo ves desde la solapa html me hace >>> apostar por un tema de cast + indices en m1_7, m1_8 y m1_9 >>> >>> Salu2 >>> >>> On 2018-03-07 17:54, jvenegasperu . wrote: >>> >>> Buen dia ahora me ha tocado lidiar con una tabla de fechas y horas >>> >>> Tengo la consulta que muestro lineas mas abajo y luego el resultado del >>> explain analyze por favor alguna sugerencia para mejorar el tiempo de >>> respuesta >>> >>> hasta el momento la tabla solo tiene estos dos indices que le agregue >>> pero solo he logrado dismunir el tiempo en 5 segundos la consulta tarda >>> aproximadamente 2 minutos >>> >>> CREATE INDEX trans_dt ON trans (dt); >>> create index emp_fecha_hora on trans (empid,CAST (dt AS date),CAST(dt as >>> time)); >>> >>> SELECT m.empid AS dni, m.empname AS nombre_empleado, '201803' AS >>> periodo, m.fecha, >>> dia_semana(m.fecha) AS dia_semana, ( >>> SELECT min(m1.dt::time without time zone) AS min FROM trans m1 >>> WHERE m1.empid = m.empid AND m.fecha = m1.dt::date >>> >>> AND (m1.dt::time without time zone - d.hora_ingreso) > >>> '-00:59:00'::interval >>> AND (m1.dt::time without time zone - d.hora_ingreso) <= >>> d.hora_salida::interval AND >>> m1.dt >= d.fecha_ini and m1.dt <= d.fecha_fin and d.turno_id = 1 or >>> d.turno_id = 3 and m1.dt::time without time zone < d.hora_salida >>> GROUP BY (m1.dt::date)limit 1) AS hor_ing_ma, >>> CASE WHEN ((( SELECT min(m1.dt::time without time zone) AS min FROM >>> trans m1 >>> WHERE m1.empid = m.empid AND m.fecha = m1.dt::date >>> AND (m1.dt::time without time zone - d.hora_ingreso) > >>> '-00:59:00'::interval >>> AND (m1.dt::time without time zone - d.hora_salida) <= >>> '00'::interval >>> AND m1.dt >= d.fecha_ini and m1.dt <= d.fecha_fin and >>> d.turno_id = 1 >>> or d.turno_id = 3 GROUP BY (m1.dt::date) limit 1)) - >>> d.hora_ingreso) >= '00:01:00'::time without time zone::interval >>> THEN (( SELECT min(m1.dt::time without time zone) AS min >>> FROM trans m1 >>> WHERE m1.empid = m.empid AND m.fecha = m1.dt::date >>> AND (m1.dt::time without time zone - d.hora_ingreso) >>> > '-00:59:00'::interval >>> AND (m1.dt::time without time zone - d.hora_salida) >>> <= '00'::interval >>> AND m1.dt >= d.fecha_ini and m1.dt <= d.fecha_fin >>> and d.turno_id = 1 or d.turno_id = 3 GROUP BY >>> (m1.dt::date) limit 1)) - d.hora_ingreso ELSE NULL::interval END AS >>> tardanz_ma, >>> ( SELECT min(m1.dt::time without time zone) AS min >>> FROM trans m1 >>> WHERE m1.empid = m.empid AND m.fecha = m1.dt::date >>> AND (m1.dt::time without time zone - d.hora_salida) >>> > '-00:59:00'::interval >>> AND (m1.dt::time without time zone - d.hora_salida) >>> <= d.sal_max_ma::interval >>> AND m1.dt >= d.fecha_ini and m1.dt <= d.fecha_fin >>> and d.turno_id = 1 or d.turno_id = 3 GROUP BY >>> (m1.dt::date) limit 1) AS hor_sal_ma, >>> CASE WHEN ((( SELECT min(m1.dt::time without time >>> zone) AS min FROM trans m1 >>> WHERE m1.empid = m.empid AND m.fecha = m1.dt::date >>> AND (m1.dt::time without time zone - d.hora_salida) >>> > '-00:59:00'::interval >>> AND (m1.dt::time without time zone - d.hora_salida) >>> <= d.sal_max_ma::interval >>> AND m1.dt >= d.fecha_ini and m1.dt <= d.fecha_fin >>> and d.turno_id = 1 or d.turno_id = 3 GROUP BY >>> (m1.dt::date) limit 1 )) - d.hora_salida) >= '00:01:00'::time without time >>> zone::interval THEN (( SELECT min(m1.dt::time without time zone) AS min >>> FROM trans m1 WHERE m1.empid = m.empid AND m.fecha = m1.dt::date AND >>> (m1.dt::time without time zone - d.hora_salida) > '-00:59:00'::interval AND >>> (m1.dt::time without time zone - d.hora_salida) <= d.sal_max_ma::interval >>> AND m1.dt >= d.fecha_ini and m1.dt <= d.fecha_fin and d.turno_id = 1 or >>> d.turno_id = 3 GROUP BY (m1.dt::date) limit 1 )) - d.hora_salida ELSE >>> NULL::interval END AS tiem_compensar_ma, ( SELECT min(m1.dt::time without >>> time zone) AS min FROM trans m1 WHERE m1.empid = m.empid AND m.fecha = >>> m1.dt::date AND (m1.dt::time without time zone - d1.hora_ingreso) > >>> '-00:59:00'::interval AND (m1.dt::time without time zone - d1.hora_salida) >>> <= '00'::interval AND m1.dt >= d1.fecha_ini and m1.dt <= d1.fecha_fin and >>> d1.turno_id = 2 or d1.turno_id = 4 GROUP BY (m1.dt::date) limit 1 ) AS >>> hor_ing_ta, CASE WHEN ((( SELECT min(m1.dt::time without time zone) AS min >>> FROM trans m1 WHERE m1.empid = m.empid AND m.fecha = m1.dt::date AND >>> (m1.dt::time without time zone - d1.hora_ingreso) > '-00:59:00'::interval >>> AND (m1.dt::time without time zone - d1.hora_salida) <= '00'::interval AND >>> m1.dt >= d1.fecha_ini and m1.dt <= d1.fecha_fin and d1.turno_id = 2 or >>> d1.turno_id = 4 GROUP BY (m1.dt::date) limit 1)) - d1.hora_ingreso) >= >>> '00:01:00'::time without time zone::interval THEN (( SELECT min(m1.dt::time >>> without time zone) AS min FROM trans m1 WHERE m1.empid = m.empid AND >>> m.fecha = m1.dt::date AND (m1.dt::time without time zone - d1.hora_ingreso) >>> > '-00:59:00'::interval AND (m1.dt::time without time zone - >>> d1.hora_salida) <= '00'::interval AND m1.dt >= d1.fecha_ini and m1.dt <= >>> d1.fecha_fin and d1.turno_id = 2 or d1.turno_id = 4 GROUP BY (m1.dt::date) >>> limit 1 )) - d1.hora_ingreso ELSE NULL::interval END AS tardanz_ta, ( >>> SELECT min(m1.dt::time without time zone) AS min FROM trans m1 WHERE >>> m1.empid = m.empid AND m.fecha = m1.dt::date AND (m1.dt::time without time >>> zone - d1.hora_salida) > '-00:59:00'::interval AND (m1.dt::time without >>> time zone - d1.hora_salida) <= d1.sal_max_ta::interval AND m1.dt >= >>> d1.fecha_ini and m1.dt <= d1.fecha_fin and d1.turno_id = 2 or d1.turno_id = >>> 4 GROUP BY (m1.dt::date) limit 1 ) AS hor_sal_ta, CASE WHEN ((( SELECT >>> min(m1.dt::time without time zone) AS min FROM trans m1 WHERE m1.empid = >>> m.empid AND m.fecha = m1.dt::date AND (m1.dt::time without time zone - >>> d1.hora_salida) > '-00:59:00'::interval AND (m1.dt::time without time zone >>> - d1.hora_salida) <= d1.sal_max_ta::interval AND m1.dt >= d1.fecha_ini and >>> m1.dt <= d1.fecha_fin and d1.turno_id = 2 or d1.turno_id = 4 GROUP BY >>> (m1.dt::date))) - d1.hora_salida) >= '00:01:00'::time without time >>> zone::interval >>> THEN (( SELECT min(m1.dt::time without time zone) AS >>> min FROM trans m1 >>> WHERE m1.empid = m.empid AND m.fecha = m1.dt::date >>> AND (m1.dt::time without time zone - >>> d1.hora_salida) > '-00:59:00'::interval >>> AND (m1.dt::time without time zone - >>> d1.hora_salida) <= d1.sal_max_ta::interval >>> AND m1.dt >= d1.fecha_ini and m1.dt <= >>> d1.fecha_fin >>> and d1.turno_id = 2 or d1.turno_id = 4 GROUP BY >>> (m1.dt::date) limit 1 )) - d1.hora_salida ELSE NULL::interval END AS >>> tiem_compensar_ta FROM trans m LEFT JOIN rrhh_persona_horario d on m.empid >>> = d.documento and d.activo = 1 and d.turno_id = 1 or d.turno_id = 3 LEFT >>> JOIN rrhh_persona_horario d1 on m.empid = d1.documento and d1.activo = 1 >>> and d1.turno_id = 2 or d1.turno_id = 4 WHERE ((m.dt >= d.fecha_ini and m.dt >>> <= d.fecha_fin ) or (m.dt >= d1.fecha_ini and m.dt <= d1.fecha_fin )) and >>> (m.dt >= '20180226' and m.dt <= '20180307') >>> GROUP BY m.empid, m.empname, m.fecha, d.fecha_ini,d.fecha_fin,d.hora >>> _ingreso,d.hora_salida,d.turno_id,d.sal_max_ma,d.sal_max_ta, >>> d1.fecha_ini,d1.fecha_fin,d1.hora_ingreso,d1.hora_salida,d1. >>> turno_id,d1.sal_max_ma,d1.sal_max_ta ORDER BY m.fecha; >>> >>> >>> "Group (cost=7474.69..3976090.10 rows=337 width=263) (actual >>> time=232.781..109954.357 rows=2007 loops=1)" >>> " Group Key: m.fecha, m.empid, m.empname, d.fecha_ini, d.fecha_fin, >>> d.hora_ingreso, d.hora_salida, d.turno_id, d.sal_max_ma, d.sal_max_ta, >>> d1.fecha_ini, d1.fecha_fin, d1.hora_ingreso, d1.hora_salida, d1.turno_id, >>> d1.sal_max_ma, d1.sal_max_ta" >>> " -> Sort (cost=7474.69..7475.54 rows=337 width=103) (actual >>> time=182.457..183.850 rows=5575 loops=1)" >>> " Sort Key: m.fecha, m.empid, m.empname, d.fecha_ini, d.fecha_fin, >>> d.hora_ingreso, d.hora_salida, d.turno_id, d.sal_max_ma, d.sal_max_ta, >>> d1.fecha_ini, d1.fecha_fin, d1.hora_ingreso, d1.hora_salida, d1.turno_id, >>> d1.sal_max_ma, d1.sal_max_ta" >>> " Sort Method: quicksort Memory: 887kB" >>> " -> Nested Loop Left Join (cost=0.29..7460.55 rows=337 width=103) >>> (actual time=0.572..139.117 rows=5575 loops=1)" >>> " Join Filter: (((m.empid = (d.documento)::text) AND (d.activo >>> = 1) AND (d.turno_id = 1)) OR (d.turno_id = 3))" >>> " Rows Removed by Join Filter: 260627" >>> " Filter: (((m.dt >= d.fecha_ini) AND (m.dt <= d.fecha_fin)) >>> OR ((m.dt >= d1.fecha_ini) AND (m.dt <= d1.fecha_fin)))" >>> " Rows Removed by Filter: 6050" >>> " -> Nested Loop Left Join (cost=0.29..2908.81 rows=1319 >>> width=73) (actual time=0.201..46.688 rows=2347 loops=1)" >>> " Join Filter: (((m.empid = (d1.documento)::text) AND >>> (d1.activo = 1) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Join Filter: 134955" >>> " -> Index Scan using trans_dt on trans m >>> (cost=0.29..135.13 rows=1294 width=35) (actual time=0.034..1.049 rows=1383 >>> loops=1)" >>> " Index Cond: ((dt >= '2018-02-26 >>> 00:00:00'::timestamp without time zone) AND (dt <= '2018-03-07 >>> 00:00:00'::timestamp without time zone))" >>> " -> Materialize (cost=0.00..7.99 rows=95 width=49) >>> (actual time=0.000..0.006 rows=99 loops=1383)" >>> " -> Seq Scan on rrhh_persona_horario d1 >>> (cost=0.00..7.52 rows=95 width=49) (actual time=0.017..0.106 rows=99 >>> loops=1)" >>> " Filter: (((activo = 1) AND (turno_id = 2)) >>> OR (turno_id = 4))" >>> " Rows Removed by Filter: 116" >>> " -> Materialize (cost=0.00..8.05 rows=106 width=49) (actual >>> time=0.000..0.007 rows=116 loops=2347)" >>> " -> Seq Scan on rrhh_persona_horario d >>> (cost=0.00..7.52 rows=106 width=49) (actual time=0.008..0.096 rows=116 >>> loops=1)" >>> " Filter: (((activo = 1) AND (turno_id = 1)) OR >>> (turno_id = 3))" >>> " Rows Removed by Filter: 99" >>> " SubPlan 1" >>> " -> Limit (cost=976.07..976.09 rows=1 width=12) (actual >>> time=2.607..2.607 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=976.07..976.61 rows=24 width=12) >>> (actual time=2.605..2.605 rows=1 loops=2007)" >>> " Group Key: ((m1.dt)::date)" >>> " -> Sort (cost=976.07..976.13 rows=24 width=12) (actual >>> time=2.595..2.595 rows=3 loops=2007)" >>> " Sort Key: ((m1.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Bitmap Heap Scan on trans m1 >>> (cost=414.41..975.52 rows=24 width=12) (actual time=2.314..2.547 rows=96 >>> loops=2007)" >>> " Recheck Cond: (((empid = m.empid) AND (m.fecha >>> = (dt)::date)) OR ((dt)::time without time zone < d.hora_salida))" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d.hora_ingreso) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d.hora_ingreso) >>> <= (d.hora_salida)::interval) AND (dt >= d.fecha_ini) AND (dt <= >>> d.fecha_fin) AND (d.turno_id = 1)) OR ((d.turno_id = 3) AND ((dt)::time >>> without time zone < d.hora_salida)))" >>> " Rows Removed by Filter: 2658" >>> " Heap Blocks: exact=264670" >>> " -> BitmapOr (cost=414.41..414.41 rows=4881 >>> width=0) (actual time=1.250..1.250 rows=0 loops=2007)" >>> " -> Bitmap Index Scan on emp_fecha_hora >>> (cost=0.00..4.29 rows=1 width=0) (actual time=0.033..0.033 rows=3 >>> loops=2007)" >>> " Index Cond: ((empid = m.empid) AND >>> (m.fecha = (dt)::date))" >>> " -> Bitmap Index Scan on emp_fecha_hora >>> (cost=0.00..410.10 rows=4881 width=0) (actual time=1.210..1.210 rows=2752 >>> loops=2007)" >>> " Index Cond: ((dt)::time without >>> time zone < d.hora_salida)" >>> " SubPlan 2" >>> " -> Limit (cost=941.73..941.75 rows=1 width=12) (actual >>> time=9.182..9.182 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=941.73..943.37 rows=73 width=12) >>> (actual time=9.179..9.179 rows=1 loops=2007)" >>> " Group Key: ((m1_1.dt)::date)" >>> " -> Sort (cost=941.73..941.91 rows=73 width=12) (actual >>> time=9.057..9.064 rows=125 loops=2007)" >>> " Sort Key: ((m1_1.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_1 (cost=0.00..939.47 >>> rows=73 width=12) (actual time=1.832..7.138 rows=8842 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d.hora_ingreso) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d.hora_salida) >>> <= '00:00:00'::interval) AND (dt >= d.fecha_ini) AND (dt <= d.fecha_fin) >>> AND (d.turno_id = 1)) OR (d.turno_id = 3))" >>> " Rows Removed by Filter: 5800" >>> " SubPlan 3" >>> " -> Limit (cost=941.73..941.75 rows=1 width=12) (actual >>> time=5.200..5.201 rows=1 loops=161)" >>> " -> GroupAggregate (cost=941.73..943.37 rows=73 width=12) >>> (actual time=5.198..5.198 rows=1 loops=161)" >>> " Group Key: ((m1_2.dt)::date)" >>> " -> Sort (cost=941.73..941.91 rows=73 width=12) (actual >>> time=5.187..5.187 rows=1 loops=161)" >>> " Sort Key: ((m1_2.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_2 (cost=0.00..939.47 >>> rows=73 width=12) (actual time=4.603..5.164 rows=1 loops=161)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d.hora_ingreso) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d.hora_salida) >>> <= '00:00:00'::interval) AND (dt >= d.fecha_ini) AND (dt <= d.fecha_fin) >>> AND (d.turno_id = 1)) OR (d.turno_id = 3))" >>> " Rows Removed by Filter: 14641" >>> " SubPlan 4" >>> " -> Limit (cost=1014.94..1014.96 rows=1 width=12) (actual >>> time=9.184..9.185 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=1014.94..1016.58 rows=73 width=12) >>> (actual time=9.182..9.182 rows=1 loops=2007)" >>> " Group Key: ((m1_3.dt)::date)" >>> " -> Sort (cost=1014.94..1015.12 rows=73 width=12) (actual >>> time=9.041..9.049 rows=125 loops=2007)" >>> " Sort Key: ((m1_3.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_3 (cost=0.00..1012.68 >>> rows=73 width=12) (actual time=1.810..7.104 rows=8843 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d.hora_salida) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d.hora_salida) >>> <= (d.sal_max_ma)::interval) AND (dt >= d.fecha_ini) AND (dt <= >>> d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" >>> " Rows Removed by Filter: 5799" >>> " SubPlan 5" >>> " -> Limit (cost=1014.94..1014.96 rows=1 width=12) (actual >>> time=9.253..9.254 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=1014.94..1016.58 rows=73 width=12) >>> (actual time=9.251..9.251 rows=1 loops=2007)" >>> " Group Key: ((m1_4.dt)::date)" >>> " -> Sort (cost=1014.94..1015.12 rows=73 width=12) (actual >>> time=9.073..9.080 rows=125 loops=2007)" >>> " Sort Key: ((m1_4.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_4 (cost=0.00..1012.68 >>> rows=73 width=12) (actual time=1.803..7.108 rows=8843 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d.hora_salida) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d.hora_salida) >>> <= (d.sal_max_ma)::interval) AND (dt >= d.fecha_ini) AND (dt <= >>> d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" >>> " Rows Removed by Filter: 5799" >>> " SubPlan 6" >>> " -> Limit (cost=1014.94..1014.96 rows=1 width=12) (actual >>> time=4.896..4.896 rows=1 loops=515)" >>> " -> GroupAggregate (cost=1014.94..1016.58 rows=73 width=12) >>> (actual time=4.894..4.894 rows=1 loops=515)" >>> " Group Key: ((m1_5.dt)::date)" >>> " -> Sort (cost=1014.94..1015.12 rows=73 width=12) (actual >>> time=4.884..4.885 rows=2 loops=515)" >>> " Sort Key: ((m1_5.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_5 (cost=0.00..1012.68 >>> rows=73 width=12) (actual time=4.331..4.865 rows=2 loops=515)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d.hora_salida) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d.hora_salida) >>> <= (d.sal_max_ma)::interval) AND (dt >= d.fecha_ini) AND (dt <= >>> d.fecha_fin) AND (d.turno_id = 1)) OR (d.turno_id = 3))" >>> " Rows Removed by Filter: 14640" >>> " SubPlan 7" >>> " -> Limit (cost=941.73..941.75 rows=1 width=12) (actual >>> time=4.933..4.933 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=941.73..943.37 rows=73 width=12) >>> (actual time=4.931..4.931 rows=1 loops=2007)" >>> " Group Key: ((m1_6.dt)::date)" >>> " -> Sort (cost=941.73..941.91 rows=73 width=12) (actual >>> time=4.923..4.923 rows=1 loops=2007)" >>> " Sort Key: ((m1_6.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_6 (cost=0.00..939.47 >>> rows=73 width=12) (actual time=4.563..4.901 rows=1 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d1.hora_ingreso) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d1.hora_salida) >>> <= '00:00:00'::interval) AND (dt >= d1.fecha_ini) AND (dt <= d1.fecha_fin) >>> AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Filter: 14641" >>> " SubPlan 8" >>> " -> Limit (cost=941.73..941.75 rows=1 width=12) (actual >>> time=4.899..4.899 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=941.73..943.37 rows=73 width=12) >>> (actual time=4.897..4.897 rows=1 loops=2007)" >>> " Group Key: ((m1_7.dt)::date)" >>> " -> Sort (cost=941.73..941.91 rows=73 width=12) (actual >>> time=4.890..4.890 rows=1 loops=2007)" >>> " Sort Key: ((m1_7.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_7 (cost=0.00..939.47 >>> rows=73 width=12) (actual time=4.532..4.870 rows=1 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d1.hora_ingreso) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d1.hora_salida) >>> <= '00:00:00'::interval) AND (dt >= d1.fecha_ini) AND (dt <= d1.fecha_fin) >>> AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Filter: 14641" >>> " SubPlan 9" >>> " -> Limit (cost=941.73..941.75 rows=1 width=12) (actual >>> time=4.911..4.912 rows=1 loops=367)" >>> " -> GroupAggregate (cost=941.73..943.37 rows=73 width=12) >>> (actual time=4.909..4.909 rows=1 loops=367)" >>> " Group Key: ((m1_8.dt)::date)" >>> " -> Sort (cost=941.73..941.91 rows=73 width=12) (actual >>> time=4.900..4.900 rows=1 loops=367)" >>> " Sort Key: ((m1_8.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_8 (cost=0.00..939.47 >>> rows=73 width=12) (actual time=4.235..4.880 rows=1 loops=367)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d1.hora_ingreso) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d1.hora_salida) >>> <= '00:00:00'::interval) AND (dt >= d1.fecha_ini) AND (dt <= d1.fecha_fin) >>> AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Filter: 14641" >>> " SubPlan 10" >>> " -> Limit (cost=1014.94..1014.96 rows=1 width=12) (actual >>> time=5.028..5.028 rows=1 loops=2007)" >>> " -> GroupAggregate (cost=1014.94..1016.58 rows=73 width=12) >>> (actual time=5.026..5.026 rows=1 loops=2007)" >>> " Group Key: ((m1_9.dt)::date)" >>> " -> Sort (cost=1014.94..1015.12 rows=73 width=12) (actual >>> time=5.019..5.019 rows=1 loops=2007)" >>> " Sort Key: ((m1_9.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_9 (cost=0.00..1012.68 >>> rows=73 width=12) (actual time=4.646..4.999 rows=1 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d1.hora_salida) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d1.hora_salida) >>> <= (d1.sal_max_ta)::interval) AND (dt >= d1.fecha_ini) AND (dt <= >>> d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Filter: 14641" >>> " SubPlan 11" >>> " -> GroupAggregate (cost=1014.94..1016.58 rows=73 width=12) (actual >>> time=4.923..4.923 rows=1 loops=2007)" >>> " Group Key: ((m1_10.dt)::date)" >>> " -> Sort (cost=1014.94..1015.12 rows=73 width=12) (actual >>> time=4.916..4.916 rows=1 loops=2007)" >>> " Sort Key: ((m1_10.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_10 (cost=0.00..1012.68 rows=73 >>> width=12) (actual time=4.555..4.898 rows=1 loops=2007)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d1.hora_salida) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d1.hora_salida) >>> <= (d1.sal_max_ta)::interval) AND (dt >= d1.fecha_ini) AND (dt <= >>> d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Filter: 14641" >>> " SubPlan 12" >>> " -> Limit (cost=1014.94..1014.96 rows=1 width=12) (actual >>> time=4.901..4.901 rows=1 loops=836)" >>> " -> GroupAggregate (cost=1014.94..1016.58 rows=73 width=12) >>> (actual time=4.899..4.899 rows=1 loops=836)" >>> " Group Key: ((m1_11.dt)::date)" >>> " -> Sort (cost=1014.94..1015.12 rows=73 width=12) (actual >>> time=4.890..4.890 rows=1 loops=836)" >>> " Sort Key: ((m1_11.dt)::date)" >>> " Sort Method: quicksort Memory: 17kB" >>> " -> Seq Scan on trans m1_11 (cost=0.00..1012.68 >>> rows=73 width=12) (actual time=4.241..4.870 rows=1 loops=836)" >>> " Filter: (((empid = m.empid) AND (m.fecha = >>> (dt)::date) AND (((dt)::time without time zone - d1.hora_salida) > >>> '-00:59:00'::interval) AND (((dt)::time without time zone - d1.hora_salida) >>> <= (d1.sal_max_ta)::interval) AND (dt >= d1.fecha_ini) AND (dt <= >>> d1.fecha_fin) AND (d1.turno_id = 2)) OR (d1.turno_id = 4))" >>> " Rows Removed by Filter: 14641" >>> "Planning time: 2.927 ms" >>> "Execution time: 109957.969 ms" >>> >>> >>> >>> -- >>> José Mercedes Venegas Acevedo >>> cel Mov RPC 964185205 >>> >>> >>> >>> >> >> >> -- >> José Mercedes Venegas Acevedo >> cel Mov RPC 964185205 >> >> >> > > > -- > José Mercedes Venegas Acevedo > cel Mov RPC 964185205 > > > -- fElIpE
