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

Reply via email to