explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZYFz

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

CTE Scan on trechos (cost=23,545.53..23,545.55 rows=1 width=32) (actual time=846.546..846.546 rows=0 loops=1)

2.          

CTE dataset

3. 0.000 494.877 ↓ 13.9 67,422 1

Append (cost=14,639.92..22,286.17 rows=4,857 width=32) (actual time=255.643..494.877 rows=67,422 loops=1)

4.          

Initplan (forAppend)

5. 0.001 0.069 ↑ 1.0 1 1

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

6.          

Initplan (forSubquery Scan)

7. 0.002 0.020 ↑ 1.0 1 1

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

8.          

Initplan (forResult)

9. 0.002 0.018 ↑ 1.0 1 1

Limit (cost=0.57..0.71 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=1)

10. 0.000 0.016 ↑ 107,295.0 1 1

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

  • Sort Key: ddn_mapa_cliente4.dt_hr_utc_inicial
11. 0.016 0.016 ↑ 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,038.81 rows=107,295 width=8) (actual time=0.016..0.016 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.002 0.048 ↑ 1.0 1 1

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

13. 0.046 0.046 ↓ 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.046..0.046 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.004 255.542 ↑ 1.0 1 1

Subquery Scan on t_1 (cost=14,590.51..14,590.53 rows=1 width=8) (actual time=255.541..255.542 rows=1 loops=1)

15.          

Initplan (forSubquery Scan)

16. 10.000 255.511 ↑ 1.0 1 1

Aggregate (cost=14,575.29..14,575.30 rows=1 width=8) (actual time=255.511..255.511 rows=1 loops=1)

17. 10.704 245.511 ↓ 1.0 107,619 1

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

18. 234.807 234.807 ↓ 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,770.58 rows=107,295 width=8) (actual time=0.009..234.807 rows=107,619 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 1475))
  • Heap Fetches: 17269
19. 0.001 0.027 ↑ 1.0 1 1

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

20. 0.026 0.026 ↓ 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.026..0.026 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. 476.046 476.046 ↓ 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,622.52 rows=4,857 width=32) (actual time=255.643..476.046 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. 155.568 796.263 ↓ 13.9 67,422 1

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

24. 61.108 640.695 ↓ 13.9 67,422 1

Sort (cost=394.53..406.67 rows=4,857 width=32) (actual time=633.790..640.695 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. 579.587 579.587 ↓ 13.9 67,422 1

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

26.          

CTE last_and_lag

27. 0.436 845.276 ↑ 10.2 476 1

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

28. 0.281 844.840 ↑ 10.2 476 1

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

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

CTE Scan on lead_operacao_talhao lot (cost=0.00..121.43 rows=4,857 width=24) (actual time=633.869..844.559 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. 0.002 846.545 ↓ 0.0 0 1

GroupAggregate (cost=203.13..203.17 rows=1 width=48) (actual time=846.545..846.545 rows=0 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. 0.004 846.543 ↓ 0.0 0 1

Sort (cost=203.13..203.14 rows=1 width=48) (actual time=846.543..846.543 rows=0 loops=1)

  • Sort Key: lal.cd_id_operacao, lal.cd_id_talhao, lal.dt_fim_trecho_anterior, lal.dt_fim
  • Sort Method: quicksort Memory: 25kB
33. 0.081 846.539 ↓ 0.0 0 1

Nested Loop (cost=10.70..203.12 rows=1 width=48) (actual time=846.539..846.539 rows=0 loops=1)

34. 845.506 845.506 ↓ 476.0 476 1

CTE Scan on last_and_lag lal (cost=0.00..121.43 rows=1 width=32) (actual time=844.800..845.506 rows=476 loops=1)

  • Filter: ((cd_cliente = 4) AND (cd_id_equipamento = 1475))
35. 0.476 0.952 ↓ 0.0 0 476

Append (cost=10.70..81.69 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=476)

36. 0.000 0.476 ↓ 0.0 0 476

Bitmap Heap Scan on ddn_mapa_cliente4 glot_1 (cost=10.70..81.68 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=476)

  • Recheck Cond: (((cd_cliente = 4) AND (cd_id_talhao = lal.cd_id_talhao) AND (cd_id_talhao = '-1'::integer) AND (cd_id_equipamento = 1475)) OR ((cd_id_talhao IS NULL) AND (cd_id_talhao = '-1'::integer)))
  • Filter: ((cd_cliente = 4) AND (cd_id_equipamento = 1475) AND ((cd_id_operacao_cb = lal.cd_id_operacao) OR (cd_id_operacao_cb IS NULL)) AND (dt_hr_utc_inicial >= lal.dt_fim_trecho_anterior) AND (dt_hr_utc_final <= lal.dt_fim))
37. 0.476 0.476 ↓ 0.0 0 476

BitmapOr (cost=10.70..10.70 rows=63 width=0) (actual time=0.001..0.001 rows=0 loops=476)

38. 0.000 0.000 ↓ 0.0 0 476

Bitmap Index Scan on ddn_mapa_cliente4_cluster_idx (cost=0.00..2.60 rows=63 width=0) (actual time=0.000..0.000 rows=0 loops=476)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_talhao = lal.cd_id_talhao) AND (cd_id_talhao = '-1'::integer) AND (cd_id_equipamento = 1475))
39. 0.000 0.000 ↓ 0.0 0 476

Bitmap Index Scan on ddn_mapa_cliente4_cd_id_talhao_idx (cost=0.00..1.54 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=476)

  • Index Cond: ((cd_id_talhao IS NULL) AND (cd_id_talhao = '-1'::integer))
Planning time : 9.851 ms