explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fS0Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on trechos (cost=18,086,911.49..18,086,913.57 rows=104 width=32) (actual rows= loops=)

  • Functions: 96
2.          

CTE dataset

3. 0.000 0.000 ↓ 0.0

Append (cost=69,905.61..78,772.97 rows=5,786 width=32) (actual rows= loops=)

4.          

Initplan (forAppend)

5. 0.000 0.000 ↓ 0.0

Subquery Scan on t (cost=49.23..49.25 rows=1 width=8) (actual rows= loops=)

6.          

Initplan (forSubquery Scan)

7. 0.000 0.000 ↓ 0.0

Result (cost=1.12..1.13 rows=1 width=8) (actual rows= loops=)

8.          

Initplan (forResult)

9. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..1.12 rows=1 width=8) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Merge Append (cost=0.57..70,519.26 rows=127,818 width=8) (actual rows= loops=)

  • Sort Key: ddn_mapa_cliente4.dt_hr_utc_inicial
11. 0.000 0.000 ↓ 0.0

Index Only Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx on ddn_mapa_cliente4 (cost=0.56..69,241.07 rows=127,818 width=8) (actual rows= loops=)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 1475) AND (dt_hr_utc_inicial IS NOT NULL))
12. 0.000 0.000 ↓ 0.0

Aggregate (cost=48.10..48.11 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Index Only Scan using trecho_operacao_talhao_cd_cliente_cd_equipamento_cd_operac_key1 on trecho_operacao_talhao (cost=0.42..47.92 rows=69 width=8) (actual rows= loops=)

  • Index Cond: ((cd_cliente = 4) AND (cd_equipamento = 1475) AND (dt_fim < '2019-03-01 11:52:08'::timestamp without time zone))
14. 0.000 0.000 ↓ 0.0

Subquery Scan on t_1 (cost=69,855.78..69,855.80 rows=1 width=8) (actual rows= loops=)

15.          

Initplan (forSubquery Scan)

16. 0.000 0.000 ↓ 0.0

Aggregate (cost=69,840.56..69,840.57 rows=1 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Append (cost=0.56..69,521.02 rows=127,818 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_cd_estado_cd_idx on ddn_mapa_cliente4 ddn_mapa_cliente4_1 (cost=0.56..68,881.93 rows=127,818 width=8) (actual rows= loops=)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 1475))
19. 0.000 0.000 ↓ 0.0

Aggregate (cost=15.21..15.22 rows=1 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Index Only Scan using trecho_operacao_talhao_cd_cliente_cd_equipamento_cd_operac_key1 on trecho_operacao_talhao trecho_operacao_talhao_1 (cost=0.42..15.18 rows=12 width=8) (actual rows= loops=)

  • Index Cond: ((cd_cliente = 4) AND (cd_equipamento = 1475) AND (dt_inicio > '2019-05-22 16:49:05'::timestamp without time zone))
21. 0.000 0.000 ↓ 0.0

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx on ddn_mapa_cliente4 glot (cost=0.56..8,838.99 rows=5,786 width=32) (actual rows= loops=)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 1475) AND (dt_hr_utc_inicial >= $2) AND (dt_hr_utc_final <= $4))
  • Filter: ((cd_id_talhao <> '-1'::integer) AND (cd_estado = 'E'::bpchar))
22.          

CTE lead_operacao_talhao

23. 0.000 0.000 ↓ 0.0

WindowAgg (cost=477.30..636.41 rows=5,786 width=40) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Sort (cost=477.30..491.76 rows=5,786 width=32) (actual rows= loops=)

  • Sort Key: d.cd_cliente, d.cd_id_equipamento, d.dt_hr_utc_inicial, d.dt_hr_utc_final
25. 0.000 0.000 ↓ 0.0

CTE Scan on dataset d (cost=0.00..115.72 rows=5,786 width=32) (actual rows= loops=)

26.          

CTE last_and_lag

27. 0.000 0.000 ↓ 0.0

WindowAgg (cost=506.23..636.41 rows=5,786 width=32) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Sort (cost=506.23..520.69 rows=5,786 width=24) (actual rows= loops=)

  • Sort Key: lot.cd_cliente, lot.cd_id_equipamento, lot.dt_hr_utc_final
29. 0.000 0.000 ↓ 0.0

CTE Scan on lead_operacao_talhao lot (cost=0.00..144.65 rows=5,786 width=24) (actual rows= loops=)

  • Filter: ((cd_id_operacao <> ld_cd_operacao) OR (cd_id_talhao <> ld_cd_talhao))
30.          

CTE trechos

31. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=18,006,862.32..18,006,865.70 rows=104 width=48) (actual rows= loops=)

  • Group Key: lal.cd_cliente, lal.cd_id_equipamento, lal.cd_id_operacao, lal.cd_id_talhao, lal.dt_fim_trecho_anterior, lal.dt_fim
32. 0.000 0.000 ↓ 0.0

Sort (cost=18,006,862.32..18,006,862.58 rows=104 width=48) (actual rows= loops=)

  • Sort Key: lal.cd_cliente, lal.cd_id_equipamento, lal.cd_id_operacao, lal.cd_id_talhao, lal.dt_fim_trecho_anterior, lal.dt_fim
33. 0.000 0.000 ↓ 0.0

Merge Join (cost=17,257,149.65..18,006,858.83 rows=104 width=48) (actual rows= loops=)

  • Merge Cond: ((glot_1.cd_cliente = lal.cd_cliente) AND (glot_1.cd_id_equipamento = lal.cd_id_equipamento))
  • Join Filter: (((glot_1.cd_id_operacao_cb = lal.cd_id_operacao) OR (glot_1.cd_id_operacao_cb IS NULL)) AND ((glot_1.cd_id_talhao = lal.cd_id_talhao) OR (glot_1.cd_id_talhao IS NULL)) AND (glot_1.dt_hr_utc_inicial >= lal.dt_fim_trecho_anterior) AND (glot_1.dt_hr_utc_final <= lal.dt_fim))
34. 0.000 0.000 ↓ 0.0

Sort (cost=17,256,672.35..17,419,966.78 rows=65,317,769 width=32) (actual rows= loops=)

  • Sort Key: glot_1.cd_cliente, glot_1.cd_id_equipamento
35. 0.000 0.000 ↓ 0.0

Append (cost=0.00..6,947,421.54 rows=65,317,769 width=32) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on ddn_mapa_cliente1 glot_1 (cost=0.00..519,470.24 rows=5,912,724 width=32) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on ddn_mapa_cliente2 glot_2 (cost=0.00..1,999,841.88 rows=15,327,088 width=32) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on ddn_mapa_cliente3 glot_3 (cost=0.00..3,094,561.92 rows=35,620,492 width=32) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on ddn_mapa_cliente4 glot_4 (cost=0.00..1,006,936.85 rows=8,457,285 width=32) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on ddn_mapa_cliente5 glot_5 (cost=0.00..10.90 rows=90 width=32) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on ddn_mapa_cliente6 glot_6 (cost=0.00..10.90 rows=90 width=32) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=477.30..491.76 rows=5,786 width=32) (actual rows= loops=)

  • Sort Key: lal.cd_cliente, lal.cd_id_equipamento
43. 0.000 0.000 ↓ 0.0

CTE Scan on last_and_lag lal (cost=0.00..115.72 rows=5,786 width=32) (actual rows= loops=)