explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HjQ5

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 12,376.099 ↓ 0.0 0 1

Result (cost=793,824.67..793,830.70 rows=2 width=110) (actual time=12,376.099..12,376.099 rows=0 loops=1)

  • Buffers: shared hit=418264 read=78015
  • Functions: 188
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 34.831 ms, Inlining 248.026 ms, Optimization 1430.018 ms, Emission 793.259 ms, Total 2506.135 ms
2.          

CTE dataset

3. 0.000 7,573.644 ↓ 19.4 199,461 1

Append (cost=61,807.53..143,126.38 rows=10,266 width=46) (actual time=2,652.260..7,573.644 rows=199,461 loops=1)

  • Buffers: shared hit=340078 read=76468
4.          

Initplan (forAppend)

5. 0.006 3.016 ↑ 1.0 1 1

Subquery Scan on t (cost=41.23..41.25 rows=1 width=8) (actual time=3.013..3.016 rows=1 loops=1)

  • Buffers: shared hit=5 read=18
6.          

Initplan (forSubquery Scan)

7. 0.005 0.542 ↑ 1.0 1 1

Result (cost=0.79..0.80 rows=1 width=8) (actual time=0.541..0.542 rows=1 loops=1)

  • Buffers: shared hit=3 read=3
8.          

Initplan (forResult)

9. 0.006 0.537 ↑ 1.0 1 1

Limit (cost=0.57..0.79 rows=1 width=8) (actual time=0.533..0.537 rows=1 loops=1)

  • Buffers: shared hit=3 read=3
10. 0.002 0.531 ↑ 303,858.0 1 1

Merge Append (cost=0.57..66,232.27 rows=303,858 width=8) (actual time=0.530..0.531 rows=1 loops=1)

  • Sort Key: ddn_mapa_cliente4.dt_hr_utc_inicial
  • Buffers: shared hit=3 read=3
11. 0.529 0.529 ↑ 303,858.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..63,193.68 rows=303,858 width=8) (actual time=0.527..0.529 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=3 read=3
12. 0.008 2.468 ↑ 1.0 1 1

Aggregate (cost=40.43..40.44 rows=1 width=8) (actual time=2.467..2.468 rows=1 loops=1)

  • Buffers: shared hit=2 read=15
13. 0.003 2.460 ↓ 0.0 0 1

Bitmap Heap Scan on trecho_operacao_talhao (cost=4.78..40.43 rows=1 width=8) (actual time=2.460..2.460 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=2 read=15
14. 2.457 2.457 ↓ 0.0 0 1

Bitmap Index Scan on trecho_operacao_talhao_un (cost=0.00..4.78 rows=9 width=0) (actual time=2.456..2.457 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=2 read=15
15. 0.017 2,649.152 ↑ 1.0 1 1

Subquery Scan on t_1 (cost=61,765.69..61,765.71 rows=1 width=8) (actual time=2,649.149..2,649.152 rows=1 loops=1)

  • Buffers: shared hit=123511 read=41749
16.          

Initplan (forSubquery Scan)

17. 0.000 2,649.085 ↑ 1.0 1 1

Finalize Aggregate (cost=61,725.25..61,725.26 rows=1 width=8) (actual time=2,649.083..2,649.085 rows=1 loops=1)

  • Buffers: shared hit=123494 read=41749
18. 25.694 2,649.191 ↓ 1.2 5 1

Gather (cost=61,724.83..61,725.24 rows=4 width=8) (actual time=2,648.478..2,649.191 rows=5 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=123494 read=41749
19. 42.289 2,623.497 ↑ 1.0 1 5

Partial Aggregate (cost=60,724.83..60,724.84 rows=1 width=8) (actual time=2,623.496..2,623.497 rows=1 loops=5)

  • Buffers: shared hit=123494 read=41749
20. 79.772 2,581.208 ↑ 1.3 59,969 5

Parallel Append (cost=0.56..60,534.92 rows=75,964 width=8) (actual time=124.918..2,581.208 rows=59,969 loops=5)

  • Buffers: shared hit=123494 read=41749
21. 2,501.436 2,501.436 ↑ 1.3 59,969 5

Parallel 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..60,155.10 rows=75,964 width=8) (actual time=124.915..2,501.436 rows=59,969 loops=5)

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

Aggregate (cost=40.43..40.44 rows=1 width=8) (actual time=0.049..0.050 rows=1 loops=1)

  • Buffers: shared hit=17
23. 0.001 0.046 ↓ 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.045..0.046 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.045 0.045 ↓ 0.0 0 1

Bitmap Index Scan on trecho_operacao_talhao_un (cost=0.00..4.78 rows=9 width=0) (actual time=0.044..0.045 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. 7,319.843 7,319.843 ↓ 19.4 199,461 1

Index Scan using ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_hr_utc_in_idx on ddn_mapa_cliente4 dm (cost=0.56..81,268.09 rows=10,266 width=46) (actual time=2,652.257..7,319.843 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=340078 read=76468
26.          

CTE lead_operacao_talhao

27. 510.386 8,753.357 ↓ 19.4 199,461 1

WindowAgg (cost=889.32..1,248.63 rows=10,266 width=68) (actual time=8,120.161..8,753.357 rows=199,461 loops=1)

  • Buffers: shared hit=340078 read=76468
28. 313.179 8,242.971 ↓ 19.4 199,461 1

Sort (cost=889.32..914.99 rows=10,266 width=46) (actual time=8,120.096..8,242.971 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=340078 read=76468
29. 7,929.792 7,929.792 ↓ 19.4 199,461 1

CTE Scan on dataset d (cost=0.00..205.32 rows=10,266 width=46) (actual time=2,652.266..7,929.792 rows=199,461 loops=1)

  • Buffers: shared hit=340078 read=76468
30.          

CTE last_and_lag

31. 1.755 8,998.505 ↑ 8.2 1,251 1

WindowAgg (cost=1,043.31..1,274.30 rows=10,266 width=46) (actual time=8,996.160..8,998.505 rows=1,251 loops=1)

  • Buffers: shared hit=340078 read=76468
32. 1.726 8,996.750 ↑ 8.2 1,251 1

Sort (cost=1,043.31..1,068.98 rows=10,266 width=38) (actual time=8,996.134..8,996.750 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=340078 read=76468
33. 8,995.024 8,995.024 ↑ 8.2 1,251 1

CTE Scan on lead_operacao_talhao lot (cost=0.00..359.31 rows=10,266 width=38) (actual time=8,120.444..8,995.024 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=340078 read=76468
34.          

CTE trecho_unico

35. 0.003 10,846.855 ↓ 0.0 0 1

GroupAggregate (cost=24,713.09..24,713.14 rows=1 width=78) (actual time=10,846.854..10,846.855 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=340078 read=76468
36. 0.004 10,846.852 ↓ 0.0 0 1

Sort (cost=24,713.09..24,713.10 rows=1 width=54) (actual time=10,846.851..10,846.852 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=340078 read=76468
37. 0.002 10,846.848 ↓ 0.0 0 1

Nested Loop (cost=17,676.50..24,713.08 rows=1 width=54) (actual time=10,846.847..10,846.848 rows=0 loops=1)

  • Buffers: shared hit=340078 read=76468
38. 0.000 10,846.846 ↓ 0.0 0 1

Limit (cost=0.02..0.04 rows=1 width=30) (actual time=10,846.845..10,846.846 rows=0 loops=1)

  • Buffers: shared hit=340078 read=76468
39.          

Initplan (forLimit)

40. 8,996.167 8,996.167 ↑ 10,266.0 1 1

CTE Scan on last_and_lag (cost=0.00..205.32 rows=10,266 width=0) (actual time=8,996.166..8,996.167 rows=1 loops=1)

  • Buffers: shared hit=340078 read=76468
41. 8,996.179 8,996.179 ↓ 0.0 0 1

Result (cost=0.00..205.32 rows=10,266 width=30) (actual time=8,996.178..8,996.179 rows=0 loops=1)

  • One-Time Filter: (NOT $9)
  • Buffers: shared hit=340078 read=76468
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=17,676.48..24,713.02 rows=1 width=46) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ddn_mapa_cliente4 dm_1 (cost=17,676.48..24,713.02 rows=1 width=46) (never executed)

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

BitmapAnd (cost=17,676.48..17,676.48 rows=1,767 width=0) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ddn_mapa_cliente4_cd_id_operador_idx (cost=0.00..8,305.12 rows=107,808 width=0) (never executed)

  • Index Cond: (cd_id_operador = d_1.cd_id_operador)
47. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ddn_mapa_cliente4_cd_cliente_cd_id_equipamento_dt_updated_idx (cost=0.00..9,295.14 rows=303,858 width=0) (never executed)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504))
48.          

CTE trechos

49. 0.003 1,529.219 ↓ 0.0 0 1

GroupAggregate (cost=623,462.00..623,462.05 rows=1 width=94) (actual time=1,529.218..1,529.219 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=78186 read=1547
50. 0.013 1,529.216 ↓ 0.0 0 1

Sort (cost=623,462.00..623,462.01 rows=1 width=70) (actual time=1,529.215..1,529.216 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=78186 read=1547
51. 153.993 1,529.203 ↓ 0.0 0 1

Merge Join (cost=619,535.41..623,461.99 rows=1 width=70) (actual time=1,529.202..1,529.203 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=78186 read=1547
52. 1.603 5.474 ↑ 8.2 1,251 1

Sort (cost=889.32..914.99 rows=10,266 width=46) (actual time=4.666..5.474 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
53. 3.871 3.871 ↑ 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.871 rows=1,251 loops=1)

54. 421.325 1,369.736 ↑ 1.4 216,128 1

Sort (cost=618,646.09..619,405.73 rows=303,858 width=38) (actual time=1,230.806..1,369.736 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=78186 read=1547
55. 354.460 948.411 ↑ 1.0 299,845 1

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

  • Buffers: shared hit=78186 read=1547
56. 593.951 593.951 ↑ 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=0.676..593.951 rows=299,845 loops=1)

  • Index Cond: ((cd_cliente = 4) AND (cd_id_equipamento = 103504))
  • Buffers: shared hit=78186 read=1547
57. 0.005 12,376.097 ↓ 0.0 0 1

Sort (cost=0.17..0.18 rows=2 width=78) (actual time=12,376.096..12,376.097 rows=0 loops=1)

  • Sort Key: trecho_unico.dt_inicio
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=418264 read=78015
58. 0.002 12,376.092 ↓ 0.0 0 1

Unique (cost=0.08..0.14 rows=2 width=78) (actual time=12,376.091..12,376.092 rows=0 loops=1)

  • Buffers: shared hit=418264 read=78015
59. 0.007 12,376.090 ↓ 0.0 0 1

Sort (cost=0.08..0.09 rows=2 width=78) (actual time=12,376.089..12,376.090 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=418264 read=78015
60. 0.004 12,376.083 ↓ 0.0 0 1

Append (cost=0.00..0.07 rows=2 width=78) (actual time=12,376.082..12,376.083 rows=0 loops=1)

  • Buffers: shared hit=418264 read=78015
61. 10,846.858 10,846.858 ↓ 0.0 0 1

CTE Scan on trecho_unico (cost=0.00..0.02 rows=1 width=78) (actual time=10,846.857..10,846.858 rows=0 loops=1)

  • Buffers: shared hit=340078 read=76468
62. 1,529.221 1,529.221 ↓ 0.0 0 1

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

  • Buffers: shared hit=78186 read=1547
Planning time : 6.455 ms
Execution time : 12,418.210 ms