explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oMX0 : Optimization for: plan #9wyE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.318 863.868 ↓ 1,000.0 1,000 1

Limit (cost=650,918.22..650,918.22 rows=1 width=417) (actual time=863.459..863.868 rows=1,000 loops=1)

2. 6.122 863.550 ↓ 2.3 2,000 1

Sort (cost=650,916.06..650,918.22 rows=861 width=417) (actual time=863.312..863.550 rows=2,000 loops=1)

  • Sort Key: aereo_res.id
  • Sort Method: top-N heapsort Memory: 742kB
3. 0.559 857.428 ↓ 9.1 7,840 1

Append (cost=479,563.19..650,874.09 rows=861 width=417) (actual time=607.257..857.428 rows=7,840 loops=1)

4. 14.640 706.996 ↓ 8.1 5,072 1

Nested Loop (cost=479,563.19..484,636.66 rows=626 width=433) (actual time=607.257..706.996 rows=5,072 loops=1)

5. 1.959 677.140 ↓ 8.1 5,072 1

Hash Join (cost=479,562.76..482,766.60 rows=626 width=277) (actual time=607.209..677.140 rows=5,072 loops=1)

  • Hash Cond: (aereo_res.idcia = cia.id)
6. 2.832 675.061 ↓ 8.1 5,072 1

Nested Loop (cost=479,550.39..482,745.62 rows=626 width=213) (actual time=607.075..675.061 rows=5,072 loops=1)

7. 2.845 667.157 ↓ 8.1 5,072 1

Nested Loop (cost=479,550.10..482,514.04 rows=626 width=207) (actual time=607.070..667.157 rows=5,072 loops=1)

8. 2.730 659.240 ↓ 8.1 5,072 1

Nested Loop (cost=479,549.82..482,282.46 rows=626 width=201) (actual time=607.062..659.240 rows=5,072 loops=1)

9. 2.855 651.438 ↓ 8.1 5,072 1

Nested Loop (cost=479,549.53..482,086.83 rows=626 width=197) (actual time=607.057..651.438 rows=5,072 loops=1)

10. 5.423 643.511 ↓ 8.1 5,072 1

Nested Loop (cost=479,549.25..481,891.20 rows=626 width=193) (actual time=607.051..643.511 rows=5,072 loops=1)

  • Join Filter: (orc.id = aereo_res.idorcamento)
11. 2.072 627.924 ↓ 4.2 2,541 1

Nested Loop (cost=479,548.81..481,353.69 rows=603 width=85) (actual time=607.038..627.924 rows=2,541 loops=1)

12. 1.668 618.229 ↓ 4.2 2,541 1

Nested Loop (cost=479,548.38..481,039.91 rows=603 width=69) (actual time=607.027..618.229 rows=2,541 loops=1)

13. 1.064 608.938 ↓ 4.2 2,541 1

GroupAggregate (cost=479,547.94..479,558.50 rows=603 width=12) (actual time=607.015..608.938 rows=2,541 loops=1)

  • Group Key: orc.id
14. 1.730 607.874 ↓ 8.4 5,072 1

Sort (cost=479,547.94..479,549.45 rows=603 width=4) (actual time=607.009..607.874 rows=5,072 loops=1)

  • Sort Key: orc.id
  • Sort Method: quicksort Memory: 430kB
15. 9.261 606.144 ↓ 8.4 5,072 1

Gather (cost=5,671.02..479,520.10 rows=603 width=4) (actual time=249.193..606.144 rows=5,072 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
16. 4.785 596.883 ↓ 7.2 725 7

Nested Loop (cost=4,671.02..478,459.80 rows=100 width=4) (actual time=387.805..596.883 rows=725 loops=7)

17. 1.669 592.088 ↓ 5.0 482 7

Nested Loop Semi Join (cost=4,670.58..478,374.30 rows=97 width=5) (actual time=334.287..592.088 rows=482 loops=7)

18. 104.283 590.417 ↓ 3.1 596 7

Hash Join (cost=4,670.15..478,060.80 rows=194 width=9) (actual time=334.230..590.417 rows=596 loops=7)

  • Hash Cond: (orc.idsv = soli.id)
19. 380.937 380.937 ↑ 1.2 924,153 7

Parallel Seq Scan on orcamento orc (cost=0.00..469,350.78 rows=1,076,778 width=9) (actual time=0.015..380.937 rows=924,153 loops=7)

20. 1.256 105.197 ↓ 6.3 8,626 7

Hash (cost=4,652.99..4,652.99 rows=1,373 width=8) (actual time=105.197..105.197 rows=8,626 loops=7)

  • Buckets: 16384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 465kB
21. 35.402 103.941 ↓ 6.3 8,626 7

Bitmap Heap Scan on solicitacao soli (cost=3,235.99..4,652.99 rows=1,373 width=8) (actual time=69.427..103.941 rows=8,626 loops=7)

  • Recheck Cond: ((idcliente = 2214) AND (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: 3
  • Heap Blocks: exact=5841
22. 6.115 68.539 ↓ 0.0 0 7

BitmapAnd (cost=3,235.99..3,235.99 rows=1,376 width=0) (actual time=68.539..68.539 rows=0 loops=7)

23. 12.109 12.109 ↑ 1.1 66,747 7

Bitmap Index Scan on solicitacao_idx2 (cost=0.00..896.37 rows=75,058 width=0) (actual time=12.109..12.109 rows=66,747 loops=7)

  • Index Cond: (idcliente = 2214)
24. 50.315 50.315 ↓ 1.0 146,354 7

Bitmap Index Scan on idx2_solicitacao (cost=0.00..2,338.69 rows=139,925 width=0) (actual time=50.315..50.315 rows=146,354 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)))
25. 0.002 0.002 ↑ 86.0 1 4,175

Index Only Scan using usuario_centro_custo_extrator_idx on usuario_centro_custo_extrator centro (cost=0.43..2.24 rows=86 width=4) (actual time=0.002..0.002 rows=1 loops=4,175)

  • Index Cond: ((id_centro_custo = soli.idcentrocusto) AND (id_usuario = 2219789))
  • Heap Fetches: 0
26. 0.010 0.010 ↑ 2.0 2 3,377

Index Scan using aereo_idx11 on aereo (cost=0.44..0.84 rows=4 width=5) (actual time=0.008..0.010 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
27. 7.623 7.623 ↑ 1.0 1 2,541

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

  • Index Cond: (id = orc.id)
28. 7.623 7.623 ↑ 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.003 rows=1 loops=2,541)

  • Index Cond: (id = orc_res.idsv)
29. 10.164 10.164 ↑ 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.003..0.004 rows=2 loops=2,541)

  • Index Cond: (idorcamento = orc_res.id)
  • Filter: ((idaereo IS NULL) AND (orc_res.aprovado OR cancelado_emitido))
30. 5.072 5.072 ↑ 1.0 1 5,072

Index Scan using aeroporto_pkey on aeroporto aeroportoorigem (cost=0.29..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5,072)

  • Index Cond: (id = aereo_res.idaeroportoorigem)
31. 5.072 5.072 ↑ 1.0 1 5,072

Index Scan using aeroporto_pkey on aeroporto aeroportodestino (cost=0.29..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5,072)

  • Index Cond: (id = aereo_res.idaeroportodestino)
32. 5.072 5.072 ↑ 1.0 1 5,072

Index Scan using cidade_pkey on cidade cidadeorigem (cost=0.29..0.36 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=5,072)

  • Index Cond: (id = aeroportoorigem.idcidade)
33. 5.072 5.072 ↑ 1.0 1 5,072

Index Scan using cidade_pkey on cidade cidadedestino (cost=0.29..0.36 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=5,072)

  • Index Cond: (id = aeroportodestino.idcidade)
34. 0.075 0.120 ↑ 1.0 372 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
35. 0.045 0.045 ↑ 1.0 372 1

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

36. 10.144 10.144 ↑ 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.002..0.002 rows=1 loops=5,072)

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

SubPlan (for Nested Loop)

38. 0.000 5.072 ↑ 1.0 1 5,072

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

39. 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)
40. 4.403 149.873 ↓ 11.8 2,768 1

Nested Loop Semi Join (cost=3,250.81..166,228.81 rows=235 width=375) (actual time=52.422..149.873 rows=2,768 loops=1)

41. 0.025 136.116 ↓ 7.0 3,293 1

Nested Loop (cost=3,250.38..164,885.91 rows=470 width=284) (actual time=52.386..136.116 rows=3,293 loops=1)

42. 1.077 126.212 ↓ 7.0 3,293 1

Hash Join (cost=3,249.94..164,646.12 rows=470 width=268) (actual time=52.375..126.212 rows=3,293 loops=1)

  • Hash Cond: (aereo_1.idcia = cia_1.id)
43. 2.041 125.027 ↓ 7.0 3,293 1

Nested Loop (cost=3,237.57..164,627.29 rows=470 width=204) (actual time=52.255..125.027 rows=3,293 loops=1)

44. 2.153 119.693 ↓ 7.0 3,293 1

Nested Loop (cost=3,237.29..164,453.42 rows=470 width=198) (actual time=52.250..119.693 rows=3,293 loops=1)

45. 1.935 114.247 ↓ 7.0 3,293 1

Nested Loop (cost=3,237.00..164,279.55 rows=470 width=192) (actual time=52.243..114.247 rows=3,293 loops=1)

46. 2.220 109.019 ↓ 7.0 3,293 1

Nested Loop (cost=3,236.71..164,132.65 rows=470 width=188) (actual time=52.238..109.019 rows=3,293 loops=1)

47. 6.199 103.506 ↓ 7.0 3,293 1

Nested Loop (cost=3,236.43..163,985.75 rows=470 width=184) (actual time=52.232..103.506 rows=3,293 loops=1)

48. 11.466 62.803 ↓ 6.3 8,626 1

Bitmap Heap Scan on solicitacao soli_1 (cost=3,235.99..4,652.99 rows=1,373 width=24) (actual time=52.189..62.803 rows=8,626 loops=1)

  • Recheck Cond: ((idcliente = 2214) AND (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: 3
  • Heap Blocks: exact=5841
49. 4.321 51.337 ↓ 0.0 0 1

BitmapAnd (cost=3,235.99..3,235.99 rows=1,376 width=0) (actual time=51.337..51.337 rows=0 loops=1)

50. 9.820 9.820 ↑ 1.1 66,747 1

Bitmap Index Scan on solicitacao_idx2 (cost=0.00..896.37 rows=75,058 width=0) (actual time=9.820..9.820 rows=66,747 loops=1)

  • Index Cond: (idcliente = 2214)
51. 37.196 37.196 ↓ 1.0 146,354 1

Bitmap Index Scan on idx2_solicitacao (cost=0.00..2,338.69 rows=139,925 width=0) (actual time=37.196..37.196 rows=146,354 loops=1)

  • 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)))
52. 34.504 34.504 ↓ 0.0 0 8,626

Index Scan using aereo_idsv on aereo aereo_1 (cost=0.44..115.84 rows=21 width=164) (actual time=0.003..0.004 rows=0 loops=8,626)

  • 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. 3.293 3.293 ↑ 1.0 1 3,293

Index Scan using aeroporto_pkey on aeroporto aeroportoorigem_1 (cost=0.29..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=3,293)

  • Index Cond: (id = aereo_1.idaeroportoorigem)
54. 3.293 3.293 ↑ 1.0 1 3,293

Index Scan using aeroporto_pkey on aeroporto aeroportodestino_1 (cost=0.29..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=3,293)

  • Index Cond: (id = aereo_1.idaeroportodestino)
55. 3.293 3.293 ↑ 1.0 1 3,293

Index Scan using cidade_pkey on cidade cidadeorigem_1 (cost=0.29..0.36 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=3,293)

  • Index Cond: (id = aeroportoorigem_1.idcidade)
56. 3.293 3.293 ↑ 1.0 1 3,293

Index Scan using cidade_pkey on cidade cidadedestino_1 (cost=0.29..0.36 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=3,293)

  • Index Cond: (id = aeroportodestino_1.idcidade)
57. 0.058 0.108 ↑ 1.0 372 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
58. 0.050 0.050 ↑ 1.0 372 1

Seq Scan on cia cia_1 (cost=0.00..7.72 rows=372 width=72) (actual time=0.009..0.050 rows=372 loops=1)

59. 9.879 9.879 ↑ 1.0 1 3,293

Index Scan using pk_requisicao_aereosol on aereosol asol (cost=0.43..0.50 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=3,293)

  • Index Cond: (id = aereo_1.idaereosol)
60. 6.586 6.586 ↑ 86.0 1 3,293

Index Only Scan using usuario_centro_custo_extrator_idx on usuario_centro_custo_extrator centro_1 (cost=0.43..2.24 rows=86 width=4) (actual time=0.002..0.002 rows=1 loops=3,293)

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

SubPlan (for Nested Loop Semi Join)

62. 0.000 2.768 ↑ 1.0 1 2,768

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

63. 2.768 2.768 ↓ 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.001..0.001 rows=0 loops=2,768)

  • Index Cond: (id_aereo = aereo_1.id)
Planning time : 8.991 ms
Execution time : 864.847 ms