explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Hp2 : Optimization for: plan #9wyE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.303 801.953 ↓ 1,000.0 1,000 1

Limit (cost=632,713.41..632,713.41 rows=1 width=417) (actual time=801.582..801.953 rows=1,000 loops=1)

2. 6.066 801.650 ↓ 2.7 2,000 1

Sort (cost=632,711.55..632,713.41 rows=744 width=417) (actual time=801.436..801.650 rows=2,000 loops=1)

  • Sort Key: aereo_res.id
  • Sort Method: top-N heapsort Memory: 742kB
3. 0.556 795.584 ↓ 10.5 7,840 1

Append (cost=479,828.24..632,676.07 rows=744 width=417) (actual time=549.698..795.584 rows=7,840 loops=1)

4. 14.310 648.442 ↓ 9.4 5,072 1

Nested Loop (cost=479,828.24..484,606.05 rows=540 width=433) (actual time=549.698..648.442 rows=5,072 loops=1)

5. 1.964 618.916 ↓ 9.4 5,072 1

Hash Join (cost=479,827.81..482,617.29 rows=540 width=277) (actual time=549.651..618.916 rows=5,072 loops=1)

  • Hash Cond: (aereo_res.idcia = cia.id)
6. 2.761 616.834 ↓ 9.4 5,072 1

Nested Loop (cost=479,815.44..482,597.49 rows=540 width=213) (actual time=549.518..616.834 rows=5,072 loops=1)

7. 2.814 609.001 ↓ 9.4 5,072 1

Nested Loop (cost=479,815.15..482,397.73 rows=540 width=207) (actual time=549.513..609.001 rows=5,072 loops=1)

8. 2.700 601.115 ↓ 9.4 5,072 1

Nested Loop (cost=479,814.87..482,197.97 rows=540 width=201) (actual time=549.504..601.115 rows=5,072 loops=1)

9. 2.912 593.343 ↓ 9.4 5,072 1

Nested Loop (cost=479,814.58..482,029.21 rows=540 width=197) (actual time=549.499..593.343 rows=5,072 loops=1)

10. 5.144 585.359 ↓ 9.4 5,072 1

Nested Loop (cost=479,814.30..481,860.46 rows=540 width=193) (actual time=549.491..585.359 rows=5,072 loops=1)

  • Join Filter: (orc.id = aereo_res.idorcamento)
11. 1.994 570.051 ↓ 4.9 2,541 1

Nested Loop (cost=479,813.86..481,377.08 rows=522 width=85) (actual time=549.478..570.051 rows=2,541 loops=1)

12. 1.514 560.434 ↓ 4.9 2,541 1

Nested Loop (cost=479,813.43..481,105.47 rows=522 width=69) (actual time=549.465..560.434 rows=2,541 loops=1)

13. 1.043 551.297 ↓ 4.9 2,541 1

GroupAggregate (cost=479,812.99..479,822.13 rows=522 width=12) (actual time=549.453..551.297 rows=2,541 loops=1)

  • Group Key: orc.id
14. 1.661 550.254 ↓ 9.7 5,072 1

Sort (cost=479,812.99..479,814.30 rows=522 width=4) (actual time=549.448..550.254 rows=5,072 loops=1)

  • Sort Key: orc.id
  • Sort Method: quicksort Memory: 430kB
15. 9.451 548.593 ↓ 9.7 5,072 1

Gather (cost=5,413.97..479,789.43 rows=522 width=4) (actual time=480.190..548.593 rows=5,072 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
16. 5.632 539.142 ↓ 8.3 725 7

Nested Loop (cost=4,413.97..478,737.23 rows=87 width=4) (actual time=354.049..539.142 rows=725 loops=7)

17. 1.537 533.499 ↓ 5.7 482 7

Nested Loop Semi Join (cost=4,413.53..478,660.29 rows=84 width=5) (actual time=259.422..533.499 rows=482 loops=7)

18. 101.939 531.960 ↓ 3.5 596 7

Hash Join (cost=4,413.10..478,388.59 rows=168 width=9) (actual time=259.376..531.960 rows=596 loops=7)

  • Hash Cond: (orc.idsv = soli.id)
19. 330.422 330.422 ↑ 1.2 925,402 7

Parallel Seq Scan on orcamento orc (cost=0.00..469,927.41 rows=1,079,041 width=9) (actual time=0.013..330.422 rows=925,402 loops=7)

20. 1.249 99.599 ↓ 7.2 8,626 7

Hash (cost=4,398.19..4,398.19 rows=1,193 width=8) (actual time=99.599..99.599 rows=8,626 loops=7)

  • Buckets: 16384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 465kB
21. 30.685 98.350 ↓ 7.2 8,626 7

Bitmap Heap Scan on solicitacao soli (cost=3,164.78..4,398.19 rows=1,193 width=8) (actual time=68.576..98.350 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=5837
22. 6.267 67.665 ↓ 0.0 0 7

BitmapAnd (cost=3,164.78..3,164.78 rows=1,197 width=0) (actual time=67.665..67.665 rows=0 loops=7)

23. 12.039 12.039 ↓ 1.1 67,198 7

Bitmap Index Scan on solicitacao_idx2 (cost=0.00..761.67 rows=63,765 width=0) (actual time=12.039..12.039 rows=67,198 loops=7)

  • Index Cond: (idcliente = 2214)
24. 49.359 49.359 ↓ 1.0 143,790 7

Bitmap Index Scan on idx2_solicitacao (cost=0.00..2,402.27 rows=143,583 width=0) (actual time=49.359..49.359 rows=143,790 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.26 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.011 0.011 ↑ 2.0 2 3,377

Index Scan using aereo_idx11 on aereo (cost=0.44..0.88 rows=4 width=5) (actual time=0.009..0.011 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.88 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.070 0.118 ↑ 1.0 372 1

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

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

Seq Scan on cia (cost=0.00..7.72 rows=372 width=72) (actual time=0.008..0.048 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=3.14..3.15 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..3.13 rows=2 width=2) (actual time=0.001..0.001 rows=0 loops=5,072)

  • Index Cond: (id_orcamento = orc_res.id)
40. 4.149 146.586 ↓ 13.6 2,768 1

Nested Loop Semi Join (cost=3,179.60..148,062.58 rows=204 width=375) (actual time=52.016..146.586 rows=2,768 loops=1)

41. 2.928 133.083 ↓ 8.1 3,293 1

Nested Loop (cost=3,179.17..146,896.33 rows=408 width=284) (actual time=51.978..133.083 rows=3,293 loops=1)

42. 1.148 123.569 ↓ 8.1 3,293 1

Hash Join (cost=3,178.74..146,688.20 rows=408 width=268) (actual time=51.969..123.569 rows=3,293 loops=1)

  • Hash Cond: (aereo_1.idcia = cia_1.id)
43. 1.890 122.312 ↓ 8.1 3,293 1

Nested Loop (cost=3,166.37..146,670.22 rows=408 width=204) (actual time=51.847..122.312 rows=3,293 loops=1)

44. 1.967 117.129 ↓ 8.1 3,293 1

Nested Loop (cost=3,166.08..146,519.29 rows=408 width=198) (actual time=51.843..117.129 rows=3,293 loops=1)

45. 1.917 111.869 ↓ 8.1 3,293 1

Nested Loop (cost=3,165.79..146,368.36 rows=408 width=192) (actual time=51.836..111.869 rows=3,293 loops=1)

46. 2.119 106.659 ↓ 8.1 3,293 1

Nested Loop (cost=3,165.51..146,240.84 rows=408 width=188) (actual time=51.831..106.659 rows=3,293 loops=1)

47. 4.784 101.247 ↓ 8.1 3,293 1

Nested Loop (cost=3,165.22..146,113.32 rows=408 width=184) (actual time=51.824..101.247 rows=3,293 loops=1)

48. 11.021 61.959 ↓ 7.2 8,626 1

Bitmap Heap Scan on solicitacao soli_1 (cost=3,164.78..4,398.19 rows=1,193 width=24) (actual time=51.781..61.959 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=5837
49. 4.410 50.938 ↓ 0.0 0 1

BitmapAnd (cost=3,164.78..3,164.78 rows=1,197 width=0) (actual time=50.938..50.938 rows=0 loops=1)

50. 9.955 9.955 ↓ 1.1 67,198 1

Bitmap Index Scan on solicitacao_idx2 (cost=0.00..761.67 rows=63,765 width=0) (actual time=9.955..9.955 rows=67,198 loops=1)

  • Index Cond: (idcliente = 2214)
51. 36.573 36.573 ↓ 1.0 143,791 1

Bitmap Index Scan on idx2_solicitacao (cost=0.00..2,402.27 rows=143,583 width=0) (actual time=36.573..36.573 rows=143,791 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..118.57 rows=22 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.056 0.109 ↑ 1.0 372 1

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

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

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

59. 6.586 6.586 ↑ 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.002 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.26 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 : 9.511 ms
Execution time : 802.962 ms