explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PksF

Settings
# exclusive inclusive rows x rows loops node
1. 43,876.272 85,784.965 ↓ 6.8 999,856 1

Result (cost=2,081,220.59..2,414,828.86 rows=146,641 width=631) (actual time=41,462.673..85,784.965 rows=999,856 loops=1)

2. 3,192.445 41,908.693 ↓ 6.8 999,856 1

Sort (cost=2,081,220.59..2,081,587.19 rows=146,641 width=391) (actual time=41,462.579..41,908.693 rows=999,856 loops=1)

  • Sort Key: bh.user_id, bh.dt_calendario
  • Sort Method: external sort Disk: 213264kB
3. 743.073 38,716.248 ↓ 6.8 999,856 1

Subquery Scan on bh (cost=1,340,060.80..2,042,572.88 rows=146,641 width=391) (actual time=19,042.309..38,716.248 rows=999,856 loops=1)

  • Filter: (bh.tt_add_noturno IS NOT NULL)
4. 17,901.371 37,973.175 ↓ 6.8 999,856 1

GroupAggregate (cost=1,340,060.80..2,040,365.89 rows=147,378 width=380) (actual time=19,042.306..37,973.175 rows=999,856 loops=1)

  • Group Key: je.user_id, je.situacao_ponto, je.nm_colaborador, je.dt_calendario, (to_char((je.dt_calendario)::timestamp with time zone, 'DD/MM/YYYY'::text)), je.dt_aberto, je.gestor_id, je.nm_gestor, je.bu, je.cargo, je.matricula, je.hr_negativas, je.hr_positivas, je.hr_entrada1, je.hr_saida1, je.hr_entrada2, je.hr_saida2, je.qtidade, (base.fnc_convertehorachar(je.int_hr_total_trabalhado)), (base.fnc_convertehorachar(je.int_hr_total_extras50)), (base.fnc_convertehorachar(je.int_hr_total_extras100)), je.hr_hora_adicinal_noturno, je.is_falta, je.hr_jornada
5. 3,798.904 20,071.804 ↓ 1.0 1,008,976 1

Sort (cost=1,340,060.80..1,342,563.54 rows=1,001,098 width=372) (actual time=19,041.772..20,071.804 rows=1,008,976 loops=1)

  • Sort Key: je.user_id, je.situacao_ponto, je.nm_colaborador, je.dt_calendario, (to_char((je.dt_calendario)::timestamp with time zone, 'DD/MM/YYYY'::text)), je.dt_aberto, je.gestor_id, je.nm_gestor, je.bu, je.cargo, je.matricula, je.hr_negativas, je.hr_positivas, je.hr_entrada1, je.hr_saida1, je.hr_entrada2, je.hr_saida2, je.qtidade, (base.fnc_convertehorachar(je.int_hr_total_trabalhado)), (base.fnc_convertehorachar(je.int_hr_total_extras50)), (base.fnc_convertehorachar(je.int_hr_total_extras100)), je.hr_hora_adicinal_noturno, je.is_falta, je.hr_jornada
  • Sort Method: external merge Disk: 165784kB
6. 14,933.844 16,272.900 ↓ 1.0 1,008,976 1

Hash Left Join (cost=71,105.44..898,111.60 rows=1,001,098 width=372) (actual time=758.012..16,272.900 rows=1,008,976 loops=1)

  • Hash Cond: (je.user_id = tt.user_id)
7. 582.145 582.145 ↑ 1.0 999,856 1

Seq Scan on jornada_efetiva_trabalhada je (cost=0.00..67,420.92 rows=1,001,098 width=228) (actual time=0.006..582.145 rows=999,856 loops=1)

  • Filter: (NOT hr_extra)
  • Rows Removed by Filter: 473936
8. 0.030 756.911 ↓ 33.0 33 1

Hash (cost=71,105.42..71,105.42 rows=1 width=48) (actual time=756.911..756.911 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
9. 0.017 756.881 ↓ 33.0 33 1

Subquery Scan on tt (cost=71,105.41..71,105.42 rows=1 width=48) (actual time=756.859..756.881 rows=33 loops=1)

10. 0.049 756.864 ↓ 33.0 33 1

Sort (cost=71,105.41..71,105.41 rows=1 width=52) (actual time=756.857..756.864 rows=33 loops=1)

  • Sort Key: jet.user_id, jet.dt_calendario
  • Sort Method: quicksort Memory: 27kB
11. 756.815 756.815 ↓ 33.0 33 1

Seq Scan on jornada_efetiva_trabalhada jet (cost=0.00..71,105.40 rows=1 width=52) (actual time=690.007..756.815 rows=33 loops=1)

  • Filter: ((NOT hr_extra) AND (fechamento_id IS NOT NULL) AND (dt_calendario < dt_aberto))
  • Rows Removed by Filter: 1473759
Planning time : 2.922 ms