explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M4SO : ponto

Settings
# exclusive inclusive rows x rows loops node
1. 3,148.279 67,882.324 ↓ 1.4 355,372 1

Sort (cost=29,241,322.17..29,241,974.69 rows=261,006 width=68) (actual time=67,537.711..67,882.324 rows=355,372 loops=1)

  • Sort Key: (NULL::bigint), (NULL::bigint), br.batida_4, br.funcionario_horario_fk
  • Sort Method: external merge Disk: 35,408kB
2. 6,162.170 64,734.045 ↓ 1.4 355,372 1

WindowAgg (cost=670,308.57..29,217,839.82 rows=261,006 width=68) (actual time=12,727.710..64,734.045 rows=355,372 loops=1)

3. 1,131.095 13,219.323 ↓ 1.4 355,372 1

HashAggregate (cost=670,308.57..672,918.63 rows=261,006 width=51) (actual time=12,662.865..13,219.323 rows=355,372 loops=1)

  • Group Key: br.batida_rel_pk, (NULL::bigint), br.data, br.batida_1, br.batida_2, br.batida_3, br.batida_4, br.funcionario_horario_fk, (NULL::bigint)
4. 553.214 12,088.228 ↓ 1.4 377,238 1

Append (cost=4,955.59..664,435.94 rows=261,006 width=51) (actual time=240.085..12,088.228 rows=377,238 loops=1)

5. 450.452 688.274 ↓ 1.6 307,823 1

Bitmap Heap Scan on t_batida_rel br (cost=4,955.59..272,173.97 rows=192,292 width=52) (actual time=240.080..688.274 rows=307,823 loops=1)

  • Recheck Cond: ((data >= (date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone))::date) AND (data <= (((date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::date))
  • Heap Blocks: exact=5,875
6. 237.822 237.822 ↓ 1.9 367,109 1

Bitmap Index Scan on xak_batida_rel (cost=0.00..4,907.52 rows=192,292 width=0) (actual time=237.822..237.822 rows=367,109 loops=1)

  • Index Cond: ((data >= (date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone))::date) AND (data <= (((date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::date))
7. 2,847.141 6,613.783 ↓ 1.0 34,705 1

Hash Join (cost=103,608.54..194,829.86 rows=34,516 width=48) (actual time=1,749.364..6,613.783 rows=34,705 loops=1)

  • Hash Cond: (bre.escala_item_fk = ei.escala_item_pk)
8. 2,017.421 2,017.421 ↑ 1.0 2,469,079 1

Seq Scan on t_batida_rel_escala bre (cost=0.00..56,791.84 rows=2,472,584 width=32) (actual time=0.047..2,017.421 rows=2,469,079 loops=1)

9. 65.663 1,749.221 ↓ 1.0 36,360 1

Hash (cost=103,162.14..103,162.14 rows=35,712 width=24) (actual time=1,749.221..1,749.221 rows=36,360 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,989kB
10. 149.201 1,683.558 ↓ 1.0 36,360 1

Bitmap Heap Scan on t_escala_item ei (cost=76,312.21..103,162.14 rows=35,712 width=24) (actual time=1,576.007..1,683.558 rows=36,360 loops=1)

  • Recheck Cond: ((entrada >= (date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone))::timestamp without time zone) AND (entrada <= (((date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=614
11. 1,534.357 1,534.357 ↓ 1.0 36,360 1

Bitmap Index Scan on xak01_escala_item (cost=0.00..76,303.29 rows=35,712 width=0) (actual time=1,534.357..1,534.357 rows=36,360 loops=1)

  • Index Cond: ((entrada >= (date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone))::timestamp without time zone) AND (entrada <= (((date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::timestamp without time zone))
12. 2,387.595 4,232.957 ↓ 1.0 34,710 1

Hash Join (cost=103,604.70..194,822.05 rows=34,198 width=48) (actual time=149.352..4,232.957 rows=34,710 loops=1)

  • Hash Cond: (bre_1.escala_item_fk = ei_1.escala_item_pk)
13. 1,696.116 1,696.116 ↑ 1.0 2,469,079 1

Seq Scan on t_batida_rel_escala bre_1 (cost=0.00..56,791.84 rows=2,472,584 width=32) (actual time=0.005..1,696.116 rows=2,469,079 loops=1)

14. 25.954 149.246 ↓ 1.0 36,306 1

Hash (cost=103,162.42..103,162.42 rows=35,383 width=24) (actual time=149.246..149.246 rows=36,306 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,986kB
15. 68.125 123.292 ↓ 1.0 36,306 1

Bitmap Heap Scan on t_escala_item ei_1 (cost=76,312.13..103,162.42 rows=35,383 width=24) (actual time=63.784..123.292 rows=36,306 loops=1)

  • Recheck Cond: ((saida >= (date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone))::timestamp without time zone) AND (saida <= (((date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=780
16. 55.167 55.167 ↓ 1.0 36,306 1

Bitmap Index Scan on xak01_escala_item (cost=0.00..76,303.29 rows=35,383 width=0) (actual time=55.167..55.167 rows=36,306 loops=1)

  • Index Cond: ((saida >= (date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone))::timestamp without time zone) AND (saida <= (((date_trunc('month'::text, ((('now'::cstring)::date - 10))::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval))::timestamp without time zone))
17.          

SubPlan (for WindowAgg)

18. 1,539.115 9,850.336 ↑ 1.0 1 307,823

Subquery Scan on hor (cost=0.72..16.86 rows=1 width=32) (actual time=0.030..0.032 rows=1 loops=307,823)

  • Filter: (hor.entrada_1 IS NOT NULL)
  • Rows Removed by Filter: 0
19. 0.000 8,311.221 ↑ 1.0 1 307,823

Limit (cost=0.72..16.85 rows=1 width=8) (actual time=0.026..0.027 rows=1 loops=307,823)

20.          

Initplan (for Limit)

21. 1,529.820 1,529.820 ↑ 1.0 1 305,964

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=305,964)

22. 4,318.817 7,387.752 ↑ 1.0 1 307,823

Nested Loop (cost=0.70..16.83 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=307,823)

23. 1,539.115 1,539.115 ↑ 1.0 1 307,823

Index Scan using xpk_funcionario_horario on t_funcionario_horario f (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=307,823)

  • Index Cond: (funcionario_horario_pk = br.funcionario_horario_fk)
  • Filter: (horario_fk <> 49)
  • Rows Removed by Filter: 0
24. 1,529.820 1,529.820 ↑ 1.0 1 305,964

Index Only Scan using xak01_horario_item on t_horario_item it (cost=0.41..8.49 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=305,964)

  • Index Cond: ((horario_fk = f.horario_fk) AND (dia_semana = $1))
  • Heap Fetches: 305,964
25. 190.196 760.784 ↑ 1.0 1 47,549

HashAggregate (cost=8.45..8.46 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=47,549)

  • Group Key: t_escala_item.entrada
26. 570.588 570.588 ↑ 1.0 1 47,549

Index Scan using xpk_escala_item on t_escala_item (cost=0.43..8.45 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=47,549)

  • Index Cond: (escala_item_pk = (NULL::bigint))
27. 1,231.292 8,619.044 ↑ 1.0 1 307,823

Subquery Scan on hor_1 (cost=0.72..16.86 rows=1 width=32) (actual time=0.026..0.028 rows=1 loops=307,823)

28. 0.000 7,387.752 ↑ 1.0 1 307,823

Limit (cost=0.72..16.85 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=307,823)

29.          

Initplan (for Limit)

30. 1,223.856 1,223.856 ↑ 1.0 1 305,964

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=305,964)

31. 4,009.135 6,464.283 ↑ 1.0 1 307,823

Nested Loop (cost=0.70..16.83 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=307,823)

32. 1,231.292 1,231.292 ↑ 1.0 1 307,823

Index Scan using xpk_funcionario_horario on t_funcionario_horario f_1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=307,823)

  • Index Cond: (funcionario_horario_pk = br.funcionario_horario_fk)
  • Filter: (horario_fk <> 49)
  • Rows Removed by Filter: 0
33. 1,223.856 1,223.856 ↑ 1.0 1 305,964

Index Only Scan using xak01_horario_item on t_horario_item it_1 (cost=0.41..8.49 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=305,964)

  • Index Cond: ((horario_fk = f_1.horario_fk) AND (dia_semana = $6))
  • Heap Fetches: 305,964
34. 190.196 380.392 ↑ 1.0 1 47,549

HashAggregate (cost=8.45..8.46 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=47,549)

  • Group Key: t_escala_item_1.saida
35. 190.196 190.196 ↑ 1.0 1 47,549

Index Scan using xpk_escala_item on t_escala_item t_escala_item_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=47,549)

  • Index Cond: (escala_item_pk = (NULL::bigint))
36. 1,231.292 8,311.221 ↑ 1.0 1 307,823

Subquery Scan on hor_2 (cost=0.72..16.86 rows=1 width=32) (actual time=0.025..0.027 rows=1 loops=307,823)

37. 5.577 7,079.929 ↑ 1.0 1 307,823

Limit (cost=0.72..16.85 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=307,823)

38.          

Initplan (for Limit)

39. 917.892 917.892 ↑ 1.0 1 305,964

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=305,964)

40. 4,009.135 6,156.460 ↑ 1.0 1 307,823

Nested Loop (cost=0.70..16.83 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=307,823)

41. 923.469 923.469 ↑ 1.0 1 307,823

Index Scan using xpk_funcionario_horario on t_funcionario_horario f_2 (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=307,823)

  • Index Cond: (funcionario_horario_pk = br.funcionario_horario_fk)
  • Filter: (horario_fk <> 49)
  • Rows Removed by Filter: 0
42. 1,223.856 1,223.856 ↑ 1.0 1 305,964

Index Only Scan using xak01_horario_item on t_horario_item it_2 (cost=0.41..8.49 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=305,964)

  • Index Cond: ((horario_fk = f_2.horario_fk) AND (dia_semana = $11))
  • Heap Fetches: 305,964
43. 1,231.292 11,389.451 ↑ 1.0 1 307,823

Subquery Scan on hor_3 (cost=16.85..16.89 rows=1 width=32) (actual time=0.035..0.037 rows=1 loops=307,823)

44. 315.259 10,158.159 ↑ 1.0 1 307,823

Limit (cost=16.85..16.88 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=307,823)

45.          

Initplan (for Limit)

46. 1,223.856 1,223.856 ↑ 1.0 1 305,964

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=305,964)

47. 1,231.292 8,619.044 ↑ 1.0 1 307,823

HashAggregate (cost=16.83..16.86 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=307,823)

  • Group Key: CASE WHEN (to_char(it_3.saida_2, 'hh24:mi:ss'::text) = '00:00:00'::text) THEN NULL::text ELSE (to_char((br.data)::timestamp with time zone, 'dd/mm/yyyy '::text) || to_char(it_3.saida_2, 'hh24:mi:ss'::text)) END
48. 4,318.817 7,387.752 ↑ 1.0 1 307,823

Nested Loop (cost=0.70..16.83 rows=1 width=8) (actual time=0.020..0.024 rows=1 loops=307,823)

49. 1,539.115 1,539.115 ↑ 1.0 1 307,823

Index Scan using xpk_funcionario_horario on t_funcionario_horario f_3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=307,823)

  • Index Cond: (funcionario_horario_pk = br.funcionario_horario_fk)
  • Filter: (horario_fk <> 49)
  • Rows Removed by Filter: 0
50. 1,529.820 1,529.820 ↑ 1.0 1 305,964

Index Only Scan using xak01_horario_item on t_horario_item it_3 (cost=0.41..8.49 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=305,964)

  • Index Cond: ((horario_fk = f_3.horario_fk) AND (dia_semana = $15))
  • Heap Fetches: 305,964
51. 1,776.860 4,975.208 ↑ 1.0 1 355,372

Nested Loop (cost=0.58..16.63 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=355,372)

52. 1,421.488 1,421.488 ↑ 1.0 1 355,372

Index Scan using xpk_funcionario_horario on t_funcionario_horario fh (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=355,372)

  • Index Cond: (funcionario_horario_pk = br.funcionario_horario_fk)
53. 1,776.860 1,776.860 ↑ 1.0 1 355,372

Index Scan using xpk_funcionario_contrato on t_funcionario_contrato fc (cost=0.29..8.31 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=355,372)

  • Index Cond: (funcionario_contrato_pk = fh.funcionario_contrato_fk)
54. 1,066.116 1,066.116 ↑ 1.0 1 355,372

Index Scan using xpk_funcionario_horario on t_funcionario_horario (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=355,372)

  • Index Cond: (funcionario_horario_pk = br.funcionario_horario_fk)
Planning time : 278.023 ms
Execution time : 68,082.412 ms