explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yILi

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 22,805.090 ↓ 0.0 0 1

Result (cost=872,721.85..872,727.87 rows=2 width=110) (actual time=22,805.089..22,805.090 rows=0 loops=1)

  • Buffers: shared hit=413569 read=64771
  • Functions: 191
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 63.284 ms, Inlining 391.709 ms, Optimization 1832.812 ms, Emission 967.106 ms, Total 3254.910 ms
2.          

CTE dataset

3. 0.000 17,397.747 ↓ 19.4 199,461 1

Append (cost=161,371.98..231,890.84 rows=10,266 width=46) (actual time=1,627.167..17,397.747 rows=199,461 loops=1)

  • Buffers: shared hit=335385 read=63222
4.          

Initplan (forAppend)

5. 0.006 3.598 ↑ 1.0 1 1

Subquery Scan on t (cost=41.56..41.58 rows=1 width=8) (actual time=3.595..3.598 rows=1 loops=1)

  • Buffers: shared hit=3 read=20
6.          

Initplan (forSubquery Scan)

7. 0.005 0.751 ↑ 1.0 1 1

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

  • Buffers: shared hit=2 read=4
8.          

Initplan (forResult)

9. 0.007 0.746 ↑ 1.0 1 1

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

  • Buffers: shared hit=2 read=4
10. 0.007 0.739 ↑ 303,858.0 1 1

Merge Append (cost=0.57..165,796.39 rows=303,858 width=8) (actual time=0.738..0.739 rows=1 loops=1)

  • Sort Key: ddn_mapa_cliente4.dt_hr_utc_inicial
  • Buffers: shared hit=2 read=4
11. 0.732 0.732 ↑ 303,858.0 1 1

Index Only Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_i_idx1 on ddn_mapa_cliente4 (cost=0.56..162,757.80 rows=303,858 width=8) (actual time=0.731..0.732 rows=1 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504) AND (dt_hr_utc_inicial IS NOT NULL))
  • Heap Fetches: 1
  • Buffers: shared hit=2 read=4
12. 0.008 2.841 ↑ 1.0 1 1

Aggregate (cost=40.43..40.44 rows=1 width=8) (actual time=2.840..2.841 rows=1 loops=1)

  • Buffers: shared hit=1 read=16
13. 0.004 2.833 ↓ 0.0 0 1

Bitmap Heap Scan on trecho_operacao_talhao (cost=4.78..40.43 rows=1 width=8) (actual time=2.832..2.833 rows=0 loops=1)

  • Recheck Cond: ((cd_cliente = 4) AND (cd_equipamento = 103504) AND (dt_fim < '2019-01-02 11:51:02'::timestamp without time zone))
  • Filter: (fg_status = 'A'::bpchar)
  • Buffers: shared hit=1 read=16
14. 2.829 2.829 ↓ 0.0 0 1

Bitmap Index Scan on trecho_operacao_talhao_un (cost=0.00..4.78 rows=9 width=0) (actual time=2.829..2.829 rows=0 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_equipamento = 103504) AND (dt_fim < '2019-01-02 11:51:02'::timestamp without time zone))
  • Buffers: shared hit=1 read=16
15. 0.008 1,623.140 ↑ 1.0 1 1

Subquery Scan on t_1 (cost=161,329.82..161,329.84 rows=1 width=8) (actual time=1,623.137..1,623.140 rows=1 loops=1)

  • Buffers: shared hit=127085 read=28796
16.          

Initplan (forSubquery Scan)

17. 0.000 1,623.078 ↑ 1.0 1 1

Finalize Aggregate (cost=161,289.38..161,289.39 rows=1 width=8) (actual time=1,623.077..1,623.078 rows=1 loops=1)

  • Buffers: shared hit=127068 read=28796
18. 39.555 1,623.198 ↓ 1.2 5 1

Gather (cost=161,288.95..161,289.36 rows=4 width=8) (actual time=1,620.080..1,623.198 rows=5 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=127068 read=28796
19. 44.483 1,583.643 ↑ 1.0 1 5

Partial Aggregate (cost=160,288.95..160,288.96 rows=1 width=8) (actual time=1,583.641..1,583.643 rows=1 loops=5)

  • Buffers: shared hit=127068 read=28796
20. 83.450 1,539.160 ↑ 1.3 59,969 5

Parallel Append (cost=0.56..160,099.04 rows=75,964 width=8) (actual time=176.479..1,539.160 rows=59,969 loops=5)

  • Buffers: shared hit=127068 read=28796
21. 1,455.710 1,455.710 ↑ 1.3 59,969 5

Parallel Index Only Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_i_idx1 on ddn_mapa_cliente4 ddn_mapa_cliente4_1 (cost=0.56..159,719.22 rows=75,964 width=8) (actual time=176.476..1,455.710 rows=59,969 loops=5)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504))
  • Heap Fetches: 64773
  • Buffers: shared hit=127068 read=28796
22. 0.004 0.054 ↑ 1.0 1 1

Aggregate (cost=40.43..40.44 rows=1 width=8) (actual time=0.053..0.054 rows=1 loops=1)

  • Buffers: shared hit=17
23. 0.001 0.050 ↓ 0.0 0 1

Bitmap Heap Scan on trecho_operacao_talhao trecho_operacao_talhao_1 (cost=4.78..40.43 rows=1 width=8) (actual time=0.050..0.050 rows=0 loops=1)

  • Recheck Cond: ((cd_cliente = 4) AND (cd_equipamento = 103504) AND (dt_inicio > '2019-09-07 15:06:38'::timestamp without time zone))
  • Filter: (fg_status = 'A'::bpchar)
  • Buffers: shared hit=17
24. 0.049 0.049 ↓ 0.0 0 1

Bitmap Index Scan on trecho_operacao_talhao_un (cost=0.00..4.78 rows=9 width=0) (actual time=0.048..0.049 rows=0 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_equipamento = 103504) AND (dt_inicio > '2019-09-07 15:06:38'::timestamp without time zone))
  • Buffers: shared hit=17
25. 16,951.439 16,951.439 ↓ 19.4 199,461 1

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_i_idx1 on ddn_mapa_cliente4 dm (cost=0.56..70,468.08 rows=10,266 width=46) (actual time=1,627.165..16,951.439 rows=199,461 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504) AND (dt_hr_utc_inicial >= $2) AND (dt_hr_utc_final <= $5))
  • Filter: ((cd_id_talhao <> '-1'::integer) AND (cd_estado = 'E'::bpchar))
  • Rows Removed by Filter: 100384
  • Buffers: shared hit=335385 read=63222
26.          

CTE lead_operacao_talhao

27. 464.708 19,025.438 ↓ 19.4 199,461 1

WindowAgg (cost=889.32..1,248.63 rows=10,266 width=68) (actual time=18,450.088..19,025.438 rows=199,461 loops=1)

  • Buffers: shared hit=335385 read=63222
28. 493.070 18,560.730 ↓ 19.4 199,461 1

Sort (cost=889.32..914.99 rows=10,266 width=46) (actual time=18,450.003..18,560.730 rows=199,461 loops=1)

  • Sort Key: d.cd_cliente, d.cd_id_equipamento, d.dt_hr_utc_inicial
  • Sort Method: quicksort Memory: 21727kB
  • Buffers: shared hit=335385 read=63222
29. 18,067.660 18,067.660 ↓ 19.4 199,461 1

CTE Scan on dataset d (cost=0.00..205.32 rows=10,266 width=46) (actual time=1,627.176..18,067.660 rows=199,461 loops=1)

  • Buffers: shared hit=335385 read=63222
30.          

CTE last_and_lag

31. 1.737 19,247.137 ↑ 8.2 1,251 1

WindowAgg (cost=1,043.31..1,274.30 rows=10,266 width=46) (actual time=19,244.829..19,247.137 rows=1,251 loops=1)

  • Buffers: shared hit=335385 read=63222
32. 1.599 19,245.400 ↑ 8.2 1,251 1

Sort (cost=1,043.31..1,068.98 rows=10,266 width=38) (actual time=19,244.803..19,245.400 rows=1,251 loops=1)

  • Sort Key: lot.cd_cliente, lot.cd_id_equipamento, lot.dt_hr_utc_final
  • Sort Method: quicksort Memory: 146kB
  • Buffers: shared hit=335385 read=63222
33. 19,243.801 19,243.801 ↑ 8.2 1,251 1

CTE Scan on lead_operacao_talhao lot (cost=0.00..359.31 rows=10,266 width=38) (actual time=18,450.375..19,243.801 rows=1,251 loops=1)

  • Filter: ((cd_id_operacao <> ld_cd_operacao) OR (cd_id_talhao <> ld_cd_talhao) OR (dt_dia <> ld_dt_dia) OR (cd_id_equipe <> ld_cd_id_equipe) OR (cd_id_operador <> ld_cd_id_operador) OR (vl_timezone <> ld_vl_timezone))
  • Rows Removed by Filter: 198210
  • Buffers: shared hit=335385 read=63222
34.          

CTE trecho_unico

35. 0.003 21,584.783 ↓ 0.0 0 1

GroupAggregate (cost=14,845.82..14,845.86 rows=1 width=78) (actual time=21,584.783..21,584.783 rows=0 loops=1)

  • Group Key: d_1.cd_cliente, d_1.cd_id_equipamento, d_1.cd_id_operacao, d_1.cd_id_talhao, d_1.dt_dia, d_1.cd_id_equipe, d_1.cd_id_operador, d_1.vl_timezone
  • Buffers: shared hit=335385 read=63222
36. 0.007 21,584.780 ↓ 0.0 0 1

Sort (cost=14,845.82..14,845.82 rows=1 width=54) (actual time=21,584.779..21,584.780 rows=0 loops=1)

  • Sort Key: d_1.cd_cliente, d_1.cd_id_equipamento, d_1.cd_id_operacao, d_1.cd_id_talhao, d_1.dt_dia, d_1.cd_id_equipe, d_1.cd_id_operador, d_1.vl_timezone
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=335385 read=63222
37. 0.002 21,584.773 ↓ 0.0 0 1

Nested Loop (cost=0.58..14,845.81 rows=1 width=54) (actual time=21,584.772..21,584.773 rows=0 loops=1)

  • Buffers: shared hit=335385 read=63222
38. 0.000 21,584.771 ↓ 0.0 0 1

Limit (cost=0.02..0.04 rows=1 width=30) (actual time=21,584.770..21,584.771 rows=0 loops=1)

  • Buffers: shared hit=335385 read=63222
39.          

Initplan (forLimit)

40. 19,244.836 19,244.836 ↑ 10,266.0 1 1

CTE Scan on last_and_lag (cost=0.00..205.32 rows=10,266 width=0) (actual time=19,244.835..19,244.836 rows=1 loops=1)

  • Buffers: shared hit=335385 read=63222
41. 19,244.847 19,244.847 ↓ 0.0 0 1

Result (cost=0.00..205.32 rows=10,266 width=30) (actual time=19,244.847..19,244.847 rows=0 loops=1)

  • One-Time Filter: (NOT $9)
  • Buffers: shared hit=335385 read=63222
42. 0.000 0.000 ↓ 0.0 0

CTE Scan on dataset d_1 (cost=0.00..205.32 rows=10,266 width=30) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Append (cost=0.56..14,845.74 rows=1 width=46) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_i_idx1 on ddn_mapa_cliente4 dm_1 (cost=0.56..14,845.74 rows=1 width=46) (never executed)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504) AND (vl_timezone = d_1.vl_timezone) AND (cd_id_operacao = d_1.cd_id_operacao) AND (cd_id_equipe = d_1.cd_id_equipe) AND (cd_id_operador = d_1.cd_id_operador))
  • Filter: ((dt_hr_local_inicial >= d_1.dt_dia) AND (dt_hr_local_inicial <= (d_1.dt_dia + '23:59:59'::interval)))
45.          

CTE trechos

46. 0.003 1,220.275 ↓ 0.0 0 1

GroupAggregate (cost=623,462.00..623,462.05 rows=1 width=94) (actual time=1,220.275..1,220.275 rows=0 loops=1)

  • Group Key: lal.cd_cliente, lal.cd_id_equipamento, lal.cd_id_operacao, lal.cd_id_talhao, lal.dt_dia, lal.cd_id_equipe, lal.cd_id_operador, lal.vl_timezone, lal.dt_fim_trecho_anterior, lal.dt_fim
  • Buffers: shared hit=78184 read=1549
47. 0.015 1,220.272 ↓ 0.0 0 1

Sort (cost=623,462.00..623,462.01 rows=1 width=70) (actual time=1,220.271..1,220.272 rows=0 loops=1)

  • Sort Key: lal.cd_cliente, lal.cd_id_equipamento, lal.cd_id_operacao, lal.cd_id_talhao, lal.dt_dia, lal.cd_id_equipe, lal.cd_id_operador, lal.vl_timezone, lal.dt_fim_trecho_anterior, lal.dt_fim
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=78184 read=1549
48. 155.421 1,220.257 ↓ 0.0 0 1

Merge Join (cost=619,535.41..623,461.99 rows=1 width=70) (actual time=1,220.257..1,220.257 rows=0 loops=1)

  • Merge Cond: ((lal.cd_id_operacao = dm_2.cd_id_operacao) AND (lal.cd_id_equipe = dm_2.cd_id_equipe) AND (lal.cd_id_operador = dm_2.cd_id_operador) AND (lal.vl_timezone = dm_2.vl_timezone))
  • Join Filter: ((dm_2.dt_hr_utc_final <= lal.dt_fim) AND (dm_2.dt_hr_utc_inicial > COALESCE(lal.dt_fim_trecho_anterior, (dm_2.dt_hr_utc_inicial - '00:01:00'::interval))))
  • Buffers: shared hit=78184 read=1549
49. 1.567 5.392 ↑ 8.2 1,251 1

Sort (cost=889.32..914.99 rows=10,266 width=46) (actual time=4.585..5.392 rows=1,251 loops=1)

  • Sort Key: lal.cd_id_operacao, lal.cd_id_equipe, lal.cd_id_operador, lal.vl_timezone
  • Sort Method: quicksort Memory: 146kB
50. 3.825 3.825 ↑ 8.2 1,251 1

CTE Scan on last_and_lag lal (cost=0.00..205.32 rows=10,266 width=46) (actual time=0.001..3.825 rows=1,251 loops=1)

51. 371.711 1,059.444 ↑ 1.4 216,128 1

Sort (cost=618,646.09..619,405.73 rows=303,858 width=38) (actual time=920.648..1,059.444 rows=216,128 loops=1)

  • Sort Key: dm_2.cd_id_operacao, dm_2.cd_id_equipe, dm_2.cd_id_operador, dm_2.vl_timezone
  • Sort Method: quicksort Memory: 35714kB
  • Buffers: shared hit=78184 read=1549
52. 284.671 687.733 ↑ 1.0 299,845 1

Append (cost=0.56..590,975.20 rows=303,858 width=38) (actual time=1.474..687.733 rows=299,845 loops=1)

  • Buffers: shared hit=78184 read=1549
53. 403.062 403.062 ↑ 1.0 299,845 1

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_updated_idx on ddn_mapa_cliente4 dm_2 (cost=0.56..589,455.91 rows=303,858 width=38) (actual time=1.473..403.062 rows=299,845 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504))
  • Buffers: shared hit=78184 read=1549
54. 0.008 22,805.088 ↓ 0.0 0 1

Sort (cost=0.17..0.18 rows=2 width=78) (actual time=22,805.087..22,805.088 rows=0 loops=1)

  • Sort Key: trecho_unico.dt_inicio
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=413569 read=64771
55. 0.002 22,805.080 ↓ 0.0 0 1

Unique (cost=0.08..0.14 rows=2 width=78) (actual time=22,805.080..22,805.080 rows=0 loops=1)

  • Buffers: shared hit=413569 read=64771
56. 0.009 22,805.078 ↓ 0.0 0 1

Sort (cost=0.08..0.09 rows=2 width=78) (actual time=22,805.078..22,805.078 rows=0 loops=1)

  • Sort Key: trecho_unico.cd_cliente, trecho_unico.cd_id_equipamento, trecho_unico.cd_id_operacao, trecho_unico.cd_id_talhao, trecho_unico.dt_dia, trecho_unico.cd_id_equipe, trecho_unico.cd_id_operador, trecho_unico.vl_timezone, trecho_unico.dt_inicio, trecho_unico.dt_fim, trecho_unico.vl_kijos_arr
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=413569 read=64771
57. 0.005 22,805.069 ↓ 0.0 0 1

Append (cost=0.00..0.07 rows=2 width=78) (actual time=22,805.069..22,805.069 rows=0 loops=1)

  • Buffers: shared hit=413569 read=64771
58. 21,584.787 21,584.787 ↓ 0.0 0 1

CTE Scan on trecho_unico (cost=0.00..0.02 rows=1 width=78) (actual time=21,584.786..21,584.787 rows=0 loops=1)

  • Buffers: shared hit=335385 read=63222
59. 1,220.277 1,220.277 ↓ 0.0 0 1

CTE Scan on trechos (cost=0.00..0.02 rows=1 width=78) (actual time=1,220.277..1,220.277 rows=0 loops=1)

  • Buffers: shared hit=78184 read=1549
Planning time : 11.714 ms
Execution time : 22,849.019 ms