explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GStx9 : new_1

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 2,019.281 ↑ 15.0 20 1

Subquery Scan on alerta_cotacao_pendente_new_1 (cost=1,448.95..29,771.55 rows=300 width=16) (actual time=1,999.384..2,019.281 rows=20 loops=1)

2. 0.008 2,019.272 ↑ 15.0 20 1

Limit (cost=1,448.95..29,768.55 rows=300 width=683) (actual time=1,999.383..2,019.272 rows=20 loops=1)

3. 0.035 2,019.264 ↑ 48.5 20 1

Nested Loop (cost=1,448.95..93,015.67 rows=970 width=683) (actual time=1,999.382..2,019.264 rows=20 loops=1)

4. 0.037 2,019.209 ↑ 48.5 20 1

Nested Loop (cost=1,448.66..92,721.21 rows=970 width=40) (actual time=1,999.374..2,019.209 rows=20 loops=1)

5. 0.030 2,019.152 ↑ 48.5 20 1

Hash Left Join (cost=1,448.38..92,426.76 rows=970 width=64) (actual time=1,999.365..2,019.152 rows=20 loops=1)

  • Hash Cond: (conversaodestino.id_aeroporto_filho = destinoconvertido.id)
  • Join Filter: (NOT (SubPlan 2))
6. 0.032 2,017.737 ↑ 48.5 20 1

Hash Left Join (cost=1,235.46..92,211.29 rows=970 width=64) (actual time=1,997.967..2,017.737 rows=20 loops=1)

  • Hash Cond: (conversaoorigem.id_aeroporto_filho = origemconvertido.id)
  • Join Filter: (NOT (SubPlan 1))
7. 0.019 2,016.211 ↑ 48.5 20 1

Nested Loop Left Join (cost=1,022.55..91,995.83 rows=970 width=64) (actual time=1,996.457..2,016.211 rows=20 loops=1)

8. 0.018 2,016.172 ↑ 48.5 20 1

Nested Loop (cost=1,022.41..91,833.58 rows=970 width=56) (actual time=1,996.453..2,016.172 rows=20 loops=1)

9. 0.029 2,016.114 ↑ 48.5 20 1

Nested Loop Left Join (cost=1,022.13..91,542.54 rows=970 width=48) (actual time=1,996.447..2,016.114 rows=20 loops=1)

10. 0.031 2,016.045 ↑ 48.5 20 1

Nested Loop (cost=1,022.00..91,380.29 rows=970 width=40) (actual time=1,996.439..2,016.045 rows=20 loops=1)

11. 0.057 2,015.954 ↑ 48.5 20 1

Nested Loop (cost=1,021.71..91,089.26 rows=970 width=32) (actual time=1,996.431..2,015.954 rows=20 loops=1)

12. 0.015 2,012.777 ↑ 48.5 20 1

Subquery Scan on acp (cost=1,021.28..88,582.79 rows=970 width=16) (actual time=1,996.413..2,012.777 rows=20 loops=1)

13. 286.992 2,012.762 ↑ 48.5 20 1

HashSetOp Except (cost=1,021.28..88,573.09 rows=970 width=20) (actual time=1,996.410..2,012.762 rows=20 loops=1)

14. 51.209 1,725.770 ↓ 669.4 689,472 1

Append (cost=1,021.28..88,567.94 rows=1,030 width=20) (actual time=1.551..1,725.770 rows=689,472 loops=1)

15. 36.254 284.031 ↓ 343.7 333,413 1

Subquery Scan on *SELECT* 1 (cost=1,021.28..44,757.27 rows=970 width=20) (actual time=1.550..284.031 rows=333,413 loops=1)

16. 0.000 247.777 ↓ 343.7 333,413 1

Gather (cost=1,021.28..44,747.57 rows=970 width=16) (actual time=1.549..247.777 rows=333,413 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 23.403 308.604 ↓ 275.1 111,138 3 / 3

Hash Join (cost=21.28..43,650.57 rows=404 width=16) (actual time=39.181..308.604 rows=111,138 loops=3)

  • Hash Cond: (afc.id_companhia = com.id)
18. 13.760 285.022 ↓ 7.0 121,575 3 / 3

Nested Loop (cost=1.42..43,584.67 rows=17,382 width=16) (actual time=38.900..285.022 rows=121,575 loops=3)

19. 12.611 158.696 ↓ 6.8 12,507 3 / 3

Nested Loop (cost=0.85..38,068.74 rows=1,849 width=8) (actual time=38.852..158.696 rows=12,507 loops=3)

20. 70.705 70.705 ↓ 1.6 15,076 3 / 3

Parallel Index Scan using alerta_is_persistente_idx on alerta al_1 (cost=0.43..23,609.61 rows=9,570 width=16) (actual time=0.256..70.705 rows=15,076 loops=3)

  • Index Cond: (is_persistente = true)
  • Filter: (((data_ida + '30:00:00'::interval) > CURRENT_TIMESTAMP) AND (date_part('epoch'::text, (CURRENT_TIMESTAMP - (data_hora_ultima_pesquisa)::timestamp with time zone)) >= '300'::double precision))
  • Rows Removed by Filter: 52896
21. 75.380 75.380 ↑ 1.0 1 45,228 / 3

Index Scan using usuario_pkey on usuario usu (cost=0.42..1.51 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=45,228)

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

Index Only Scan using alerta_filtro_companhia_pkey on alerta_filtro_companhia afc (cost=0.56..2.52 rows=46 width=16) (actual time=0.007..0.009 rows=10 loops=37,522)

  • Index Cond: (id_alerta = al_1.id)
  • Heap Fetches: 93455
23. 0.008 0.179 ↑ 1.0 11 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.171 0.171 ↑ 1.0 11 3 / 3

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

  • Filter: is_send_odp
  • Rows Removed by Filter: 462
25. 40.914 1,390.530 ↓ 5,934.3 356,059 1

Subquery Scan on *SELECT* 2 (cost=23,912.59..43,805.51 rows=60 width=20) (actual time=76.210..1,390.530 rows=356,059 loops=1)

26. 0.000 1,349.616 ↓ 5,934.3 356,059 1

Gather (cost=23,912.59..43,804.91 rows=60 width=16) (actual time=76.208..1,349.616 rows=356,059 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
27. 34.943 1,434.537 ↓ 4,747.4 118,686 3 / 3

Nested Loop (cost=22,912.59..42,798.91 rows=25 width=16) (actual time=68.619..1,434.537 rows=118,686 loops=3)

28. 149.308 924.059 ↓ 921.6 118,884 3 / 3

Hash Join (cost=22,912.16..42,652.41 rows=129 width=24) (actual time=68.589..924.059 rows=118,884 loops=3)

  • Hash Cond: (co.id_companhia = com_1.id)
  • Join Filter: CASE WHEN ((com_1.intervalo_atualizacao = 0) AND ((('now'::cstring)::timestamp without time zone - co.data_hora_cotacao) <= '05:00:00'::interval)) THEN true WHEN ((('now'::cstring)::timestamp without time zone - co.data_hora_cotacao) <= (((com_1.intervalo_atualizacao)::text || ' minutes'::text))::interval) THEN true ELSE false END
  • Rows Removed by Join Filter: 3931
29. 24.414 774.554 ↓ 11.6 128,564 3 / 3

Nested Loop (cost=22,892.30..42,602.89 rows=11,088 width=32) (actual time=68.252..774.554 rows=128,564 loops=3)

30. 112.341 235.885 ↓ 11.6 128,564 3 / 3

Parallel Hash Join (cost=22,891.86..36,343.45 rows=11,088 width=24) (actual time=68.220..235.885 rows=128,564 loops=3)

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

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

32. 4.776 67.840 ↑ 1.8 15,927 3 / 3

Parallel Hash (cost=22,532.99..22,532.99 rows=28,710 width=16) (actual time=67.840..67.840 rows=15,927 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 3296kB
33. 63.064 63.064 ↑ 1.8 15,927 3 / 3

Parallel Index Scan using alerta_is_persistente_idx on alerta al_2 (cost=0.43..22,532.99 rows=28,710 width=16) (actual time=1.111..63.064 rows=15,927 loops=3)

  • Index Cond: (is_persistente = true)
  • Filter: ((data_ida + '30:00:00'::interval) > CURRENT_TIMESTAMP)
  • Rows Removed by Filter: 52045
34. 514.255 514.255 ↑ 1.0 1 385,691 / 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=385,691)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.190 0.190 ↑ 1.0 11 3 / 3

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

  • Filter: is_send_odp
  • Rows Removed by Filter: 462
37. 475.535 475.535 ↑ 1.0 1 356,651 / 3

Index Scan using usuario_pkey on usuario usu_1 (cost=0.42..1.14 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=356,651)

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

Index Scan using alerta_pkey on alerta al (cost=0.43..2.58 rows=1 width=24) (actual time=0.156..0.156 rows=1 loops=20)

  • Index Cond: (id = acp.alerta_id)
39. 0.060 0.060 ↑ 1.0 1 20

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=20)

  • Index Cond: (id = al.id_aeroporto_origem)
40. 0.040 0.040 ↓ 0.0 0 20

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=20)

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

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

  • Index Cond: (id = al.id_aeroporto_destino)
42. 0.020 0.020 ↓ 0.0 0 20

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.001..0.001 rows=0 loops=20)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 339kB
44. 0.903 0.903 ↑ 1.0 5,863 1

Seq Scan on aeroporto origemconvertido (cost=0.00..139.63 rows=5,863 width=16) (actual time=0.008..0.903 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.595 1.385 ↑ 1.0 5,863 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 339kB
48. 0.790 0.790 ↑ 1.0 5,863 1

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

49.          

SubPlan (for Hash Left Join)

50. 0.000 0.000 ↓ 0.0 0

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

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

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

  • 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.020 0.020 ↑ 1.0 1 20

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=20)

  • 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 : 3.809 ms
Execution time : 2,027.662 ms