explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6HoM : new_2

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 2,048.648 ↑ 9.7 31 1

Subquery Scan on alerta_cotacao_pendente_new_2 (cost=1,448.95..19,059.03 rows=300 width=16) (actual time=2,026.021..2,048.648 rows=31 loops=1)

2. 0.008 2,048.637 ↑ 9.7 31 1

Limit (cost=1,448.95..19,056.03 rows=300 width=683) (actual time=2,026.020..2,048.637 rows=31 loops=1)

3. 0.041 2,048.629 ↑ 57.4 31 1

Nested Loop (cost=1,448.95..105,858.97 rows=1,779 width=683) (actual time=2,026.019..2,048.629 rows=31 loops=1)

4. 0.030 2,048.557 ↑ 57.4 31 1

Nested Loop (cost=1,448.66..105,318.93 rows=1,779 width=40) (actual time=2,026.009..2,048.557 rows=31 loops=1)

5. 0.103 2,048.465 ↑ 57.4 31 1

Hash Left Join (cost=1,448.38..104,778.89 rows=1,779 width=64) (actual time=2,025.995..2,048.465 rows=31 loops=1)

  • Hash Cond: (conversaodestino.id_aeroporto_filho = destinoconvertido.id)
  • Join Filter: (NOT (SubPlan 2))
6. 0.038 2,045.722 ↑ 57.4 31 1

Hash Left Join (cost=1,235.46..104,561.30 rows=1,779 width=64) (actual time=2,023.546..2,045.722 rows=31 loops=1)

  • Hash Cond: (conversaoorigem.id_aeroporto_filho = origemconvertido.id)
  • Join Filter: (NOT (SubPlan 1))
7. 0.020 2,043.178 ↑ 57.4 31 1

Nested Loop Left Join (cost=1,022.55..104,343.71 rows=1,779 width=64) (actual time=2,021.023..2,043.178 rows=31 loops=1)

8. 0.021 2,043.096 ↑ 57.4 31 1

Nested Loop (cost=1,022.41..104,047.98 rows=1,779 width=56) (actual time=2,021.016..2,043.096 rows=31 loops=1)

9. 0.025 2,042.982 ↑ 57.4 31 1

Nested Loop Left Join (cost=1,022.13..103,514.22 rows=1,779 width=48) (actual time=2,021.011..2,042.982 rows=31 loops=1)

10. 0.035 2,042.895 ↑ 57.4 31 1

Nested Loop (cost=1,022.00..103,218.48 rows=1,779 width=40) (actual time=2,021.000..2,042.895 rows=31 loops=1)

11. 0.074 2,042.767 ↑ 57.4 31 1

Nested Loop (cost=1,021.71..102,684.72 rows=1,779 width=32) (actual time=2,020.990..2,042.767 rows=31 loops=1)

12. 0.018 2,039.562 ↑ 57.4 31 1

Subquery Scan on acp (cost=1,021.28..98,178.32 rows=1,779 width=16) (actual time=2,020.968..2,039.562 rows=31 loops=1)

13. 324.970 2,039.544 ↑ 57.4 31 1

HashSetOp Except (cost=1,021.28..98,160.53 rows=1,779 width=20) (actual time=2,020.966..2,039.544 rows=31 loops=1)

14. 58.148 1,714.574 ↓ 376.4 683,632 1

Append (cost=1,021.28..98,151.45 rows=1,816 width=20) (actual time=1.211..1,714.574 rows=683,632 loops=1)

15. 41.881 279.927 ↓ 185.8 330,513 1

Subquery Scan on *SELECT* 1 (cost=1,021.28..56,967.29 rows=1,779 width=20) (actual time=1.210..279.927 rows=330,513 loops=1)

16. 0.000 238.046 ↓ 185.8 330,513 1

Gather (cost=1,021.28..56,949.50 rows=1,779 width=16) (actual time=1.209..238.046 rows=330,513 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 24.689 325.034 ↓ 148.7 110,171 3 / 3

Hash Join (cost=21.28..55,771.60 rows=741 width=16) (actual time=42.104..325.034 rows=110,171 loops=3)

  • Hash Cond: (afc.id_companhia = com.id)
18. 9.705 300.167 ↓ 3.8 120,510 3 / 3

Nested Loop (cost=1.42..55,667.33 rows=31,870 width=16) (actual time=41.811..300.167 rows=120,510 loops=3)

19. 5.889 166.475 ↓ 3.7 12,399 3 / 3

Nested Loop (cost=0.85..46,002.11 rows=3,390 width=8) (actual time=41.760..166.475 rows=12,399 loops=3)

20. 70.952 70.952 ↑ 1.2 14,939 3 / 3

Parallel Index Scan using alerta_is_persistente_idx on alerta al_1 (cost=0.43..23,178.96 rows=17,547 width=16) (actual time=0.174..70.952 rows=14,939 loops=3)

  • Index Cond: (is_persistente = true)
  • Filter: ((data_ida >= (CURRENT_TIMESTAMP - '12:00:00'::interval)) AND (data_hora_ultima_pesquisa <= (CURRENT_TIMESTAMP - '00:05:00'::interval)))
  • Rows Removed by Filter: 53,033
21. 89.634 89.634 ↑ 1.0 1 44,817 / 3

Index Scan using usuario_pkey on usuario usu (cost=0.42..1.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=44,817)

  • Index Cond: (id = al_1.id_usuario)
  • Filter: is_ativo
  • Rows Removed by Filter: 0
22. 123.987 123.987 ↑ 4.6 10 37,196 / 3

Index Only Scan using alerta_filtro_companhia_pkey on alerta_filtro_companhia afc (cost=0.56..2.39 rows=46 width=16) (actual time=0.008..0.010 rows=10 loops=37,196)

  • Index Cond: (id_alerta = al_1.id)
  • Heap Fetches: 91,976
23. 0.008 0.178 ↑ 1.0 11 3 / 3

Hash (cost=19.73..19.73 rows=11 width=8) (actual time=0.177..0.178 rows=11 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.170 0.170 ↑ 1.0 11 3 / 3

Seq Scan on companhia com (cost=0.00..19.73 rows=11 width=8) (actual time=0.038..0.170 rows=11 loops=3)

  • Filter: is_send_odp
  • Rows Removed by Filter: 462
25. 44.785 1,376.499 ↓ 9,543.8 353,119 1

Subquery Scan on *SELECT* 2 (cost=23,773.10..41,175.07 rows=37 width=20) (actual time=80.097..1,376.499 rows=353,119 loops=1)

26. 0.000 1,331.714 ↓ 9,543.8 353,119 1

Gather (cost=23,773.10..41,174.70 rows=37 width=16) (actual time=80.096..1,331.714 rows=353,119 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
27. 42.897 1,428.793 ↓ 7,847.1 117,706 3 / 3

Nested Loop (cost=22,773.10..40,171.00 rows=15 width=16) (actual time=72.320..1,428.793 rows=117,706 loops=3)

28. 112.799 914.281 ↓ 1,492.5 117,904 3 / 3

Hash Join (cost=22,772.68..40,068.26 rows=79 width=24) (actual time=72.292..914.281 rows=117,904 loops=3)

  • Hash Cond: (co.id_companhia = com_1.id)
  • Join Filter: CASE WHEN ((com_1.intervalo_atualizacao = 0) AND (co.data_hora_cotacao >= (CURRENT_TIMESTAMP - '05:00:00'::interval))) THEN true WHEN (co.data_hora_cotacao >= (CURRENT_TIMESTAMP - (((com_1.intervalo_atualizacao)::text || ' minutes'::text))::interval)) THEN true ELSE false END
  • Rows Removed by Join Filter: 3,874
29. 41.122 801.269 ↓ 18.8 127,460 3 / 3

Nested Loop (cost=22,752.81..40,030.31 rows=6,778 width=32) (actual time=71.934..801.269 rows=127,460 loops=3)

30. 120.602 250.307 ↓ 18.8 127,460 3 / 3

Parallel Hash Join (cost=22,752.38..36,203.97 rows=6,778 width=24) (actual time=71.901..250.307 rows=127,460 loops=3)

  • Hash Cond: (ac.id_alerta = al_2.id)
31. 58.042 58.042 ↑ 1.2 408,850 3 / 3

Parallel Seq Scan on alerta_cotacao ac (cost=0.00..12,111.00 rows=510,700 width=16) (actual time=0.012..58.042 rows=408,850 loops=3)

32. 4.898 71.663 ↑ 1.1 15,787 3 / 3

Parallel Hash (cost=22,532.99..22,532.99 rows=17,551 width=16) (actual time=71.663..71.663 rows=15,787 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,784kB
33. 66.765 66.765 ↑ 1.1 15,787 3 / 3

Parallel Index Scan using alerta_is_persistente_idx on alerta al_2 (cost=0.43..22,532.99 rows=17,551 width=16) (actual time=0.477..66.765 rows=15,787 loops=3)

  • Index Cond: (is_persistente = true)
  • Filter: (data_ida >= (CURRENT_TIMESTAMP - '12:00:00'::interval))
  • Rows Removed by Filter: 52,185
34. 509.840 509.840 ↑ 1.0 1 382,380 / 3

Index Scan using cotacao_pkey on cotacao co (cost=0.43..0.56 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=382,380)

  • Index Cond: (id = ac.id_cotacao)
35. 0.009 0.213 ↑ 1.0 11 3 / 3

Hash (cost=19.73..19.73 rows=11 width=12) (actual time=0.213..0.213 rows=11 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.204 0.204 ↑ 1.0 11 3 / 3

Seq Scan on companhia com_1 (cost=0.00..19.73 rows=11 width=12) (actual time=0.039..0.204 rows=11 loops=3)

  • Filter: is_send_odp
  • Rows Removed by Filter: 462
37. 471.615 471.615 ↑ 1.0 1 353,711 / 3

Index Scan using usuario_pkey on usuario usu_1 (cost=0.42..1.30 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=353,711)

  • Index Cond: (id = al_2.id_usuario)
  • Filter: is_ativo
  • Rows Removed by Filter: 0
38. 3.131 3.131 ↑ 1.0 1 31

Index Scan using alerta_pkey on alerta al (cost=0.43..2.53 rows=1 width=24) (actual time=0.101..0.101 rows=1 loops=31)

  • Index Cond: (id = acp.alerta_id)
39. 0.093 0.093 ↑ 1.0 1 31

Index Scan using aeroporto_pkey on aeroporto origemnovo (cost=0.28..0.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=31)

  • Index Cond: (id = al.id_aeroporto_origem)
40. 0.062 0.062 ↓ 0.0 0 31

Index Only Scan using pk_tabela_conversao_aeroporto_pai_filho_companhia on tabela_conversao_aeroporto_pai_filho_companhia conversaoorigem (cost=0.14..0.16 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=31)

  • Index Cond: ((id_companhia = acp.companhia_id) AND (id_aeroporto_pai = origemnovo.id))
  • Heap Fetches: 0
41. 0.093 0.093 ↑ 1.0 1 31

Index Scan using aeroporto_pkey on aeroporto destinonovo (cost=0.28..0.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=31)

  • Index Cond: (id = al.id_aeroporto_destino)
42. 0.062 0.062 ↓ 0.0 0 31

Index Only Scan using pk_tabela_conversao_aeroporto_pai_filho_companhia on tabela_conversao_aeroporto_pai_filho_companhia conversaodestino (cost=0.14..0.16 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=31)

  • Index Cond: ((id_companhia = acp.companhia_id) AND (id_aeroporto_pai = destinonovo.id))
  • Heap Fetches: 0
43. 0.941 2.506 ↑ 1.0 5,863 1

Hash (cost=139.63..139.63 rows=5,863 width=16) (actual time=2.506..2.506 rows=5,863 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 339kB
44. 1.565 1.565 ↑ 1.0 5,863 1

Seq Scan on aeroporto origemconvertido (cost=0.00..139.63 rows=5,863 width=16) (actual time=0.008..1.565 rows=5,863 loops=1)

45.          

SubPlan (for Hash Left Join)

46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using companhia_aeroporto_pkey on companhia_aeroporto coa (cost=0.29..51.75 rows=2,312 width=8) (never executed)

  • Index Cond: (id_companhia = acp.companhia_id)
  • Heap Fetches: 0
47. 0.899 2.435 ↑ 1.0 5,863 1

Hash (cost=139.63..139.63 rows=5,863 width=16) (actual time=2.434..2.435 rows=5,863 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 339kB
48. 1.536 1.536 ↑ 1.0 5,863 1

Seq Scan on aeroporto destinoconvertido (cost=0.00..139.63 rows=5,863 width=16) (actual time=0.006..1.536 rows=5,863 loops=1)

49.          

SubPlan (for Hash Left Join)

50. 0.205 0.205 ↑ 2.2 1,035 1

Index Only Scan using companhia_aeroporto_pkey on companhia_aeroporto coa_1 (cost=0.29..51.75 rows=2,312 width=8) (actual time=0.037..0.205 rows=1,035 loops=1)

  • Index Cond: (id_companhia = acp.companhia_id)
  • Heap Fetches: 0
51. 0.062 0.062 ↑ 1.0 1 31

Index Only Scan using aeroporto_pkey on aeroporto origempai (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=31)

  • Index Cond: (id = CASE WHEN (origemconvertido.id IS NOT NULL) THEN origemconvertido.id_aeroporto_pai ELSE origemnovo.id_aeroporto_pai END)
  • Heap Fetches: 0
52. 0.031 0.031 ↑ 1.0 1 31

Index Only Scan using aeroporto_pkey on aeroporto destinopai (cost=0.28..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=31)

  • Index Cond: (id = CASE WHEN (destinoconvertido.id IS NOT NULL) THEN destinoconvertido.id_aeroporto_pai ELSE destinonovo.id_aeroporto_pai END)
  • Heap Fetches: 0
Planning time : 2.899 ms
Execution time : 2,054.867 ms