explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9wyE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 40.697 3,871.031 ↑ 1.0 1,000 1

Limit (cost=1,713,599.44..1,713,601.94 rows=1,000 width=417) (actual time=3,830.272..3,871.031 rows=1,000 loops=1)

2. 6.553 3,830.334 ↑ 43.9 2,000 1

Sort (cost=1,713,596.94..1,713,816.19 rows=87,703 width=417) (actual time=3,830.111..3,830.334 rows=2,000 loops=1)

  • Sort Key: aereo_res.id
  • Sort Method: top-N heapsort Memory: 742kB
3. 0.000 3,823.781 ↑ 11.2 7,840 1

Append (cost=544,435.04..1,708,349.76 rows=87,703 width=417) (actual time=2,006.837..3,823.781 rows=7,840 loops=1)

4. 8.502 2,121.137 ↑ 12.6 5,072 1

Nested Loop (cost=544,435.04..933,808.01 rows=63,791 width=433) (actual time=2,006.837..2,121.137 rows=5,072 loops=1)

5. 1.614 2,066.987 ↑ 12.6 5,072 1

Hash Join (cost=544,434.61..743,243.23 rows=63,791 width=277) (actual time=2,004.882..2,066.987 rows=5,072 loops=1)

  • Hash Cond: (aereo_res.idcia = cia.id)
6. 1.824 2,065.222 ↑ 12.6 5,072 1

Hash Join (cost=544,422.24..742,353.73 rows=63,791 width=213) (actual time=2,004.713..2,065.222 rows=5,072 loops=1)

  • Hash Cond: (aereo_res.idaeroportoorigem = aeroportoorigem.id)
7. 1.913 2,046.951 ↑ 12.6 5,072 1

Hash Join (cost=542,935.76..739,990.13 rows=63,791 width=203) (actual time=1,988.193..2,046.951 rows=5,072 loops=1)

  • Hash Cond: (aereo_res.idaeroportodestino = aeroportodestino.id)
8. 5.922 2,031.613 ↑ 12.6 5,072 1

Nested Loop (cost=541,449.28..737,626.52 rows=63,791 width=193) (actual time=1,974.686..2,031.613 rows=5,072 loops=1)

  • Join Filter: (orc.id = aereo_res.idorcamento)
9. 1.522 2,005.363 ↑ 24.2 2,541 1

Nested Loop (cost=541,448.84..682,858.48 rows=61,438 width=85) (actual time=1,974.670..2,005.363 rows=2,541 loops=1)

10. 1.884 1,993.677 ↑ 24.2 2,541 1

Nested Loop (cost=541,448.41..650,887.00 rows=61,438 width=69) (actual time=1,974.647..1,993.677 rows=2,541 loops=1)

11. 1.066 1,976.547 ↑ 24.2 2,541 1

GroupAggregate (cost=541,447.98..542,523.14 rows=61,438 width=12) (actual time=1,974.605..1,976.547 rows=2,541 loops=1)

  • Group Key: orc.id
12. 2.072 1,975.481 ↑ 12.1 5,072 1

Sort (cost=541,447.98..541,601.57 rows=61,438 width=4) (actual time=1,974.600..1,975.481 rows=5,072 loops=1)

  • Sort Key: orc.id
  • Sort Method: quicksort Memory: 430kB
13. 10.982 1,973.409 ↑ 12.1 5,072 1

Gather (cost=48,047.26..536,561.55 rows=61,438 width=4) (actual time=1,823.864..1,973.409 rows=5,072 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
14. 5.960 1,962.427 ↑ 14.1 725 7

Nested Loop (cost=47,047.26..529,417.75 rows=10,240 width=4) (actual time=1,652.144..1,962.427 rows=725 loops=7)

15. 2.868 1,956.455 ↑ 20.5 482 7

Hash Join (cost=47,046.82..520,725.03 rows=9,862 width=5) (actual time=1,548.867..1,956.455 rows=482 loops=7)

  • Hash Cond: (soli.idcentrocusto = centro.id_centro_custo)
16. 157.122 1,185.787 ↑ 1.1 18,408 7

Hash Join (cost=35,440.50..508,919.09 rows=19,724 width=9) (actual time=283.833..1,185.787 rows=18,408 loops=7)

  • Hash Cond: (orc.idsv = soli.id)
17. 746.746 746.746 ↑ 1.2 923,964 7

Parallel Seq Scan on orcamento orc (cost=0.00..469,244.34 rows=1,076,534 width=9) (actual time=0.045..746.746 rows=923,964 loops=7)

18. 33.052 281.919 ↓ 1.0 142,628 7

Hash (cost=33,692.40..33,692.40 rows=139,848 width=8) (actual time=281.919..281.919 rows=142,628 loops=7)

  • Buckets: 262144 Batches: 1 Memory Usage: 7620kB
19. 248.867 248.867 ↓ 1.0 142,628 7

Index Scan using idx2_solicitacao on solicitacao soli (cost=0.44..33,692.40 rows=139,848 width=8) (actual time=0.609..248.867 rows=142,628 loops=7)

  • Index Cond: ((datacad >= to_timestamp('2019-10-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (datacad <= to_timestamp('2019-10-31 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: (idstatus = ANY ('{8,3,4,5,6,1,2}'::integer[]))
  • Rows Removed by Filter: 27
20. 12.799 767.800 ↓ 3.9 66,784 7

Hash (cost=11,390.53..11,390.53 rows=17,263 width=4) (actual time=767.800..767.800 rows=66,784 loops=7)

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3372kB
21. 755.001 755.001 ↓ 3.9 66,784 7

Index Scan using ix1_id_usuario__usuario_centro_custo_extrator on usuario_centro_custo_extrator centro (cost=0.43..11,390.53 rows=17,263 width=4) (actual time=0.303..755.001 rows=66,784 loops=7)

  • Index Cond: (id_usuario = 2219789)
22. 0.012 0.012 ↑ 2.0 2 3,377

Index Scan using aereo_idx11 on aereo (cost=0.44..0.84 rows=4 width=5) (actual time=0.010..0.012 rows=2 loops=3,377)

  • Index Cond: (idorcamento = orc.id)
  • Filter: ((idaereo IS NULL) AND (orc.aprovado OR cancelado_emitido))
  • Rows Removed by Filter: 0
23. 15.246 15.246 ↑ 1.0 1 2,541

Index Scan using orcamento_pkey on orcamento orc_res (cost=0.43..1.74 rows=1 width=57) (actual time=0.006..0.006 rows=1 loops=2,541)

  • Index Cond: (id = orc.id)
24. 10.164 10.164 ↑ 1.0 1 2,541

Index Scan using pk_requisicao_id on solicitacao soli_res (cost=0.43..0.51 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=2,541)

  • Index Cond: (id = orc_res.idsv)
25. 20.328 20.328 ↑ 2.0 2 2,541

Index Scan using aereo_idx11 on aereo aereo_res (cost=0.44..0.84 rows=4 width=117) (actual time=0.005..0.008 rows=2 loops=2,541)

  • Index Cond: (idorcamento = orc_res.id)
  • Filter: ((idaereo IS NULL) AND (orc_res.aprovado OR cancelado_emitido))
26. 1.651 13.425 ↑ 1.0 8,938 1

Hash (cost=1,374.75..1,374.75 rows=8,938 width=18) (actual time=13.424..13.425 rows=8,938 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 577kB
27. 2.210 11.774 ↑ 1.0 8,938 1

Hash Join (cost=1,064.47..1,374.75 rows=8,938 width=18) (actual time=8.918..11.774 rows=8,938 loops=1)

  • Hash Cond: (aeroportodestino.idcidade = cidadedestino.id)
28. 0.794 0.794 ↑ 1.0 8,938 1

Seq Scan on aeroporto aeroportodestino (cost=0.00..187.38 rows=8,938 width=12) (actual time=0.006..0.794 rows=8,938 loops=1)

29. 4.995 8.770 ↓ 1.0 30,912 1

Hash (cost=678.10..678.10 rows=30,910 width=14) (actual time=8.770..8.770 rows=30,912 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1711kB
30. 3.775 3.775 ↓ 1.0 30,912 1

Seq Scan on cidade cidadedestino (cost=0.00..678.10 rows=30,910 width=14) (actual time=0.005..3.775 rows=30,912 loops=1)

31. 1.571 16.447 ↑ 1.0 8,938 1

Hash (cost=1,374.75..1,374.75 rows=8,938 width=18) (actual time=16.446..16.447 rows=8,938 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 577kB
32. 2.218 14.876 ↑ 1.0 8,938 1

Hash Join (cost=1,064.47..1,374.75 rows=8,938 width=18) (actual time=11.274..14.876 rows=8,938 loops=1)

  • Hash Cond: (aeroportoorigem.idcidade = cidadeorigem.id)
33. 1.535 1.535 ↑ 1.0 8,938 1

Seq Scan on aeroporto aeroportoorigem (cost=0.00..187.38 rows=8,938 width=12) (actual time=0.012..1.535 rows=8,938 loops=1)

34. 4.958 11.123 ↓ 1.0 30,912 1

Hash (cost=678.10..678.10 rows=30,910 width=14) (actual time=11.122..11.123 rows=30,912 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1711kB
35. 6.165 6.165 ↓ 1.0 30,912 1

Seq Scan on cidade cidadeorigem (cost=0.00..678.10 rows=30,910 width=14) (actual time=0.015..6.165 rows=30,912 loops=1)

36. 0.073 0.151 ↑ 1.0 372 1

Hash (cost=7.72..7.72 rows=372 width=72) (actual time=0.151..0.151 rows=372 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
37. 0.078 0.078 ↑ 1.0 372 1

Seq Scan on cia (cost=0.00..7.72 rows=372 width=72) (actual time=0.019..0.078 rows=372 loops=1)

38. 35.504 35.504 ↑ 1.0 1 5,072

Index Scan using pk_requisicao_aereosol on aereosol asol_res (cost=0.43..0.46 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=5,072)

  • Index Cond: (id = aereo_res.idaereosol)
39.          

SubPlan (for Nested Loop)

40. 5.072 10.144 ↑ 1.0 1 5,072

Aggregate (cost=2.44..2.45 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=5,072)

41. 5.072 5.072 ↓ 0.0 0 5,072

Index Scan using transacao_cartao_idx2 on transacao_cartao (cost=0.42..2.44 rows=1 width=2) (actual time=0.001..0.001 rows=0 loops=5,072)

  • Index Cond: (id_orcamento = orc_res.id)
42. 45.756 1,742.428 ↑ 8.6 2,768 1

Gather (cost=3,740.83..773,664.73 rows=23,912 width=375) (actual time=389.592..1,742.428 rows=2,768 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
43. 109.805 1,691.136 ↑ 10.1 395 7

Nested Loop Semi Join (cost=2,740.83..710,911.99 rows=3,985 width=280) (actual time=416.482..1,691.136 rows=395 loops=7)

44. 32.026 1,581.310 ↑ 1.5 5,175 7

Nested Loop (cost=2,740.40..701,092.29 rows=7,971 width=284) (actual time=125.883..1,581.310 rows=5,175 loops=7)

45. 1.715 1,549.278 ↑ 1.5 5,175 7

Hash Join (cost=2,739.97..697,025.63 rows=7,971 width=268) (actual time=125.825..1,549.278 rows=5,175 loops=7)

  • Hash Cond: (aereo_1.idcia = cia_1.id)
46. 2.110 1,547.414 ↑ 1.5 5,175 7

Hash Join (cost=2,727.60..696,903.66 rows=7,971 width=204) (actual time=125.554..1,547.414 rows=5,175 loops=7)

  • Hash Cond: (aeroportodestino_1.idcidade = cidadedestino_1.id)
47. 2.133 1,534.522 ↑ 1.5 5,175 7

Hash Join (cost=1,663.12..695,729.59 rows=7,971 width=198) (actual time=114.627..1,534.522 rows=5,175 loops=7)

  • Hash Cond: (aeroportoorigem_1.idcidade = cidadeorigem_1.id)
48. 2.189 1,522.663 ↑ 1.5 5,175 7

Hash Join (cost=598.65..694,555.51 rows=7,971 width=192) (actual time=104.746..1,522.663 rows=5,175 loops=7)

  • Hash Cond: (aereo_1.idaeroportodestino = aeroportodestino_1.id)
49. 2.455 1,517.400 ↑ 1.5 5,175 7

Hash Join (cost=299.54..694,146.80 rows=7,971 width=188) (actual time=101.587..1,517.400 rows=5,175 loops=7)

  • Hash Cond: (aereo_1.idaeroportoorigem = aeroportoorigem_1.id)
50. 179.534 1,512.264 ↑ 1.5 5,175 7

Nested Loop (cost=0.44..693,738.10 rows=7,971 width=184) (actual time=98.811..1,512.264 rows=5,175 loops=7)

51. 1,332.722 1,332.722 ↑ 1.1 20,375 7

Parallel Seq Scan on solicitacao soli_1 (cost=0.00..403,111.72 rows=23,308 width=24) (actual time=10.424..1,332.722 rows=20,375 loops=7)

  • Filter: ((datacad >= to_timestamp('2019-10-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (datacad <= to_timestamp('2019-10-31 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (idstatus = ANY ('{8,3,4,5,6,1,2}'::integer[])))
  • Rows Removed by Filter: 1071470
52. 0.008 0.008 ↓ 0.0 0 142,628

Index Scan using aereo_idsv on aereo aereo_1 (cost=0.44..12.26 rows=21 width=164) (actual time=0.007..0.008 rows=0 loops=142,628)

  • Index Cond: (idsv = soli_1.id)
  • Filter: ((aprovado OR cancelado_emitido) AND (idorcamento IS NULL) AND (idaereo IS NULL))
  • Rows Removed by Filter: 2
53. 1.485 2.681 ↑ 1.0 8,938 7

Hash (cost=187.38..187.38 rows=8,938 width=12) (actual time=2.681..2.681 rows=8,938 loops=7)

  • Buckets: 16384 Batches: 1 Memory Usage: 513kB
54. 1.196 1.196 ↑ 1.0 8,938 7

Seq Scan on aeroporto aeroportoorigem_1 (cost=0.00..187.38 rows=8,938 width=12) (actual time=0.008..1.196 rows=8,938 loops=7)

55. 1.381 3.074 ↑ 1.0 8,938 7

Hash (cost=187.38..187.38 rows=8,938 width=12) (actual time=3.074..3.074 rows=8,938 loops=7)

  • Buckets: 16384 Batches: 1 Memory Usage: 513kB
56. 1.693 1.693 ↑ 1.0 8,938 7

Seq Scan on aeroporto aeroportodestino_1 (cost=0.00..187.38 rows=8,938 width=12) (actual time=0.017..1.693 rows=8,938 loops=7)

57. 5.480 9.726 ↓ 1.0 30,912 7

Hash (cost=678.10..678.10 rows=30,910 width=14) (actual time=9.726..9.726 rows=30,912 loops=7)

  • Buckets: 32768 Batches: 1 Memory Usage: 1711kB
58. 4.246 4.246 ↓ 1.0 30,912 7

Seq Scan on cidade cidadeorigem_1 (cost=0.00..678.10 rows=30,910 width=14) (actual time=0.006..4.246 rows=30,912 loops=7)

59. 5.049 10.782 ↓ 1.0 30,912 7

Hash (cost=678.10..678.10 rows=30,910 width=14) (actual time=10.782..10.782 rows=30,912 loops=7)

  • Buckets: 32768 Batches: 1 Memory Usage: 1711kB
60. 5.733 5.733 ↓ 1.0 30,912 7

Seq Scan on cidade cidadedestino_1 (cost=0.00..678.10 rows=30,910 width=14) (actual time=0.016..5.733 rows=30,912 loops=7)

61. 0.064 0.149 ↑ 1.0 372 7

Hash (cost=7.72..7.72 rows=372 width=72) (actual time=0.149..0.149 rows=372 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
62. 0.085 0.085 ↑ 1.0 372 7

Seq Scan on cia cia_1 (cost=0.00..7.72 rows=372 width=72) (actual time=0.021..0.085 rows=372 loops=7)

63. 0.006 0.006 ↑ 1.0 1 36,227

Index Scan using pk_requisicao_aereosol on aereosol asol (cost=0.43..0.50 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=36,227)

  • Index Cond: (id = aereo_1.idaereosol)
64. 0.021 0.021 ↓ 0.0 0 36,227

Index Only Scan using usuario_centro_custo_extrator_idx on usuario_centro_custo_extrator centro_1 (cost=0.43..1.38 rows=86 width=4) (actual time=0.021..0.021 rows=0 loops=36,227)

  • Index Cond: ((id_centro_custo = soli_1.idcentrocusto) AND (id_usuario = 2219789))
  • Heap Fetches: 0
65.          

SubPlan (for Gather)

66. 0.000 5.536 ↑ 1.0 1 2,768

Aggregate (cost=2.44..2.45 rows=1 width=2) (actual time=0.002..0.002 rows=1 loops=2,768)

67. 5.536 5.536 ↓ 0.0 0 2,768

Index Scan using transacao_cartao_idx3 on transacao_cartao transacao_cartao_1 (cost=0.42..2.44 rows=1 width=2) (actual time=0.002..0.002 rows=0 loops=2,768)

  • Index Cond: (id_aereo = aereo_1.id)
Planning time : 23.480 ms
Execution time : 3,872.277 ms