Re: Optimizar consulta a tabla con fechas y horas

Hola Felipe
Buenas tardes

Aqui mis comentarios

>
> Realice una sql de una forma diferente, y me atreveria a decir que la tuya
> no te arroja información correcta; por lo que me has dado a entender
> quieres saber los registros de un usuario en un rango de fechas dado ( (m.fecha
> >= '2017-12-26' and m.fecha <= '2018-01-25') , hice unos cruces simples
> para el dni 15683298 y mientras a ti solo te arroja dos registros, a mi
> me arroja 23, todos con dias distintos, lo que quiere decir que este señor
> fue a trabajar 23 veces en ese rango de fechas.
>

Esto se debe Felipe a que en mi consulta yo verifico que la fecha de la
marcacion este dentro de las fechas de los turnos ya se para la mañana o
para la tarde es decir se debe haber asignado previamente un horario para
el rango de fechas si verificas la data el dni 15683298 solo tiene asignado
horario a partir del dia 24 por eso yo obtengos dos registros hasta el dia
25 y tu 23 porque tu consulta no verifica las fechas del horario.

en mi consulta si se retira esta parte del where yo tambien obtengo 23
registros sin embargo las horas aparecen en null porque no hay horas para
comparar.

(( m.fecha >= d.fecha_ini and m.fecha <= d.fecha_fin )
or ( m.fecha >= d1.fecha_ini and m.fecha <= d1.fecha_fin ))

>
> Por otro lado, tu solo compensas al que se va tarde, pero no penalizas al
> que se va antes de la hora de salida, ni tampoco compensas al que llega
> temprano y otra cosa muy importante que miro es que tu en la tabla trans no
> identificas el turno_id, por tanto si una persona tiene asignados dos
> turnos sea 1 y 3 o 2 y 4 tu no sabes si el registro pertenece a que turno,
> tu lo asumes, pero no es correcto, creo que deberias adicionar ese campo en
> la tabla trans. Como pude identificar este problema, porque dependiendo del
> turno el trabajador podia salir a las 12 o a las 13, pero al no tener
> cesteza de a que turno pertenece, tu asumes el de la hora menor
> compensando al trabajador, donde se podria dar el caso de que el turno
> tenga la salida a las 13 y en realidad este se este yendo antes.
>

En parte tienes razon Felipe sin embargo donde me pidieron elaborar esta
aplicacion no se tiene pensado compensar si alguien llega unos minutos
antes, respecto a penalizar si se va antes lo voy a preguntar para ver como
incoporarlo seria bueno por otro lado si bien un trabajador puede tener
asignado los turnos 1 y 2 turnos por ejemplo dentro de un mismo periodo
estos por politica siempre seran en fechas diferentes por eso lo estoy
asumiendo por ejemplo un trabajador tiene turno 1 mañana de 7 am a 11 am
para los primeros 5 dias y luego el turno 2 de 8 am a 12 para el resto del
mes por eso evaluo las fechas del turno con la fecha de la marcacion.
Con respecto a agregar a la tabla trans el campo turno no veo forma de que
se pueda informar eso ya que esa tabla se alimenta de un marcador
biometrico y las personas rotan de turnos practicamente todas las semanas
no veo como se podria llenar el campo turno al momento de la marcacion asi
que creo que con la evaluación en la consulta esta bien.

y respecto a la query que me escribiste y te agradezco bastante por haberlo
hecho encontre este inconveniente dado que yo voy a guardar el resultado de
esta consulta en una tabla de detalle y luego en otra tabla se suman la
cantidad de minutos tarde y los minutos para compesar de cada periodo. y
estan hechos primero las mañana y luego las tardes.

si pruebas por ejemplo con el dni 18201898 obtendras datos en la mañana y
la tarde cuando se trata solo de datos de la tarde y esto llevaria a
descuentos o compensaciones dobles.

y tambien si pruebas con el dni 17936496 no se obtiene ningun registro esto
debido a que esta persona solo tiene turnos de la tarde.

Asi que modifique mi consulta para que muestre todos los registros aunque
no tenga horario asignado en la fecha de ese modo si todas las horas estan
vacias significa que esa fecha no tenia horario asignado.

asi que la consulta que estoy manejando ahora es la que dejo lineas abajo y
te dejo comentado los dnis por si quieres probar y lo esta obteniendo en
0.07seg lo cual creo q ahora esta perfecto considerando que incluso esta
devolviendo mas registros que antes ahora son aprox 2000.

PD Francisco gracias por el dato del limit y el group para tenerlo en
cuenta.

with queryAsis as (SELECT m.empid AS dni,
m.empname AS nombre_empleado,
'201801' 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 (d.turno_id = 1 or d.turno_id = 3)
and m1.hora < coalesce(d.hora_salida,'13:00'::time)
) AS hor_ing_ma, d.hora_ingreso,
( 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, d.hora_salida,
( 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) 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, d1.hora_ingreso as
hora_ingreso_t,
( 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, d1.hora_salida as
hora_salida_t
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)
and ( m.fecha >= d.fecha_ini and m.fecha <= d.fecha_fin )
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)
and ( m.fecha >= d1.fecha_ini and m.fecha <= d1.fecha_fin )
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 BETWEEN '2017-12-26' AND '2018-01-25')
-- (m.fecha >= '2017-12-26' and m.fecha <= '2018-01-25')
-- and m.empid = '15683298'
-- and m.empid = '18201898'
-- and empid = '17936496'
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 desc)

select dni,nombre_empleado,periodo,fecha,dia_semana,hor_ing_ma,
CASE WHEN ((hor_ing_ma - hora_ingreso) >= '00:01:00'::interval) THEN
hor_ing_ma - hora_ingreso ELSE NULL::interval END as tardanz_ma,
hor_sal_ma,
CASE WHEN ((hor_sal_ma - hora_salida) >= '00:01:00'::interval) THEN
hor_sal_ma - hora_salida ELSE NULL::interval END as
tiem_compensar_ma,
hor_ing_ta,
CASE WHEN ((hor_ing_ta - hora_ingreso_t) >= '00:01:00'::interval) THEN
hor_ing_ta - hora_ingreso_t ELSE NULL::interval END as tardanz_ta,
hor_sal_ta,
CASE WHEN ((hor_sal_ta - hora_salida_t) >= '00:01:00'::interval) THEN
hor_sal_ta - hora_salida_t ELSE NULL::interval END as tiem_compensar_ta
from queryAsis m

>
> Te dejo mi query para que la analices, espero que la entiendas porque te
> escribi poesia.
>
> Si tienes alguna duda, me escribes.
>
> SELECT
> em.dni,
> em.periodo,
> em.fecha,
> em.hora AS entra_mañana,
> CASE WHEN ((em.hora - em.hora_ingreso) >= '00:01:00'::interval) THEN
> em.hora - em.hora_ingreso
> ELSE
> NULL::interval END as tardanz_ma,
> sm.hora AS sale_mañana ,
> sm.hora_salida,
> CASE WHEN ((sm.hora - sm.hora_salida) >= '00:01:00'::interval) THEN
> sm.hora - sm.hora_salida
> ELSE
> NULL::interval END as tiem_compensar_ma,
> et.hora AS entra_tarde,
> CASE WHEN ((et.hora - et.hora_ingreso) >= '00:01:00'::interval) THEN
> et.hora - et.hora_ingreso
> ELSE
> NULL::interval END as tardanz_ta,
> st.hora AS sale_tarde,
> CASE WHEN ((st.hora - st.hora_salida) >= '00:01:00'::interval) THEN
> st.hora - st.hora_salida
> ELSE
> NULL::interval END as tiem_compensar_ta
> FROM
> --- ENTRA MAÑANA
> (SELECT DISTINCT ON(m.empid,m.fecha)
> m.empid AS dni,
> '201801' AS periodo,
> m.fecha,
> m.hora,
> d.hora_ingreso
> FROM
> rrhh_persona_horario d,
> trans m
> WHERE
> m.empid=d.documento AND
> m.fecha BETWEEN '2017-12-26' AND '2018-01-25' AND
> --m.empid='15683298' AND
> (d.turno_id=1 OR d.turno_id=3)
> ORDER BY
> m.empid,
> m.fecha DESC,
> m.hora ASC) AS em
> -- SALE MAÑANA
> LEFT OUTER JOIN
> (SELECT DISTINCT ON(m.empid,m.fecha)
> m.empid AS dni,
> '201801' AS periodo,
> m.fecha,
> m.hora,
> d.hora_salida
> FROM
> rrhh_persona_horario d,
> trans m
> WHERE
> m.empid=d.documento AND
> m.fecha BETWEEN '2017-12-26' AND '2018-01-25' AND
> --m.empid='15683298' AND
> (d.turno_id=1 OR d.turno_id=3) AND
> m.hora>d.hora_salida
> ORDER BY
> m.empid,
> m.fecha DESC,
> m.hora ASC) AS sm
> ON
> em.dni=sm.dni AND
> em.fecha=sm.fecha
> LEFT OUTER JOIN
> -- ENTRA TARDE
> (SELECT DISTINCT ON(m.empid,m.fecha)
> m.empid AS dni,
> '201801' AS periodo,
> m.fecha,
> m.hora,
> d.hora_ingreso
> FROM
> rrhh_persona_horario d,
> trans m
> WHERE
> m.empid=d.documento AND
> m.fecha BETWEEN '2017-12-26' AND '2018-01-25' AND
> --m.empid='15683298' AND
> (d.turno_id=2 OR d.turno_id=4) AND
> m.hora>d.hora_ingreso
> ORDER BY
> m.empid,
> m.fecha DESC,
> m.hora ASC) AS et
> ON
> em.dni=et.dni AND
> em.fecha=et.fecha
> LEFT OUTER JOIN
> -- SALE TARDE
> (SELECT DISTINCT ON(m.empid,m.fecha)
> m.empid AS dni,
> '201801' AS periodo,
> m.fecha,
> m.hora,
> d.hora_salida
> FROM
> rrhh_persona_horario d,
> trans m
> WHERE
> m.empid=d.documento AND
> m.fecha BETWEEN '2017-12-26' AND '2018-01-25' AND
> --m.empid='15683298' AND
> (d.turno_id=2 OR d.turno_id=4)
> ORDER BY
> m.empid,
> m.fecha DESC,
> m.hora DESC) AS st
> ON
> em.dni=st.dni AND
> em.fecha=st.fecha
>
>
>
>
>
>
>
>
> 2018-03-25 10:30 GMT-05:00 jvenegasperu . <jvenegasperu(at)gmail(dot)com>:
>
>> Hola Felipe
>> Buen dia gracias por tu tiempo
>>
>> en efecto tienes razon revise la consulta dentro de la aplicacion la
>> consulta final que estoy usando y si falta los parentesis a esta parte
>> d.turno_id = 1 or d.turno_id = 3 debia quedar asi ( d.turno_id = 1 or
>> d.turno_id = 3 )
>>
>> turno 1 y 3 son turnos por la mañana turnos 2 y 4 son turnos por la
>> tarde. cada persona puede tener hasta dos turnos en el dia. y existen
>> cuatro tipos de turnos por eso estan clasificados asi por que a efectos de
>> mostrar el reporte necesitaba mostrar primero las mañanas y luego las
>> tardes. cualquiera de los dos que tenga asignado la persona.
>>
>> el limit y el group by se quedaron de la horrenda consulta inicial jejeje
>> acabo de quitarlo y todo sigue funcionando igual excepto porque ahora la
>> consulta tarda un par de milisegundos menos que los usaban el group by y el
>> limit 1
>>
>> Aqui te dejo la consulta como esta ahora y te adjunto datos de prueba
>> gracias. actualmente la consulta tarda 0.7 seg aprox para devolver aprox
>> 1500 registros
>>
>> with queryAsis as (SELECT m.empid AS dni,
>> m.empname AS nombre_empleado,
>> '201801' 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
>> ) AS hor_ing_ma, d.hora_ingreso,
>> ( 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)
>> ) AS hor_sal_ma, d.hora_salida,
>> ( 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) AND
>> m1.fecha >= d1.fecha_ini and m1.fecha <= d1.fecha_fin
>> and (d1.turno_id = 2 or d1.turno_id = 4)
>> ) AS hor_ing_ta, d1.hora_ingreso as hora_ingreso_t,
>> ( 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)
>> ) AS hor_sal_ta, d1.hora_salida as hora_salida_t
>> 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)
>> and ( m.fecha >= d.fecha_ini and m.fecha <= d.fecha_fin )
>> 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)
>> and ( m.fecha >= d1.fecha_ini and m.fecha <= d1.fecha_fin )
>> 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 >= '2017-12-26' and m.fecha <= '2018-01-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 )
>>
>> select dni,nombre_empleado,periodo,fecha,dia_semana,hor_ing_ma,
>> CASE WHEN ((hor_ing_ma - hora_ingreso) >= '00:01:00'::interval) THEN
>> hor_ing_ma - hora_ingreso ELSE NULL::interval END as tardanz_ma,
>> hor_sal_ma,
>> CASE WHEN ((hor_sal_ma - hora_salida) >= '00:01:00'::interval) THEN
>> hor_sal_ma - hora_salida ELSE NULL::interval END as
>> tiem_compensar_ma,
>> hor_ing_ta,
>> CASE WHEN ((hor_ing_ta - hora_ingreso_t) >= '00:01:00'::interval) THEN
>> hor_ing_ta - hora_ingreso_t ELSE NULL::interval END as tardanz_ta,
>> hor_sal_ta,
>> CASE WHEN ((hor_sal_ta - hora_salida_t) >= '00:01:00'::interval) THEN
>> hor_sal_ta - hora_salida_t ELSE NULL::interval END as tiem_compensar_ta
>> from queryAsis m
>>
>>
>>
>>
>>
>>
>>
>> 2018-03-24 18:21 GMT-05:00 Felipe Hernández <pipelx(at)gmail(dot)com>:
>>
>>> Hola de nuevo,
>>>
>>> podrias darme un copy de algunos datos de las dos tablas, necesito
>>> entender que es turno 1 y 3, turno 2 y 4, es doble jornada? necesito mas
>>> info para poder analizar mejor.
>>>
>>> 2018-03-24 17:38 GMT-05:00 Felipe Hernández <pipelx(at)gmail(dot)com>:
>>>
>>>> Hola de nuevo,
>>>>
>>>> Estuve mirando tu sql detenidamente y encontre muchas inconcistencias a
>>>> mi criterio, las cuales pueden generar lentitud y que la información
>>>> arrojada por estas no sea la que esperas.
>>>>
>>>> Primero, en varias partes dentro de los where utilizas AND'S Y OR'S
>>>> pero sin paréntesis, no se si lo hiciste a drede o los olvidaste, te pego
>>>> una parte de la query:
>>>>
>>>>
>>>> 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 --- AQUI !!!!!
>>>> m1.hora < d.hora_salida
>>>> GROUP BY
>>>> (m1.fecha) LIMIT 1,
>>>>
>>>> Para mi un or entre tantos and sin parentesis que obligue a validar las
>>>> condiciones encerradas en ellos se me vuelve imposible predecir lo que esto
>>>> te va a arrojar, lo que si puedo asegurar es que eso se va a demorar mucho
>>>> dependiendo de la cantidad de registros que tengas.
>>>>
>>>> Por otro lado tienes un group by limitado a 1 registro, para mi no
>>>> tiene sentido limitarlo a un registro a menos que sepas que el primer
>>>> registro que va a salir es el que necesitas, pero no es en este caso, es
>>>> mas para mi sobra el group by, se puede utilizar la función de agregación
>>>> min sin el group by si lo que se va a retornar es un unico registro
>>>> resultado de todos los cruces que tienes en el where, no se si me podrias
>>>> explicar para que lo utilizas asi, ya que yo no logro encontrale algun
>>>> sentido.
>>>>
>>>> Espero me aclares estas dudas para poder seguir avanzando en ayudarte a
>>>> conseguir una query mas optimizada.
>>>>
>>>>
>>>> 2018-03-23 10:01 GMT-05:00 jvenegasperu . <jvenegasperu(at)gmail(dot)com>:
>>>>
>>>>> Ok Felipe
>>>>>
>>>>> Lineas abajo las estructuras de las tablas lo que se quiere obtener
>>>>> son las marcaciones de un empleado asi como el tiempo de tardanza y tiempo
>>>>> a compensar en funcion a su horario de trabajo.
>>>>> en la tabla trans estan las fechas y horas de su marcaciones cada
>>>>> empleado se distingue por el campo empid. en la tabla rrhh_persona_horario
>>>>> se encuentra el horario de cada empleado.
>>>>>
>>>>>
>>>>> -- ----------------------------
>>>>> -- Table structure for trans
>>>>> -- ----------------------------
>>>>> DROP TABLE IF EXISTS "public"."trans";
>>>>> CREATE TABLE "public"."trans" (
>>>>> "cid" text COLLATE "pg_catalog"."default",
>>>>> "gtno" text COLLATE "pg_catalog"."default",
>>>>> "empid" text COLLATE "pg_catalog"."default",
>>>>> "cardid" text COLLATE "pg_catalog"."default",
>>>>> "dt" timestamp(6),
>>>>> "inout" text COLLATE "pg_catalog"."default",
>>>>> "errdesc" text COLLATE "pg_catalog"."default",
>>>>> "status" text COLLATE "pg_catalog"."default",
>>>>> "tid" int8,
>>>>> "sitecode" text COLLATE "pg_catalog"."default",
>>>>> "deptid" text COLLATE "pg_catalog"."default",
>>>>> "type" text COLLATE "pg_catalog"."default",
>>>>> "updatedon" timestamp(6),
>>>>> "empname" text COLLATE "pg_catalog"."default",
>>>>> "location" text COLLATE "pg_catalog"."default",
>>>>> "newcardid" text COLLATE "pg_catalog"."default",
>>>>> "vflag" text COLLATE "pg_catalog"."default",
>>>>> "fecha" date,
>>>>> "hora" time(6),
>>>>> "dia_semana" varchar(9) COLLATE "pg_catalog"."default"
>>>>> )
>>>>> ;
>>>>>
>>>>> -- ----------------------------
>>>>> -- Indexes structure for table trans
>>>>> -- ----------------------------
>>>>> CREATE INDEX "trans_emp_fecha_hora" ON "public"."trans" USING btree (
>>>>> "empid" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC
>>>>> NULLS LAST,
>>>>> "fecha" "pg_catalog"."date_ops" ASC NULLS LAST,
>>>>> "hora" "pg_catalog"."time_ops" ASC NULLS LAST
>>>>> );
>>>>>
>>>>> -- ----------------------------
>>>>> -- Triggers structure for table trans
>>>>> -- ----------------------------
>>>>> CREATE TRIGGER "fecha" BEFORE INSERT OR UPDATE ON "public"."trans"
>>>>> FOR EACH ROW
>>>>> EXECUTE PROCEDURE "public"."fecha"();
>>>>> CREATE TRIGGER "hora" BEFORE INSERT OR UPDATE ON "public"."trans"
>>>>> FOR EACH ROW
>>>>> EXECUTE PROCEDURE "public"."hora"();
>>>>>
>>>>>
>>>>>
>>>>> -- ----------------------------
>>>>> -- Table structure for rrhh_persona_horario
>>>>> -- ----------------------------
>>>>> DROP TABLE IF EXISTS "public"."rrhh_persona_horario";
>>>>> CREATE TABLE "public"."rrhh_persona_horario" (
>>>>> "documento" varchar(15) COLLATE "pg_catalog"."default" NOT NULL,
>>>>> "hora_ingreso" time(6) NOT NULL,
>>>>> "hora_salida" time(6) NOT NULL,
>>>>> "turno_id" int2 NOT NULL,
>>>>> "fecha_ini" date NOT NULL,
>>>>> "fecha_fin" date,
>>>>> "fregistro" timestamp(6),
>>>>> "usuario" varchar(20) COLLATE "pg_catalog"."default",
>>>>> "activo" int2,
>>>>> "tolerancia" char(5) COLLATE "pg_catalog"."default",
>>>>> "sal_max_ma" char(5) COLLATE "pg_catalog"."default",
>>>>> "sal_max_ta" char(5) COLLATE "pg_catalog"."default",
>>>>> "grupo_id" int2 NOT NULL DEFAULT nextval('rrhh_persona_horario_
>>>>> grupo_id_seq'::regclass),
>>>>> "dia" varchar(9) COLLATE "pg_catalog"."default"
>>>>> )
>>>>> ;
>>>>>
>>>>> -- ----------------------------
>>>>> -- Primary Key structure for table rrhh_persona_horario
>>>>> -- ----------------------------
>>>>> ALTER TABLE "public"."rrhh_persona_horario" ADD CONSTRAINT
>>>>> "rrhh_persona_horario_pkey" PRIMARY KEY ("documento", "hora_ingreso",
>>>>> "hora_salida", "turno_id", "fecha_ini");
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 2018-03-23 9:48 GMT-05:00 Felipe Hernández <pipelx(at)gmail(dot)com>:
>>>>>
>>>>>> Claro que si, como te dije en el correo anterior, danos las
>>>>>> estructuras de las tablas y dinos que quieres consultar para ver de que
>>>>>> otra forma se la puede hacer.
>>>>>>
>>>>>> Cordialmente,
>>>>>>
>>>>>> 2018-03-23 9:40 GMT-05:00 jvenegasperu . <jvenegasperu(at)gmail(dot)com>:
>>>>>>
>>>>>>> Hola Felipe
>>>>>>>
>>>>>>> Buen dia tienes razon en efecto lo que dices explica la lentitud de
>>>>>>> las consultas creo que ese dia estuve fumando de la mala jajajaja es un
>>>>>>> decir en efecto estaba repitiendo el select miles de veces por cada
>>>>>>> resultado.
>>>>>>> Bueno un amigo fuera de la lista me sugirio usar with para este tipo
>>>>>>> de consulta asi que reescribi la consulta con with como lo muestro lineas
>>>>>>> abajo y en efecto obtengo el mismo resultado pero ahora la consulta tarda
>>>>>>> menos de un segundo. tambien pongo el explain gracias por responder, por
>>>>>>> favor si puedes sugerir algo para optimizar esto aun mas seria fabuloso.
>>>>>>>
>>>>>>> with queryAsis as (SELECT m.empid AS dni,
>>>>>>> m.empname AS nombre_empleado,
>>>>>>> '201801' 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, d.hora_ingreso,
>>>>>>> ( 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, d.hora_salida,
>>>>>>> ( 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,
>>>>>>> d1.hora_ingreso as hora_ingreso_t,
>>>>>>> ( 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,
>>>>>>> d1.hora_salida as hora_salida_t
>>>>>>> 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)
>>>>>>> and ( m.fecha >= d.fecha_ini and m.fecha <= d.fecha_fin )
>>>>>>> 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)
>>>>>>> and ( m.fecha >= d1.fecha_ini and m.fecha <= d1.fecha_fin )
>>>>>>> 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 >= '2017-12-26' and m.fecha <= '2018-01-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 )
>>>>>>>
>>>>>>> select dni,nombre_empleado,periodo,fecha,dia_semana,hor_ing_ma,
>>>>>>> CASE WHEN ((hor_ing_ma - hora_ingreso) >= '00:01:00'::interval) THEN
>>>>>>> hor_ing_ma - hora_ingreso ELSE NULL::interval END as
>>>>>>> tardanz_ma,
>>>>>>> hor_sal_ma,
>>>>>>> CASE WHEN ((hor_sal_ma - hora_salida) >= '00:01:00'::interval) THEN
>>>>>>> hor_sal_ma - hora_salida ELSE NULL::interval END as
>>>>>>> tiem_compensar_ma,
>>>>>>> hor_ing_ta,
>>>>>>> CASE WHEN ((hor_ing_ta - hora_ingreso_t) >= '00:01:00'::interval)
>>>>>>> THEN
>>>>>>> hor_ing_ta - hora_ingreso_t ELSE NULL::interval END as
>>>>>>> tardanz_ta,
>>>>>>> hor_sal_ta,
>>>>>>> CASE WHEN ((hor_sal_ta - hora_salida_t) >= '00:01:00'::interval) THEN
>>>>>>> hor_sal_ta - hora_salida_t ELSE NULL::interval END as
>>>>>>> tiem_compensar_ta
>>>>>>> from queryAsis m
>>>>>>>
>>>>>>>
>>>>>>> CTE Scan on queryasis m (cost=35555.60..35607.50 rows=1038 width=228) (actual time=18.296..51.338 rows=1230 loops=1)
>>>>>>> CTE queryasis
>>>>>>> -> Group (cost=865.64..35555.60 rows=1038 width=199) (actual time=18.289..49.063 rows=1230 loops=1)
>>>>>>> Group Key: m_1.fecha, m_1.empid, m_1.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=865.64..868.23 rows=1038 width=103) (actual time=18.104..18.461 rows=2965 loops=1)
>>>>>>> Sort Key: m_1.fecha, m_1.empid, m_1.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: 458kB
>>>>>>> -> Hash Left Join (cost=10.27..813.64 rows=1038 width=103) (actual time=1.269..7.609 rows=2965 loops=1)
>>>>>>> Hash Cond: (m_1.empid = (d1.documento)::text)
>>>>>>> Join Filter: ((m_1.fecha >= d1.fecha_ini) AND (m_1.fecha <= d1.fecha_fin))
>>>>>>> Rows Removed by Join Filter: 2206
>>>>>>> Filter: (((m_1.fecha >= d.fecha_ini) AND (m_1.fecha <= d.fecha_fin)) OR ((m_1.fecha >= d1.fecha_ini) AND (m_1.fecha <= d1.fecha_fin)))
>>>>>>> Rows Removed by Filter: 2541
>>>>>>> -> Hash Left Join (cost=5.19..642.38 rows=4945 width=65) (actual time=1.197..5.570 rows=5274 loops=1)
>>>>>>> Hash Cond: (m_1.empid = (d.documento)::text)
>>>>>>> Join Filter: ((m_1.fecha >= d.fecha_ini) AND (m_1.fecha <= d.fecha_fin))
>>>>>>> Rows Removed by Join Filter: 2168
>>>>>>> -> Seq Scan on trans m_1 (cost=0.00..502.69 rows=4945 width=27) (actual time=1.088..3.466 rows=4944 loops=1)
>>>>>>> Filter: ((fecha >= '2017-12-26'::date) AND (fecha <= '2018-01-25'::date))
>>>>>>> Rows Removed by Filter: 9169
>>>>>>> -> Hash (cost=4.31..4.31 rows=70 width=47) (actual time=0.090..0.090 rows=70 loops=1)
>>>>>>> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>>>>>>> -> Seq Scan on rrhh_persona_horario d (cost=0.00..4.31 rows=70 width=47) (actual time=0.026..0.059 rows=70 loops=1)
>>>>>>> Filter: ((activo = 1) AND ((turno_id = 1) OR (turno_id = 3)))
>>>>>>> Rows Removed by Filter: 62
>>>>>>> -> Hash (cost=4.31..4.31 rows=62 width=47) (actual time=0.058..0.058 rows=62 loops=1)
>>>>>>> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>>>>>>> -> Seq Scan on rrhh_persona_horario d1 (cost=0.00..4.31 rows=62 width=47) (actual time=0.007..0.034 rows=62 loops=1)
>>>>>>> Filter: ((activo = 1) AND ((turno_id = 2) OR (turno_id = 4)))
>>>>>>> Rows Removed by Filter: 70
>>>>>>> SubPlan 1
>>>>>>> -> Limit (cost=0.29..8.34 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1230)
>>>>>>> -> GroupAggregate (cost=0.29..8.34 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1230)
>>>>>>> Group Key: m1.fecha
>>>>>>> -> Result (cost=0.29..8.33 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=1230)
>>>>>>> One-Time Filter: ((d.turno_id = 1) OR (d.turno_id = 3))
>>>>>>> -> Index Only Scan using trans_emp_fecha_hora on trans m1 (cost=0.29..8.33 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1040)
>>>>>>> Index Cond: ((empid = m_1.empid) AND (fecha >= d.fecha_ini) AND (fecha <= d.fecha_fin) AND (fecha = m_1.fecha) AND (hora < d.hora_salida))
>>>>>>> Filter: (((hora - d.hora_ingreso) > '-00:59:00'::interval) AND ((hora - d.hora_ingreso) <= (d.hora_salida)::interval))
>>>>>>> Rows Removed by Filter: 0
>>>>>>> Heap Fetches: 1047
>>>>>>> SubPlan 2
>>>>>>> -> Limit (cost=0.29..8.34 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1230)
>>>>>>> -> GroupAggregate (cost=0.29..8.34 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1230)
>>>>>>> Group Key: m1_1.fecha
>>>>>>> -> Result (cost=0.29..8.33 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1230)
>>>>>>> One-Time Filter: ((d.turno_id = 1) OR (d.turno_id = 3))
>>>>>>> -> Index Only Scan using trans_emp_fecha_hora on trans m1_1 (cost=0.29..8.33 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=1040)
>>>>>>> Index Cond: ((empid = m_1.empid) AND (fecha >= d.fecha_ini) AND (fecha <= d.fecha_fin) AND (fecha = m_1.fecha))
>>>>>>> Filter: (((hora - d.hora_salida) > '-00:59:00'::interval) AND ((hora - d.hora_salida) <= (d.sal_max_ma)::interval))
>>>>>>> Rows Removed by Filter: 1
>>>>>>> Heap Fetches: 2574
>>>>>>> SubPlan 3
>>>>>>> -> Limit (cost=0.29..8.33 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1230)
>>>>>>> -> GroupAggregate (cost=0.29..8.33 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1230)
>>>>>>> Group Key: m1_2.fecha
>>>>>>> -> Result (cost=0.29..8.32 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=1230)
>>>>>>> One-Time Filter: ((d1.turno_id = 2) OR (d1.turno_id = 4))
>>>>>>> -> Index Only Scan using trans_emp_fecha_hora on trans m1_2 (cost=0.29..8.32 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=909)
>>>>>>> Index Cond: ((empid = m_1.empid) AND (fecha >= d1.fecha_ini) AND (fecha <= d1.fecha_fin) AND (fecha = m_1.fecha))
>>>>>>> Filter: (((hora - d1.hora_ingreso) > '-00:59:00'::interval) AND ((hora - d1.hora_salida) <= '00:00:00'::interval))
>>>>>>> Rows Removed by Filter: 2
>>>>>>> Heap Fetches: 2323
>>>>>>> SubPlan 4
>>>>>>> -> Limit (cost=0.29..8.34 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=1230)
>>>>>>> -> GroupAggregate (cost=0.29..8.34 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=1230)
>>>>>>> Group Key: m1_3.fecha
>>>>>>> -> Result (cost=0.29..8.33 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1230)
>>>>>>> One-Time Filter: ((d1.turno_id = 2) OR (d1.turno_id = 4))
>>>>>>> -> Index Only Scan using trans_emp_fecha_hora on trans m1_3 (cost=0.29..8.33 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=909)
>>>>>>> Index Cond: ((empid = m_1.empid) AND (fecha >= d1.fecha_ini) AND (fecha <= d1.fecha_fin) AND (fecha = m_1.fecha))
>>>>>>> Filter: (((hora - d1.hora_salida) > '-00:59:00'::interval) AND ((hora - d1.hora_salida) <= (d1.sal_max_ta)::interval))
>>>>>>> Rows Removed by Filter: 2
>>>>>>> Heap Fetches: 2323
>>>>>>> Planning time: 1.792 ms
>>>>>>> Execution time: 51.859 ms
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> 2018-03-22 20:34 GMT-05:00 Felipe Hernández <pipelx(at)gmail(dot)com>:
>>>>>>>
>>>>>>>> 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 . <jvenegasperu(at)gmail(dot)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 . <jvenegasperu(at)gmail(dot)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 <StephenAmell(at)inbox(dot)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
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> José Mercedes Venegas Acevedo
>>>>>>> cel Mov RPC 964185205
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> fElIpE
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> José Mercedes Venegas Acevedo
>>>>> cel Mov RPC 964185205
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> fElIpE
>>>>
>>>
>>>
>>>
>>> --
>>> fElIpE
>>>
>>
>>
>>
>> --
>> José Mercedes Venegas Acevedo
>> cel Mov RPC 964185205
>>
>>
>>
>
>
> --
> fElIpE
>

--
José Mercedes Venegas Acevedo
cel Mov RPC 964185205