Re: Optimizar consulta a tabla con fechas y horas

hola de nuevo,

Hice los ajustes del caso con la información que me diste en correo
anterior y la query me quedo así, creo que falta algún detalle porque me
dan 204 registros menos que a ti, pero con los dni que me enviaste me da la
misma información, por favor revisarla y me comentas.

SELECT
COALESCE(em.dni,et.dni) AS dni,
'201802' AS periodo,
COALESCE(em.fecha,et.fecha) AS 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,
m.fecha,
m.hora,
d.hora_ingreso,
d.turno_id
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.fecha BETWEEN d.fecha_ini AND d.fecha_fin 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,
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.fecha BETWEEN d.fecha_ini AND d.fecha_fin 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
FULL OUTER JOIN
-- ENTRA TARDE
(SELECT DISTINCT ON(m.empid,m.fecha)
m.empid AS dni,
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.fecha BETWEEN d.fecha_ini AND d.fecha_fin AND
(d.turno_id=2 OR d.turno_id=4)
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,
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.fecha BETWEEN d.fecha_ini AND d.fecha_fin AND
(d.turno_id=2 OR d.turno_id=4)
ORDER BY
m.empid,
m.fecha DESC,
m.hora DESC) AS st
ON
et.dni=st.dni AND
et.fecha=st.fecha
ORDER BY
dni,
fecha

El 25 de marzo de 2018, 17:47, jvenegasperu . <jvenegasperu(at)gmail(dot)com>
escribió:

> 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 <(1)%205683298> 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 <(1)%205683298>
> 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 <(1)%208201898> 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 <(1)%207936496> 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 <(1)%205683298>'
> -- and m.empid = '18201898 <(1)%208201898>'
> -- and empid = '17936496 <(1)%207936496>'
> 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 <(1)%205683298>' 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 <(1)%205683298>' 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 <(1)%205683298>' 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 <(1)%205683298>' 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
>
>
>

--
fElIpE