explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Vq7

Settings
# exclusive inclusive rows x rows loops node
1. 1.524 412,912.790 ↑ 545.5 310 1

Unique (cost=164,169,912.25..164,178,790.21 rows=169,104 width=679) (actual time=412,911.132..412,912.790 rows=310 loops=1)

2.          

CTE internacoes

3. 0.434 882.143 ↑ 4.7 1,065 1

Hash Join (cost=21.86..6,966.31 rows=4,972 width=48) (actual time=35.874..882.143 rows=1,065 loops=1)

  • Hash Cond: (leito.id_quarto = quarto_1.id)
4. 0.585 877.542 ↑ 4.7 1,065 1

Hash Join (cost=7.01..6,883.09 rows=4,972 width=24) (actual time=31.691..877.542 rows=1,065 loops=1)

  • Hash Cond: (bol_int.id_leito = leito.id)
5. 861.577 861.577 ↑ 4.7 1,065 1

Seq Scan on hspbol_int bol_int (cost=0.00..6,807.72 rows=4,972 width=19) (actual time=16.287..861.577 rows=1,065 loops=1)

  • Filter: (((dthr_alta IS NULL) OR (dthr_alta <= '2019-02-01 00:00:00'::timestamp without time zone)) AND (dthr_baixa >= '2019-01-01 00:00:00'::timestamp without time zone) AND (tipo = '0'::numeric))
  • Rows Removed by Filter: 192922
6. 0.054 15.380 ↑ 1.0 178 1

Hash (cost=4.78..4.78 rows=178 width=13) (actual time=15.380..15.380 rows=178 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
7. 15.326 15.326 ↑ 1.0 178 1

Seq Scan on tableito leito (cost=0.00..4.78 rows=178 width=13) (actual time=15.096..15.326 rows=178 loops=1)

8. 0.032 4.167 ↑ 1.0 73 1

Hash (cost=13.94..13.94 rows=73 width=32) (actual time=4.167..4.167 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
9. 0.061 4.135 ↑ 1.0 73 1

Hash Join (cost=11.21..13.94 rows=73 width=32) (actual time=4.072..4.135 rows=73 loops=1)

  • Hash Cond: (quarto_1.id_setor = setor_1.id)
10. 0.020 0.020 ↑ 1.0 73 1

Seq Scan on tabquarto quarto_1 (cost=0.00..1.73 rows=73 width=8) (actual time=0.007..0.020 rows=73 loops=1)

11. 0.129 4.054 ↑ 1.0 276 1

Hash (cost=7.76..7.76 rows=276 width=28) (actual time=4.054..4.054 rows=276 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
12. 3.925 3.925 ↑ 1.0 276 1

Seq Scan on tabset setor_1 (cost=0.00..7.76 rows=276 width=28) (actual time=3.518..3.925 rows=276 loops=1)

13. 9.053 412,911.266 ↑ 136.4 1,240 1

Sort (cost=164,162,945.94..164,163,368.70 rows=169,104 width=679) (actual time=412,911.130..412,911.266 rows=1,240 loops=1)

  • Sort Key: pespront.nome, visitante.dt_inicial, visitante.nome, visitante.tipo_visitante, visitante.dt_final, visitante.id_pront, (count(visitante.id)), visitante.id_pes_visitante, visit_pes.nome, bol.cod, bol.id, bol.dthr_baixa, pront.cod, pesmed.n (...)
  • Sort Method: quicksort Memory: 388kB
14. 3.551 412,902.213 ↑ 136.4 1,240 1

Nested Loop Left Join (cost=3,596.04..164,097,395.83 rows=169,104 width=679) (actual time=127,364.767..412,902.213 rows=1,240 loops=1)

15. 2.069 411,704.542 ↑ 136.4 1,240 1

Hash Join (cost=3,471.70..143,066,776.87 rows=169,104 width=495) (actual time=126,481.812..411,704.542 rows=1,240 loops=1)

  • Hash Cond: (tpbol.id_setor = setor.id)
16. 7.695 411,702.226 ↑ 10.3 4,058 1

Hash Join (cost=3,456.84..143,066,495.36 rows=41,697 width=475) (actual time=112,547.854..411,702.226 rows=4,058 loops=1)

  • Hash Cond: (bh.id_tpbol = tpbol.id)
17. 148.551 411,694.508 ↑ 10.3 4,058 1

Merge Join (cost=3,455.44..143,065,920.62 rows=41,697 width=279) (actual time=112,547.799..411,694.508 rows=4,058 loops=1)

  • Merge Cond: (bol.id = bh.id_bol)
18. 4.738 376,132.530 ↑ 11.0 3,755 1

Nested Loop (cost=3,454.33..143,026,162.04 rows=41,252 width=275) (actual time=77,551.871..376,132.530 rows=3,755 loops=1)

19. 4.468 374,700.892 ↑ 11.0 3,755 1

Nested Loop (cost=3,453.91..142,898,789.84 rows=41,252 width=256) (actual time=77,413.719..374,700.892 rows=3,755 loops=1)

20. 7.880 374,144.439 ↑ 11.0 3,755 1

Nested Loop (cost=3,453.63..142,885,832.85 rows=41,252 width=256) (actual time=77,396.929..374,144.439 rows=3,755 loops=1)

21. 7.870 374,095.254 ↑ 11.0 3,755 1

Nested Loop (cost=3,453.49..142,878,911.14 rows=41,252 width=122) (actual time=77,370.628..374,095.254 rows=3,755 loops=1)

22. 9.696 369,923.089 ↑ 11.0 3,755 1

Nested Loop (cost=3,453.07..142,856,924.52 rows=41,252 width=103) (actual time=77,331.453..369,923.089 rows=3,755 loops=1)

23. 10.475 360,330.633 ↑ 11.0 3,755 1

Nested Loop Left Join (cost=3,452.65..142,824,159.38 rows=41,252 width=97) (actual time=77,289.609..360,330.633 rows=3,755 loops=1)

24. 140.692 344,567.933 ↑ 11.0 3,755 1

Nested Loop (cost=3,452.23..142,475,683.11 rows=41,252 width=74) (actual time=77,251.176..344,567.933 rows=3,755 loops=1)

25. 87,697.444 87,697.444 ↑ 4.7 8,703 1

Index Scan using hspbol_cluster_id on hspbol bol (cost=0.43..80,482.56 rows=41,252 width=30) (actual time=77,024.345..87,697.444 rows=8,703 loops=1)

  • Filter: (((dthr_alta IS NULL) OR (dthr_alta <= '2019-02-01 00:00:00'::timestamp without time zone)) AND (dthr_baixa >= '2019-01-01 00:00:00'::timestamp without time zone) AND ( (...)
  • Rows Removed by Filter: 1050245
26. 95.733 256,729.797 ↓ 0.0 0 8,703

HashAggregate (cost=3,451.81..3,451.82 rows=1 width=40) (actual time=29.498..29.499 rows=0 loops=8,703)

  • Group Key: visitante.nome, visitante.tipo_visitante, visitante.dt_inicial, visitante.dt_final, visitante.id_pront, visitante.id_pes_visitante
27. 256,634.064 256,634.064 ↓ 0.0 0 8,703

Seq Scan on tabvisitante visitante (cost=0.00..3,451.79 rows=1 width=40) (actual time=28.952..29.488 rows=0 loops=8,703)

  • Filter: ((id_bol = bol.id) AND (date(dt_inicial) >= '2019-01-01'::date) AND (date(dt_inicial) <= '2019-02-01'::date))
  • Rows Removed by Filter: 88924
28. 15,752.225 15,752.225 ↑ 1.0 1 3,755

Index Scan using tabpes_cluster_id on tabpes visit_pes (cost=0.42..8.44 rows=1 width=27) (actual time=4.195..4.195 rows=1 loops=3,755)

  • Index Cond: (id = visitante.id_pes_visitante)
29. 9,582.760 9,582.760 ↑ 1.0 1 3,755

Index Scan using hsppront_cluster_id on hsppront pront (cost=0.42..0.78 rows=1 width=14) (actual time=2.545..2.552 rows=1 loops=3,755)

  • Index Cond: (id = bol.id_pront)
30. 4,164.295 4,164.295 ↑ 1.0 1 3,755

Index Scan using tabpes_cluster_id on tabpes pespront (cost=0.42..0.52 rows=1 width=27) (actual time=1.109..1.109 rows=1 loops=3,755)

  • Index Cond: (id = pront.id_pes)
31. 41.305 41.305 ↑ 1.0 1 3,755

Index Scan using tabconv_cluster_id on tabconv conv (cost=0.14..0.16 rows=1 width=142) (actual time=0.010..0.011 rows=1 loops=3,755)

  • Index Cond: (id = bol.id_conv)
32. 551.985 551.985 ↑ 1.0 1 3,755

Index Scan using tabmedico_pk on tabmedico med (cost=0.28..0.30 rows=1 width=8) (actual time=0.146..0.147 rows=1 loops=3,755)

  • Index Cond: (id = bol.id_med_resp)
33. 1,426.900 1,426.900 ↑ 1.0 1 3,755

Index Scan using tabpes_cluster_id on tabpes pesmed (cost=0.42..3.08 rows=1 width=27) (actual time=0.379..0.380 rows=1 loops=3,755)

  • Index Cond: (id = med.id_pes)
34. 35,413.427 35,413.427 ↑ 1.0 1,039,708 1

Index Scan using hspbol_hist_indice_id_bol on hspbol_hist bh (cost=0.43..40,029.61 rows=1,070,365 width=8) (actual time=0.021..35,413.427 rows=1,039,708 loops=1)

35. 0.006 0.023 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=204) (actual time=0.023..0.023 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.017 0.017 ↑ 1.0 18 1

Seq Scan on tabtpbol tpbol (cost=0.00..1.18 rows=18 width=204) (actual time=0.013..0.017 rows=18 loops=1)

37. 0.013 0.247 ↑ 1.0 73 1

Hash (cost=13.94..13.94 rows=73 width=32) (actual time=0.247..0.247 rows=73 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
38. 0.038 0.234 ↑ 1.0 73 1

Hash Join (cost=11.21..13.94 rows=73 width=32) (actual time=0.198..0.234 rows=73 loops=1)

  • Hash Cond: (quarto.id_setor = setor.id)
39. 0.012 0.012 ↑ 1.0 73 1

Seq Scan on tabquarto quarto (cost=0.00..1.73 rows=73 width=4) (actual time=0.006..0.012 rows=73 loops=1)

40. 0.058 0.184 ↑ 1.0 276 1

Hash (cost=7.76..7.76 rows=276 width=28) (actual time=0.184..0.184 rows=276 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
41. 0.126 0.126 ↑ 1.0 276 1

Seq Scan on tabset setor (cost=0.00..7.76 rows=276 width=28) (actual time=0.019..0.126 rows=276 loops=1)

42. 1.240 1,194.120 ↑ 1.0 1 1,240

Limit (cost=124.34..124.34 rows=1 width=184) (actual time=0.963..0.963 rows=1 loops=1,240)

43. 4.960 1,192.880 ↑ 8.0 1 1,240

Sort (cost=124.34..124.36 rows=8 width=184) (actual time=0.962..0.962 rows=1 loops=1,240)

  • Sort Key: internacoes.dthr_baixa_int DESC
  • Sort Method: quicksort Memory: 25kB
44. 1,187.920 1,187.920 ↑ 4.0 2 1,240

CTE Scan on internacoes (cost=0.00..124.30 rows=8 width=184) (actual time=0.790..0.958 rows=2 loops=1,240)

  • Filter: ((visitante.dt_inicial > dthr_baixa_int) AND (id_bol = bol.id))
  • Rows Removed by Filter: 1063
Planning time : 470.790 ms
Execution time : 412,913.310 ms