explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6yXw

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,440.321 68,256.254 ↑ 10.0 1 1

Limit (cost=1,335,604.19..1,335,604.91 rows=10 width=171) (actual time=68,256.100..68,256.254 rows=1 loops=1)

  • Buffers: shared hit=175517 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
  • Functions: 276
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 50.704 ms, Inlining 171.346 ms, Optimization 4109.569 ms, Emission 2446.882 ms, Total 6778.500 ms
2. 0.162 64,815.933 ↑ 235,343.0 1 1

Unique (cost=1,335,604.19..1,352,666.55 rows=235,343 width=171) (actual time=64,815.780..64,815.933 rows=1 loops=1)

  • Buffers: shared hit=175517 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
3. 0.178 64,815.771 ↑ 117,671.5 2 1

Sort (cost=1,335,604.19..1,336,192.54 rows=235,343 width=171) (actual time=64,815.770..64,815.771 rows=2 loops=1)

  • Sort Key: viagem0_.id DESC, viagem0_.base_id, viagem0_.categoriapedagio, viagem0_.consumokml, viagem0_.custocombustivel, viagem0_.custolitro, viagem0_.custopedagio, viagem0_.custototal, viagem0_.date, viagem0_.dispara_id, viagem0_.distanciatotal, viagem0_.embarcadora_id, viagem0_.empresa_id, viagem0_.gastocombustivel, viagem0_.gerenciadora, viagem0_.kmplanejada, viagem0_.localidade_id, viagem0_.macrogrupo_id, viagem0_.manifesto, viagem0_.monitorada, viagem0_.numliberacao, viagem0_.numero, viagem0_.seguradora, viagem0_.status, viagem0_.telefonecontato_id, viagem0_.tempo, viagem0_.tipo, viagem0_.veiculo_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=175517 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
4. 11,268.958 64,815.593 ↑ 117,671.5 2 1

Hash Join (cost=919,755.65..1,314,606.41 rows=235,343 width=171) (actual time=48,490.327..64,815.593 rows=2 loops=1)

  • Hash Cond: (viagem0_.empresa_id = empresa11_.id)
  • Join Filter: ((upper((veiculo1_.placa)::text) ~~ '1336536'::text) OR (upper((composicao3_.placa)::text) ~~ '1336536'::text) OR (upper((pessoa6_.nome)::text) ~~ '1336536'::text) OR (upper((pontoviage8_.nome)::text) ~~ '1336536'::text) OR (upper((localidade10_.nome)::text) ~~ '1336536'::text) OR (upper((pontoviage8_.cnpj)::text) ~~ '1336536'::text) OR (upper((pessoa6_.cpfcnpj)::text) ~~ '1336536'::text) OR (((viagem0_.id)::text || ''::text) ~~ '1336536'::text) OR (upper((empresa11_.nome)::text) ~~ '1336536'::text) OR (upper((empresa11_.cnpj)::text) ~~ '1336536'::text) OR (upper((documentos9_.numero)::text) ~~ '1336536'::text))
  • Rows Removed by Join Filter: 4156758
  • Buffers: shared hit=175494 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
5. 840.155 53,546.036 ↑ 1.1 4,156,762 1

Hash Join (cost=919,717.73..1,303,030.32 rows=4,387,011 width=294) (actual time=42,997.607..53,546.036 rows=4,156,762 loops=1)

  • Hash Cond: (viagem0_.localidade_id = localidade10_.id)
  • Buffers: shared hit=175473 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
6. 1,633.034 52,705.086 ↑ 1.1 4,157,811 1

Hash Left Join (cost=919,655.25..1,291,424.82 rows=4,387,011 width=274) (actual time=42,996.718..52,705.086 rows=4,157,811 loops=1)

  • Hash Cond: (elementosv7_.ponto_id = pontoviage8_.id)
  • Buffers: shared hit=175451 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
7. 6,193.476 50,950.728 ↑ 1.1 4,157,811 1

Hash Right Join (cost=911,165.81..1,271,419.25 rows=4,387,011 width=241) (actual time=42,873.957..50,950.728 rows=4,157,811 loops=1)

  • Hash Cond: (documentos9_.elementoviagem_id = elementosv7_.id)
  • Buffers: shared hit=171935 read=197905, temp read=167046 written=167046
  • I/O Timings: read=88397.284
8. 1,886.821 1,886.821 ↑ 1.0 4,465,911 1

Seq Scan on documento documentos9_ (cost=0.00..106,054.72 rows=4,466,472 width=18) (actual time=0.128..1,886.821 rows=4,465,911 loops=1)

  • Buffers: shared hit=40725 read=20666
  • I/O Timings: read=766.279
9. 3,031.669 42,870.431 ↑ 1.3 3,378,013 1

Hash (cost=714,949.17..714,949.17 rows=4,387,011 width=239) (actual time=42,870.431..42,870.431 rows=3,378,013 loops=1)

  • Buckets: 262144 Batches: 32 Memory Usage: 30276kB
  • Buffers: shared hit=131210 read=177239, temp read=45560 written=150470
  • I/O Timings: read=87631.004
10. 4,012.887 39,838.762 ↑ 1.3 3,378,013 1

Hash Right Join (cost=406,354.84..714,949.17 rows=4,387,011 width=239) (actual time=34,157.894..39,838.762 rows=3,378,013 loops=1)

  • Hash Cond: (elementosv7_.viagem_id = viagem0_.id)
  • Buffers: shared hit=131210 read=177239, temp read=45560 written=45560
  • I/O Timings: read=87631.004
11. 1,671.419 1,671.419 ↑ 1.0 5,948,261 1

Seq Scan on elementoviagem elementosv7_ (cost=0.00..143,291.35 rows=5,959,135 width=24) (actual time=0.321..1,671.419 rows=5,948,261 loops=1)

  • Buffers: shared hit=311 read=83389
  • I/O Timings: read=943.516
12. 1,070.255 34,154.456 ↓ 1.0 983,599 1

Hash (cost=364,305.53..364,305.53 rows=983,065 width=223) (actual time=34,154.455..34,154.456 rows=983,599 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 63059kB
  • Buffers: shared hit=130899 read=93850, temp written=21839
  • I/O Timings: read=86687.488
13. 0.000 33,084.201 ↓ 1.0 983,599 1

Gather (cost=76,427.09..364,305.53 rows=983,065 width=223) (actual time=3,986.350..33,084.201 rows=983,599 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=130899 read=93850
  • I/O Timings: read=86687.488
14. 316.849 33,401.629 ↑ 1.2 327,866 3

Parallel Hash Left Join (cost=75,427.09..264,999.03 rows=409,610 width=223) (actual time=3,957.158..33,401.629 rows=327,866 loops=3)

  • Hash Cond: (motorista5_.pessoa_id = pessoa6_.id)
  • Buffers: shared hit=130899 read=93850
  • I/O Timings: read=86687.488
15. 280.297 31,402.992 ↑ 1.2 327,866 3

Parallel Hash Left Join (cost=69,392.60..257,889.29 rows=409,610 width=195) (actual time=2,273.920..31,402.992 rows=327,866 loops=3)

  • Hash Cond: (motoristas4_.motoristas_id = motorista5_.id)
  • Buffers: shared hit=129909 read=91824
  • I/O Timings: read=86510.625
16. 385.650 31,069.151 ↑ 1.2 327,866 3

Parallel Hash Left Join (cost=63,154.12..250,575.56 rows=409,610 width=195) (actual time=2,219.195..31,069.151 rows=327,866 loops=3)

  • Hash Cond: (viagem0_.id = motoristas4_.viagem_id)
  • Buffers: shared hit=125419 read=91824
  • I/O Timings: read=86510.625
17. 129.967 28,715.994 ↑ 1.3 324,951 3

Parallel Hash Left Join (cost=43,254.05..227,449.27 rows=406,264 width=187) (actual time=240.988..28,715.994 rows=324,951 loops=3)

  • Hash Cond: (composicoe2_.composicoes_id = composicao3_.id)
  • Buffers: shared hit=123874 read=86091
  • I/O Timings: read=81423.789
18. 270.567 28,458.723 ↑ 1.3 324,951 3

Hash Left Join (cost=23,974.57..207,103.31 rows=406,264 width=187) (actual time=111.888..28,458.723 rows=324,951 loops=3)

  • Hash Cond: (viagem0_.id = composicoe2_.viagem_id)
  • Buffers: shared hit=106097 read=86091
  • I/O Timings: read=81423.789
19. 305.365 28,130.849 ↑ 1.3 322,332 3

Parallel Hash Left Join (cost=19,279.48..200,372.93 rows=406,264 width=179) (actual time=51.363..28,130.849 rows=322,332 loops=3)

  • Hash Cond: (viagem0_.veiculo_id = veiculo1_.id)
  • Buffers: shared hit=103367 read=86091
  • I/O Timings: read=81423.789
20. 27,775.297 27,775.297 ↑ 1.3 322,332 3

Parallel Seq Scan on viagem viagem0_ (cost=0.00..180,026.97 rows=406,264 width=171) (actual time=0.118..27,775.297 rows=322,332 loops=3)

  • Filter: ((status <> 'C'::bpchar) AND (status <> 'N'::bpchar))
  • Rows Removed by Filter: 122711
  • Buffers: shared hit=85590 read=86091
  • I/O Timings: read=81423.789
21. 21.381 50.187 ↑ 1.2 53,422 3

Parallel Hash (cost=18,444.77..18,444.77 rows=66,777 width=16) (actual time=50.185..50.187 rows=53,422 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 10848kB
  • Buffers: shared hit=17777
22. 28.806 28.806 ↑ 1.2 53,422 3

Parallel Seq Scan on automovel veiculo1_ (cost=0.00..18,444.77 rows=66,777 width=16) (actual time=0.033..28.806 rows=53,422 loops=3)

  • Buffers: shared hit=17777
23. 35.500 57.307 ↓ 1.0 168,231 3

Hash (cost=2,592.26..2,592.26 rows=168,226 width=16) (actual time=57.307..57.307 rows=168,231 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 9934kB
  • Buffers: shared hit=2730
24. 21.807 21.807 ↓ 1.0 168,231 3

Seq Scan on viagem_automovel composicoe2_ (cost=0.00..2,592.26 rows=168,226 width=16) (actual time=0.035..21.807 rows=168,231 loops=3)

  • Buffers: shared hit=2730
25. 30.771 127.304 ↑ 1.2 53,422 3

Parallel Hash (cost=18,444.77..18,444.77 rows=66,777 width=16) (actual time=127.303..127.304 rows=53,422 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 10848kB
  • Buffers: shared hit=17777
26. 96.533 96.533 ↑ 1.2 53,422 3

Parallel Seq Scan on automovel composicao3_ (cost=0.00..18,444.77 rows=66,777 width=16) (actual time=0.082..96.533 rows=53,422 loops=3)

  • Buffers: shared hit=17777
27. 209.639 1,967.507 ↑ 1.3 447,826 3

Parallel Hash (cost=12,887.81..12,887.81 rows=560,981 width=16) (actual time=1,967.507..1,967.507 rows=447,826 loops=3)

  • Buckets: 2097152 Batches: 1 Memory Usage: 79456kB
  • Buffers: shared hit=1545 read=5733
  • I/O Timings: read=5086.836
28. 1,757.868 1,757.868 ↑ 1.3 447,826 3

Parallel Seq Scan on viagem_motorista motoristas4_ (cost=0.00..12,887.81 rows=560,981 width=16) (actual time=1.735..1,757.868 rows=447,826 loops=3)

  • Buffers: shared hit=1545 read=5733
  • I/O Timings: read=5086.836
29. 22.432 53.544 ↑ 1.3 62,030 3

Parallel Hash (cost=5,267.10..5,267.10 rows=77,710 width=16) (actual time=53.544..53.544 rows=62,030 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 10816kB
  • Buffers: shared hit=4490
30. 31.112 31.112 ↑ 1.3 62,030 3

Parallel Seq Scan on motorista motorista5_ (cost=0.00..5,267.10 rows=77,710 width=16) (actual time=0.096..31.112 rows=62,030 loops=3)

  • Buffers: shared hit=4490
31. 506.169 1,681.788 ↑ 1.8 78,485 3

Parallel Hash (cost=4,303.11..4,303.11 rows=138,511 width=44) (actual time=1,681.787..1,681.788 rows=78,485 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 20704kB
  • Buffers: shared hit=892 read=2026
  • I/O Timings: read=176.863
32. 1,175.619 1,175.619 ↑ 1.8 78,485 3

Parallel Seq Scan on pessoa pessoa6_ (cost=0.00..4,303.11 rows=138,511 width=44) (actual time=1,099.692..1,175.619 rows=78,485 loops=3)

  • Buffers: shared hit=892 read=2026
  • I/O Timings: read=176.863
33. 59.511 121.324 ↑ 1.0 221,000 1

Hash (cost=5,726.42..5,726.42 rows=221,042 width=49) (actual time=121.324..121.324 rows=221,000 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 19628kB
  • Buffers: shared hit=3516
34. 61.813 61.813 ↑ 1.0 221,000 1

Seq Scan on pontoviagem pontoviage8_ (cost=0.00..5,726.42 rows=221,042 width=49) (actual time=0.042..61.813 rows=221,000 loops=1)

  • Buffers: shared hit=3516
35. 0.231 0.795 ↑ 1.0 1,799 1

Hash (cost=39.99..39.99 rows=1,799 width=28) (actual time=0.795..0.795 rows=1,799 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 124kB
  • Buffers: shared hit=22
36. 0.564 0.564 ↑ 1.0 1,799 1

Seq Scan on localidade localidade10_ (cost=0.00..39.99 rows=1,799 width=28) (actual time=0.026..0.564 rows=1,799 loops=1)

  • Buffers: shared hit=22
37. 0.119 0.599 ↑ 1.0 752 1

Hash (cost=28.52..28.52 rows=752 width=32) (actual time=0.599..0.599 rows=752 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
  • Buffers: shared hit=21
38. 0.480 0.480 ↑ 1.0 752 1

Seq Scan on empresa empresa11_ (cost=0.00..28.52 rows=752 width=32) (actual time=0.058..0.480 rows=752 loops=1)

  • Buffers: shared hit=21
Planning time : 12.660 ms
Execution time : 68,332.475 ms