explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yUMY : le mpon monit

Settings
# exclusive inclusive rows x rows loops node
1. 21.660 10,074.391 ↑ 25.6 475 1

Subquery Scan on from_mpon (cost=176,429.18..204,998.31 rows=12,183 width=585) (actual time=9,952.530..10,074.391 rows=475 loops=1)

2. 103.526 10,052.731 ↑ 25.6 475 1

GroupAggregate (cost=176,429.18..198,267.20 rows=12,183 width=592) (actual time=9,952.434..10,052.731 rows=475 loops=1)

  • Group Key: mpon_jor.mpon_codigo, pess_moto.pess_nome, veic_veiculo.veic_placa, veic_veiculo.veic_frota, viag_viagem.viag_codigo, pess_tran_veic.pess_nome, pess_tran_viag.pess_nome, pess_prop_veic.pess_nome, upos_ultima_posicao.upos_descricao_sistema, upos_ultima_posicao.upos_data_comp_bordo, upos_ultima_posicao.upos_ignicao, upos_ultima_posicao.upos_velocidade, mpon_ult.mpon_tipo_registro, mpon_ult.mpon_data_inicio, mpon_ult.mpon_data_fim, (CASE WHEN (mpon_ult.mpon_data_fim IS NOT NULL) THEN ('[fim] '::text || (cdes_ult.cdes_constantes -> (mpon_ult.mpon_tipo_registro)::text)) WHEN (mpon_ult.mpon_data_inicio IS NOT NULL) THEN ('[início] '::text || (cdes_ult.cdes_constantes -> (mpon_ult.mpon_tipo_registro)::text)) ELSE ''::text END), mpon_jor.sum_mpon_espera, mpon_jor.sum_mpon_reserva, mtra_motorista_transportador.mtra_salario, mtra_motorista_transportador.mtra_carga_horaria_mes, mtra_motorista_transportador.mtra_perc_hora_espera, mtra_motorista_transportador.mtra_perc_hora_reser (...)
3.          

CTE with_mpon_jor

4. 1,954.432 2,265.148 ↓ 3.1 478 1

Nested Loop (cost=0.00..21,969.01 rows=154 width=68) (actual time=158.234..2,265.148 rows=478 loops=1)

  • Join Filter: (mpon_jor_2.mpon_cjmo_codigo = cjmo_configuracao_jornada_motorista.cjmo_codigo)
5. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on cjmo_configuracao_jornada_motorista (cost=0.00..1.01 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

6. 310.709 310.709 ↓ 3.1 478 1

Seq Scan on mpon_motorista_ponto mpon_jor_2 (cost=0.00..21,888.69 rows=154 width=48) (actual time=151.313..310.709 rows=478 loops=1)

  • Filter: ((mpon_data_inicio IS NOT NULL) AND (mpon_data_fim IS NULL) AND ((mpon_tipo_registro)::text = 'JOR'::text))
  • Rows Removed by Filter: 754942
7. 8.615 9,949.205 ↑ 8.8 1,377 1

Sort (cost=154,460.17..154,490.63 rows=12,183 width=364) (actual time=9,948.078..9,949.205 rows=1,377 loops=1)

  • Sort Key: mpon_jor.mpon_codigo, pess_moto.pess_nome, veic_veiculo.veic_placa, veic_veiculo.veic_frota, viag_viagem.viag_codigo, pess_tran_veic.pess_nome, pess_tran_viag.pess_nome, pess_prop_veic.pess_nome, upos_ultima_posicao.upos_descricao_sistema, upos_ultima_posicao.upos_data_comp_bordo, upos_ultima_posicao.upos_ignicao, upos_ultima_posicao.upos_velocidade, mpon_ult.mpon_tipo_registro, mpon_ult.mpon_data_inicio, mpon_ult.mpon_data_fim, (CASE WHEN (mpon_ult.mpon_data_fim IS NOT NULL) THEN ('[fim] '::text || (cdes_ult.cdes_constantes -> (mpon_ult.mpon_tipo_registro)::text)) WHEN (mpon_ult.mpon_data_inicio IS NOT NULL) THEN ('[início] '::text || (cdes_ult.cdes_constantes -> (mpon_ult.mpon_tipo_registro)::text)) ELSE ''::text END), mpon_jor.sum_mpon_espera, mpon_jor.sum_mpon_reserva, mtra_motorista_transportador.mtra_salario, mtra_motorista_transportador.mtra_carga_horaria_mes, mtra_motorista_transportador.mtra_perc_hora_espera, mtra_motorista_transportador.mtra_perc_hora_ (...)
  • Sort Method: quicksort Memory: 753kB
8. 10.945 9,940.590 ↑ 8.8 1,377 1

Hash Left Join (cost=125,164.46..151,592.90 rows=12,183 width=364) (actual time=9,543.422..9,940.590 rows=1,377 loops=1)

  • Hash Cond: (mpon.mpon_cjmo_codigo = cjmo_mpon.cjmo_codigo)
9. 2.717 9,929.634 ↑ 8.8 1,377 1

Hash Left Join (cost=125,163.44..151,302.53 rows=12,183 width=418) (actual time=9,535.641..9,929.634 rows=1,377 loops=1)

  • Hash Cond: ((mpon_jor.mpon_moto_pfis_pess_oras_codigo = mtra_motorista_transportador.mtra_moto_pfis_pess_oras_codigo) AND (COALESCE(mpon_jor.mpon_tran_pess_oras_codigo, mpon_jor.mpon_tran_pess_oras_propri) = mtra_motorista_transportador.mtra_tran_pess_oras_codigo))
10. 2.261 9,925.374 ↑ 8.8 1,377 1

Hash Left Join (cost=125,113.29..151,188.42 rows=12,183 width=411) (actual time=9,534.084..9,925.374 rows=1,377 loops=1)

  • Hash Cond: (veic_veiculo.veic_pess_oras_codigo_propri = pess_prop_veic.pess_oras_codigo)
11. 2.204 9,921.767 ↑ 8.8 1,377 1

Hash Left Join (cost=125,071.11..151,114.14 rows=12,183 width=391) (actual time=9,532.725..9,921.767 rows=1,377 loops=1)

  • Hash Cond: (veic_veiculo.veic_tran_pess_oras_codigo = pess_tran_veic.pess_oras_codigo)
12. 2.407 9,918.197 ↑ 8.8 1,377 1

Hash Join (cost=125,028.93..151,039.88 rows=12,183 width=371) (actual time=9,531.346..9,918.197 rows=1,377 loops=1)

  • Hash Cond: (mpon_jor.mpon_moto_pfis_pess_oras_codigo = pess_moto.pess_oras_codigo)
13. 2.003 9,913.550 ↑ 8.8 1,377 1

Hash Left Join (cost=124,986.75..150,965.61 rows=12,183 width=347) (actual time=9,529.091..9,913.550 rows=1,377 loops=1)

  • Hash Cond: (mpon_jor.mpon_viag_codigo = viag_viagem.viag_codigo)
14. 3.075 9,911.536 ↑ 8.8 1,377 1

Hash Join (cost=124,934.06..150,851.42 rows=12,183 width=323) (actual time=9,529.049..9,911.536 rows=1,377 loops=1)

  • Hash Cond: (((term_terminal.term_numero_terminal)::text = (upos_ultima_posicao.upos_term_numero_terminal)::text) AND (term_terminal.term_vtec_codigo = upos_ultima_posicao.upos_vtec_codigo))
15. 4.504 9,896.925 ↑ 28.8 1,525 1

Nested Loop Left Join (cost=124,500.96..149,858.03 rows=43,846 width=284) (actual time=9,337.073..9,896.925 rows=1,525 loops=1)

16. 610.542 9,890.896 ↑ 28.8 1,525 1

Hash Right Join (cost=124,500.96..149,308.71 rows=43,846 width=174) (actual time=9,337.032..9,890.896 rows=1,525 loops=1)

  • Hash Cond: (mpon.mpon_moto_pfis_pess_oras_codigo = mpon_jor.mpon_moto_pfis_pess_oras_codigo)
  • Join Filter: ((mpon.mpon_data_inicio >= mpon_jor.mpon_data_inicio) AND (mpon.mpon_codigo <> mpon_jor.mpon_codigo))
  • Rows Removed by Join Filter: 509830
17. 447.382 447.382 ↑ 1.0 755,420 1

Seq Scan on mpon_motorista_ponto mpon (cost=0.00..19,996.75 rows=756,775 width=32) (actual time=0.022..447.382 rows=755,420 loops=1)

18. 0.913 8,832.972 ↓ 3.5 532 1

Hash (cost=124,499.04..124,499.04 rows=154 width=150) (actual time=8,832.972..8,832.972 rows=532 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 93kB
19. 107.650 8,832.059 ↓ 3.5 532 1

Nested Loop Left Join (cost=69,328.48..124,499.04 rows=154 width=150) (actual time=8,617.677..8,832.059 rows=532 loops=1)

  • Join Filter: ((mpon_ppe.mpon_data_fim <= mpon_jor.mpon_data_inicio) AND (mpon_ppe.mpon_moto_pfis_pess_oras_codigo = mpon_jor.mpon_moto_pfis_pess_oras_codigo))
  • Rows Removed by Join Filter: 184770
20. 0.786 8,352.525 ↓ 3.1 478 1

Hash Right Join (cost=69,328.48..96,894.08 rows=154 width=134) (actual time=8,341.698..8,352.525 rows=478 loops=1)

  • Hash Cond: (mpon_pds.mpon_moto_pfis_pess_oras_codigo = mpon_jor.mpon_moto_pfis_pess_oras_codigo)
  • Join Filter: (mpon_pds.mpon_data_fim <= mpon_jor.mpon_data_inicio)
  • Rows Removed by Join Filter: 3
21. 205.784 205.784 ↑ 1.7 112 1

Seq Scan on mpon_motorista_ponto mpon_pds (cost=0.00..27,564.50 rows=185 width=24) (actual time=195.723..205.784 rows=112 loops=1)

  • Filter: (((mpon_tipo_registro)::text = 'PDS'::text) AND (mpon_data_inicio > (now() - '7 days'::interval)))
  • Rows Removed by Filter: 755308
22. 0.957 8,145.955 ↓ 3.1 477 1

Hash (cost=69,326.56..69,326.56 rows=154 width=118) (actual time=8,145.954..8,145.955 rows=477 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 80kB
23. 1.304 8,144.998 ↓ 3.1 477 1

Hash Left Join (cost=69,283.90..69,326.56 rows=154 width=118) (actual time=8,139.900..8,144.998 rows=477 loops=1)

  • Hash Cond: (mpon_jor.mpon_codigo = mpon_ult.mpon_codigo)
24. 2.457 2,275.646 ↓ 3.1 477 1

Hash Join (cost=48.50..90.75 rows=154 width=98) (actual time=2,271.833..2,275.646 rows=477 loops=1)

  • Hash Cond: (term_terminal.term_codigo = mpon_jor.mpon_term_codigo)
25. 1.428 1.428 ↓ 1.0 1,219 1

Seq Scan on term_terminal (cost=0.00..36.15 rows=1,215 width=15) (actual time=0.014..1.428 rows=1,219 loops=1)

26. 0.380 2,271.761 ↓ 3.1 477 1

Hash (cost=46.57..46.57 rows=154 width=87) (actual time=2,271.761..2,271.761 rows=477 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
27. 1.094 2,271.381 ↓ 3.1 477 1

Hash Join (cost=5.00..46.57 rows=154 width=87) (actual time=2,269.694..2,271.381 rows=477 loops=1)

  • Hash Cond: (veic_veiculo.veic_oras_codigo = mpon_jor.mpon_veic_oras_codigo)
28. 0.618 0.618 ↓ 1.0 1,096 1

Seq Scan on veic_veiculo (cost=0.00..35.93 rows=1,093 width=27) (actual time=0.012..0.618 rows=1,096 loops=1)

29. 1.541 2,269.669 ↓ 3.1 477 1

Hash (cost=3.08..3.08 rows=154 width=68) (actual time=2,269.669..2,269.669 rows=477 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
30. 2,268.128 2,268.128 ↓ 3.1 478 1

CTE Scan on with_mpon_jor mpon_jor (cost=0.00..3.08 rows=154 width=68) (actual time=158.242..2,268.128 rows=478 loops=1)

31. 0.680 5,868.048 ↓ 2.9 452 1

Hash (cost=69,233.47..69,233.47 rows=154 width=28) (actual time=5,868.047..5,868.048 rows=452 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
32. 0.980 5,867.368 ↓ 2.9 452 1

Subquery Scan on mpon_ult (cost=68,963.38..69,233.47 rows=154 width=28) (actual time=5,862.604..5,867.368 rows=452 loops=1)

33. 2.234 5,866.388 ↓ 2.9 452 1

Unique (cost=68,963.38..69,231.93 rows=154 width=28) (actual time=5,862.598..5,866.388 rows=452 loops=1)

34. 22.537 5,864.154 ↑ 38.3 1,403 1

Sort (cost=68,963.38..69,097.66 rows=53,710 width=28) (actual time=5,862.593..5,864.154 rows=1,403 loops=1)

  • Sort Key: mpon_jor_1.mpon_codigo, mpon_1.mpon_data_inicio DESC, mpon_1.mpon_data_fim DESC
  • Sort Method: quicksort Memory: 158kB
35. 2,615.391 5,841.617 ↑ 38.3 1,403 1

Hash Join (cost=34,630.44..64,743.68 rows=53,710 width=28) (actual time=3,258.077..5,841.617 rows=1,403 loops=1)

  • Hash Cond: (mpon_jor_1.mpon_moto_pfis_pess_oras_codigo = mpon_1.mpon_moto_pfis_pess_oras_codigo)
  • Join Filter: ((mpon_1.mpon_data_inicio >= mpon_jor_1.mpon_data_inicio) AND (mpon_1.mpon_codigo <> mpon_jor_1.mpon_codigo))
  • Rows Removed by Join Filter: 455971
36. 339.911 339.911 ↓ 3.1 478 1

Seq Scan on mpon_motorista_ponto mpon_jor_1 (cost=0.00..21,888.69 rows=154 width=16) (actual time=181.406..339.911 rows=478 loops=1)

  • Filter: ((mpon_data_inicio IS NOT NULL) AND (mpon_data_fim IS NULL) AND ((mpon_tipo_registro)::text = 'JOR'::text))
  • Rows Removed by Filter: 754942
37. 1,857.526 2,886.315 ↑ 1.0 755,420 1

Hash (cost=19,996.75..19,996.75 rows=756,775 width=32) (actual time=2,886.315..2,886.315 rows=755,420 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 16) Memory Usage: 3755kB
38. 1,028.789 1,028.789 ↑ 1.0 755,420 1

Seq Scan on mpon_motorista_ponto mpon_1 (cost=0.00..19,996.75 rows=756,775 width=32) (actual time=0.017..1,028.789 rows=755,420 loops=1)

39. 96.640 371.884 ↓ 25.8 387 478

Materialize (cost=0.00..27,564.58 rows=15 width=24) (actual time=0.264..0.778 rows=387 loops=478)

40. 275.244 275.244 ↓ 25.8 387 1

Seq Scan on mpon_motorista_ponto mpon_ppe (cost=0.00..27,564.50 rows=15 width=24) (actual time=126.121..275.244 rows=387 loops=1)

  • Filter: (((mpon_tipo_registro)::text = 'PPE'::text) AND (mpon_data_inicio > (now() - '1 day'::interval)))
  • Rows Removed by Filter: 755033
41. 1.506 1.525 ↑ 1.0 1 1,525

Materialize (cost=0.00..1.25 rows=1 width=110) (actual time=0.001..0.001 rows=1 loops=1,525)

42. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on cdes_constante_descricao cdes_ult (cost=0.00..1.25 rows=1 width=110) (actual time=0.018..0.019 rows=1 loops=1)

  • Filter: ((cdes_campo)::text = 'mpon_tipo_registro'::text)
  • Rows Removed by Filter: 19
43. 10.132 11.536 ↑ 1.0 644 1

Hash (cost=423.44..423.44 rows=644 width=61) (actual time=11.536..11.536 rows=644 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
44. 1.404 1.404 ↑ 1.0 644 1

Seq Scan on upos_ultima_posicao (cost=0.00..423.44 rows=644 width=61) (actual time=0.013..1.404 rows=644 loops=1)

45. 0.001 0.011 ↓ 0.0 0 1

Hash (cost=52.43..52.43 rows=20 width=28) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.002 0.010 ↓ 0.0 0 1

Hash Left Join (cost=42.18..52.43 rows=20 width=28) (actual time=0.009..0.010 rows=0 loops=1)

  • Hash Cond: (viag_viagem.viag_tran_pess_oras_codigo = pess_tran_viag.pess_oras_codigo)
47. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on viag_viagem (cost=0.00..10.20 rows=20 width=8) (actual time=0.007..0.008 rows=0 loops=1)

48. 0.000 0.000 ↓ 0.0 0

Hash (cost=27.08..27.08 rows=1,208 width=28) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Seq Scan on pess_pessoa pess_tran_viag (cost=0.00..27.08 rows=1,208 width=28) (never executed)

50. 1.178 2.240 ↑ 1.0 1,189 1

Hash (cost=27.08..27.08 rows=1,208 width=28) (actual time=2.239..2.240 rows=1,189 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
51. 1.062 1.062 ↑ 1.0 1,189 1

Seq Scan on pess_pessoa pess_moto (cost=0.00..27.08 rows=1,208 width=28) (actual time=0.018..1.062 rows=1,189 loops=1)

52. 0.694 1.366 ↑ 1.0 1,189 1

Hash (cost=27.08..27.08 rows=1,208 width=28) (actual time=1.365..1.366 rows=1,189 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
53. 0.672 0.672 ↑ 1.0 1,189 1

Seq Scan on pess_pessoa pess_tran_veic (cost=0.00..27.08 rows=1,208 width=28) (actual time=0.007..0.672 rows=1,189 loops=1)

54. 0.698 1.346 ↑ 1.0 1,189 1

Hash (cost=27.08..27.08 rows=1,208 width=28) (actual time=1.345..1.346 rows=1,189 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
55. 0.648 0.648 ↑ 1.0 1,189 1

Seq Scan on pess_pessoa pess_prop_veic (cost=0.00..27.08 rows=1,208 width=28) (actual time=0.007..0.648 rows=1,189 loops=1)

56. 0.785 1.543 ↓ 1.1 1,105 1

Hash (cost=35.06..35.06 rows=1,006 width=27) (actual time=1.542..1.543 rows=1,105 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 68kB
57. 0.758 0.758 ↓ 1.1 1,105 1

Seq Scan on mtra_motorista_transportador (cost=0.00..35.06 rows=1,006 width=27) (actual time=0.009..0.758 rows=1,105 loops=1)

58. 0.003 0.011 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on cjmo_configuracao_jornada_motorista cjmo_mpon (cost=0.00..1.01 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=1)

Planning time : 125.011 ms
Execution time : 10,075.656 ms