explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C4Rr

Settings
# exclusive inclusive rows x rows loops node
1. 260,258.641 260,258.641 ↓ 5.4 462 1

CTE Scan on trechos (cost=16,832,979.80..16,832,981.52 rows=86 width=32) (actual time=260,246.848..260,258.641 rows=462 loops=1)

  • Functions: 98
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 48.592 ms, Inlining 33.646 ms, Optimization 1574.870 ms, Emission 930.857 ms, Total 2587.964 ms
2.          

CTE dataset

3. 0.000 228.479 ↓ 13.9 67,422 1

Append (cost=14,633.32..22,277.37 rows=4,857 width=32) (actual time=82.253..228.479 rows=67,422 loops=1)

4.          

Initplan (for Append)

5. 0.002 0.102 ↑ 1.0 1 1

Subquery Scan on t (cost=48.81..48.83 rows=1 width=8) (actual time=0.102..0.102 rows=1 loops=1)

6.          

Initplan (for Subquery Scan)

7. 0.004 0.032 ↑ 1.0 1 1

Result (cost=0.71..0.72 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=1)

8.          

Initplan (for Result)

9. 0.005 0.028 ↑ 1.0 1 1

Limit (cost=0.57..0.71 rows=1 width=8) (actual time=0.026..0.028 rows=1 loops=1)

10. 0.001 0.023 ↑ 107,295.0 1 1

Merge Append (cost=0.57..15,105.17 rows=107,295 width=8) (actual time=0.023..0.023 rows=1 loops=1)

  • Sort Key: ddn_mapa_cliente4.dt_hr_utc_inicial
11. 0.022 0.022 ↑ 107,295.0 1 1

Index Only Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx on ddn_mapa_cliente4 (cost=0.56..14,032.21 rows=107,295 width=8) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 1475) AND (dt_hr_utc_inicial IS NOT NULL))
  • Heap Fetches: 0
12. 0.007 0.068 ↑ 1.0 1 1

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

13. 0.061 0.061 ↓ 0.0 0 1

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 time=0.061..0.061 rows=0 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_equipamento = 1475) AND (dt_fim < '2019-03-01 11:52:08'::timestamp without time zone))
  • Heap Fetches: 0
14. 0.005 82.109 ↑ 1.0 1 1

Subquery Scan on t_1 (cost=14,583.91..14,583.93 rows=1 width=8) (actual time=82.108..82.109 rows=1 loops=1)

15.          

Initplan (for Subquery Scan)

16. 7.969 82.074 ↑ 1.0 1 1

Aggregate (cost=14,568.69..14,568.70 rows=1 width=8) (actual time=82.074..82.074 rows=1 loops=1)

17. 10.762 74.105 ↓ 1.0 107,619 1

Append (cost=0.56..14,300.45 rows=107,295 width=8) (actual time=0.009..74.105 rows=107,619 loops=1)

18. 63.343 63.343 ↓ 1.0 107,619 1

Index Only Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx on ddn_mapa_cliente4 ddn_mapa_cliente4_1 (cost=0.56..13,763.98 rows=107,295 width=8) (actual time=0.009..63.343 rows=107,619 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 1475))
  • Heap Fetches: 15319
19. 0.003 0.030 ↑ 1.0 1 1

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

20. 0.027 0.027 ↓ 0.0 0 1

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 time=0.027..0.027 rows=0 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_equipamento = 1475) AND (dt_inicio > '2019-05-22 16:49:05'::timestamp without time zone))
  • Heap Fetches: 0
21. 221.609 221.609 ↓ 13.9 67,422 1

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx on ddn_mapa_cliente4 glot (cost=0.56..7,620.32 rows=4,857 width=32) (actual time=82.251..221.609 rows=67,422 loops=1)

  • 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))
  • Rows Removed by Filter: 40197
22.          

CTE lead_operacao_talhao

23. 57.752 349.014 ↓ 13.9 67,422 1

WindowAgg (cost=394.53..528.10 rows=4,857 width=40) (actual time=283.571..349.014 rows=67,422 loops=1)

24. 27.559 291.262 ↓ 13.9 67,422 1

Sort (cost=394.53..406.67 rows=4,857 width=32) (actual time=283.541..291.262 rows=67,422 loops=1)

  • Sort Key: d.cd_cliente, d.cd_id_equipamento, d.dt_hr_utc_inicial, d.dt_hr_utc_final
  • Sort Method: quicksort Memory: 8340kB
25. 263.703 263.703 ↓ 13.9 67,422 1

CTE Scan on dataset d (cost=0.00..97.14 rows=4,857 width=32) (actual time=82.257..263.703 rows=67,422 loops=1)

26.          

CTE last_and_lag

27. 0.314 376.041 ↑ 10.2 476 1

WindowAgg (cost=418.82..528.10 rows=4,857 width=32) (actual time=375.714..376.041 rows=476 loops=1)

28. 0.244 375.727 ↑ 10.2 476 1

Sort (cost=418.82..430.96 rows=4,857 width=24) (actual time=375.693..375.727 rows=476 loops=1)

  • Sort Key: lot.cd_cliente, lot.cd_id_equipamento, lot.dt_hr_utc_final
  • Sort Method: quicksort Memory: 62kB
29. 375.483 375.483 ↑ 10.2 476 1

CTE Scan on lead_operacao_talhao lot (cost=0.00..121.43 rows=4,857 width=24) (actual time=283.622..375.483 rows=476 loops=1)

  • Filter: ((cd_id_operacao <> ld_cd_operacao) OR (cd_id_talhao <> ld_cd_talhao))
  • Rows Removed by Filter: 66946
30.          

CTE trechos

31. 6.766 260,258.453 ↓ 5.4 462 1

GroupAggregate (cost=16,809,643.44..16,809,646.24 rows=86 width=48) (actual time=260,246.843..260,258.453 rows=462 loops=1)

  • 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. 44.038 260,251.687 ↓ 804.0 69,145 1

Sort (cost=16,809,643.44..16,809,643.66 rows=86 width=48) (actual time=260,246.820..260,251.687 rows=69,145 loops=1)

  • 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
  • Sort Method: quicksort Memory: 8474kB
33. 11,520.794 260,207.649 ↓ 804.0 69,145 1

Merge Join (cost=16,116,367.84..16,809,640.68 rows=86 width=48) (actual time=252,303.575..260,207.649 rows=69,145 loops=1)

  • 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))
  • Rows Removed by Join Filter: 51157499
34. 80,315.899 245,502.999 ↑ 1.1 57,291,044 1

Sort (cost=16,115,973.31..16,275,872.94 rows=63,959,854 width=32) (actual time=227,655.903..245,502.999 rows=57,291,044 loops=1)

  • Sort Key: glot_1.cd_cliente, glot_1.cd_id_equipamento
  • Sort Method: external merge Disk: 2626568kB
35. 7,343.549 165,187.100 ↑ 1.0 63,911,340 1

Append (cost=0.00..6,927,052.81 rows=63,959,854 width=32) (actual time=2,541.488..165,187.100 rows=63,911,340 loops=1)

36. 7,891.313 7,891.313 ↑ 1.0 5,912,724 1

Seq Scan on ddn_mapa_cliente1 glot_1 (cost=0.00..519,470.24 rows=5,912,724 width=32) (actual time=2,541.487..7,891.313 rows=5,912,724 loops=1)

37. 23,993.365 23,993.365 ↑ 1.0 15,327,088 1

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

38. 81,837.791 81,837.791 ↑ 1.0 35,618,862 1

Seq Scan on ddn_mapa_cliente3 glot_3 (cost=0.00..3,094,561.92 rows=35,620,492 width=32) (actual time=21.236..81,837.791 rows=35,618,862 loops=1)

39. 44,121.076 44,121.076 ↑ 1.0 7,052,666 1

Seq Scan on ddn_mapa_cliente4 glot_4 (cost=0.00..993,357.70 rows=7,099,370 width=32) (actual time=0.026..44,121.076 rows=7,052,666 loops=1)

40. 0.004 0.004 ↓ 0.0 0 1

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

41. 0.002 0.002 ↓ 0.0 0 1

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

42. 2,807.625 3,183.856 ↓ 10,524.8 51,119,026 1

Sort (cost=394.53..406.67 rows=4,857 width=32) (actual time=376.340..3,183.856 rows=51,119,026 loops=1)

  • Sort Key: lal.cd_cliente, lal.cd_id_equipamento
  • Sort Method: quicksort Memory: 62kB
43. 376.231 376.231 ↑ 10.2 476 1

CTE Scan on last_and_lag lal (cost=0.00..97.14 rows=4,857 width=32) (actual time=375.717..376.231 rows=476 loops=1)

Planning time : 43.537 ms