explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q8iu

Settings
# exclusive inclusive rows x rows loops node
1. 18,339.551 18,339.551 ↓ 51.3 462 1

CTE Scan on trechos (cost=80,303.57..80,303.75 rows=9 width=32) (actual time=18,289.958..18,339.551 rows=462 loops=1)

2.          

CTE dataset

3. 0.000 6,712.047 ↓ 13.9 67,422 1

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

4.          

Initplan (forAppend)

5. 0.002 10.965 ↑ 1.0 1 1

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

6.          

Initplan (forSubquery Scan)

7. 0.004 10.914 ↑ 1.0 1 1

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

8.          

Initplan (forResult)

9. 0.003 10.910 ↑ 1.0 1 1

Limit (cost=0.57..0.71 rows=1 width=8) (actual time=10.908..10.910 rows=1 loops=1)

10. 0.002 10.907 ↑ 107,295.0 1 1

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

  • Sort Key: ddn_mapa_cliente4.dt_hr_utc_inicial
11. 10.905 10.905 ↑ 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=10.905..10.905 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.049 ↑ 1.0 1 1

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

13. 0.047 0.047 ↓ 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.047..0.047 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.006 378.127 ↑ 1.0 1 1

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

15.          

Initplan (forSubquery Scan)

16. 10.268 378.070 ↑ 1.0 1 1

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

17. 10.731 367.802 ↓ 1.0 107,619 1

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

18. 357.071 357.071 ↓ 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.015..357.071 rows=107,619 loops=1)

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

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

20. 0.048 0.048 ↓ 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.048..0.048 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. 6,703.820 6,703.820 ↓ 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=389.150..6,703.820 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. 169.743 6,957.035 ↓ 13.9 67,422 1

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

24. 29.985 6,787.292 ↓ 13.9 67,422 1

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

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

26.          

CTE last_and_lag

27. 4.064 7,066.105 ↑ 10.2 476 1

WindowAgg (cost=418.82..528.10 rows=4,857 width=32) (actual time=7,061.545..7,066.105 rows=476 loops=1)

28. 0.741 7,062.041 ↑ 10.2 476 1

Sort (cost=418.82..430.96 rows=4,857 width=24) (actual time=7,061.537..7,062.041 rows=476 loops=1)

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

CTE Scan on lead_operacao_talhao lot (cost=0.00..121.43 rows=4,857 width=24) (actual time=6,780.881..7,061.300 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. 31.146 18,339.202 ↓ 51.3 462 1

GroupAggregate (cost=56,960.91..56,961.21 rows=9 width=48) (actual time=18,289.953..18,339.202 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. 111.003 18,308.056 ↓ 7,682.8 69,145 1

Sort (cost=56,960.91..56,960.94 rows=9 width=48) (actual time=18,289.935..18,308.056 rows=69,145 loops=1)

  • Sort Key: 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. 27.223 18,197.053 ↓ 7,682.8 69,145 1

Nested Loop (cost=2,355.30..56,960.77 rows=9 width=48) (actual time=7,115.202..18,197.053 rows=69,145 loops=1)

34. 7,075.222 7,075.222 ↓ 19.8 476 1

CTE Scan on last_and_lag lal (cost=0.00..109.28 rows=24 width=32) (actual time=7,061.547..7,075.222 rows=476 loops=1)

  • Filter: (cd_cliente = 4)
35. 14.756 11,094.608 ↓ 145.0 145 476

Append (cost=2,355.30..2,368.80 rows=1 width=32) (actual time=22.656..23.308 rows=145 loops=476)

36. 336.532 11,079.852 ↓ 145.0 145 476

Bitmap Heap Scan on ddn_mapa_cliente4 glot_1 (cost=2,355.30..2,368.80 rows=1 width=32) (actual time=22.650..23.277 rows=145 loops=476)

  • Recheck Cond: ((cd_cliente = 4) AND (cd_id_equipamento = lal.cd_id_equipamento) AND (dt_hr_utc_inicial >= lal.dt_fim_trecho_anterior) AND (dt_hr_utc_final <= lal.dt_fim) AND ((cd_id_talhao = lal.cd_id_talhao) OR (cd_id_talhao IS NULL)))
  • Filter: ((cd_id_operacao_cb = lal.cd_id_operacao) OR (cd_id_operacao_cb IS NULL))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=66067
37. 12.158 10,743.320 ↓ 0.0 0 476

BitmapAnd (cost=2,355.30..2,355.30 rows=12 width=0) (actual time=22.570..22.570 rows=0 loops=476)

38. 3,909.388 3,909.388 ↑ 24.2 208 476

Bitmap Index Scan on ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx (cost=0.00..365.02 rows=5,024 width=0) (actual time=8.213..8.213 rows=208 loops=476)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = lal.cd_id_equipamento) AND (dt_hr_utc_inicial >= lal.dt_fim_trecho_anterior) AND (dt_hr_utc_final <= lal.dt_fim))
39. 1.398 6,821.774 ↓ 0.0 0 466

BitmapOr (cost=1,990.03..1,990.03 rows=17,025 width=0) (actual time=14.639..14.639 rows=0 loops=466)

40. 6,814.784 6,814.784 ↓ 1.9 33,101 466

Bitmap Index Scan on ddn_mapa_cliente4_cd_id_talhao_idx (cost=0.00..213.65 rows=17,025 width=0) (actual time=14.624..14.624 rows=33,101 loops=466)

  • Index Cond: (cd_id_talhao = lal.cd_id_talhao)
41. 5.592 5.592 ↓ 0.0 0 466

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

  • Index Cond: (cd_id_talhao IS NULL)
Planning time : 9.041 ms