explain.depesz.com

PostgreSQL's explain analyze made readable

Result: udlp

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 411.154 ↓ 37.5 75 1

Unique (cost=31,651.49..31,651.61 rows=2 width=673) (actual time=411.084..411.154 rows=75 loops=1)

2. 0.333 411.094 ↓ 37.5 75 1

Sort (cost=31,651.49..31,651.49 rows=2 width=673) (actual time=411.083..411.094 rows=75 loops=1)

  • Sort Key: per.descricao, per.produtoesperadoresultadoid, per.tiporesultadoid, tr.nome, ctr.nome, per.anoprevisaoalcance, per.statusalcanceresultadoid, star.descricao, prdesptiptrlano.produtoesperadotipotrlanoid, tiptrl.tipotrlid, tiptrl.denominacao (...)
  • Sort Method: quicksort Memory: 105kB
3. 5.574 410.761 ↓ 37.5 75 1

Hash Left Join (cost=23,212.09..31,651.48 rows=2 width=673) (actual time=357.087..410.761 rows=75 loops=1)

  • Hash Cond: (per.planoacaoid = pa.planoacaoid)
4. 0.041 98.743 ↓ 37.5 75 1

Nested Loop (cost=4,667.62..13,106.99 rows=2 width=587) (actual time=35.503..98.743 rows=75 loops=1)

5. 0.056 98.252 ↓ 75.0 75 1

Nested Loop (cost=4,667.62..13,098.57 rows=1 width=595) (actual time=35.473..98.252 rows=75 loops=1)

6. 0.055 98.121 ↓ 75.0 75 1

Nested Loop (cost=4,667.62..13,094.28 rows=1 width=599) (actual time=35.455..98.121 rows=75 loops=1)

7. 0.030 97.991 ↓ 75.0 75 1

Nested Loop (cost=4,667.62..13,090.00 rows=1 width=603) (actual time=35.450..97.991 rows=75 loops=1)

8. 0.392 97.736 ↓ 75.0 75 1

Hash Left Join (cost=4,667.62..13,081.71 rows=1 width=466) (actual time=35.436..97.736 rows=75 loops=1)

  • Hash Cond: (per.produtoesperadoresultadoid = prdesptiptrlano.produtoesperadoresultadoid)
  • Filter: ((per.anoprevisaoalcance = 2,020) OR (prdesptiptrlano.ano = 2,020))
9. 0.080 88.336 ↓ 40.2 201 1

Hash Join (cost=3,977.62..12,391.38 rows=5 width=421) (actual time=24.112..88.336 rows=201 loops=1)

  • Hash Cond: (per.tiporesultadoid = e.tiporesultadoid)
10. 0.553 88.233 ↓ 28.7 201 1

Hash Join (cost=3,976.05..12,389.74 rows=7 width=429) (actual time=24.083..88.233 rows=201 loops=1)

  • Hash Cond: (s.submissaoid = g.submissaoid)
11. 0.821 83.853 ↑ 1.2 3,596 1

Hash Left Join (cost=3,683.20..12,080.73 rows=4,292 width=425) (actual time=17.586..83.853 rows=3,596 loops=1)

  • Hash Cond: (per.statusalcanceresultadoid = star.statusalcanceresultadoid)
12. 3.547 83.017 ↑ 1.2 3,596 1

Hash Join (cost=3,635.85..11,972.74 rows=4,292 width=410) (actual time=17.562..83.017 rows=3,596 loops=1)

  • Hash Cond: (per.submissaoid = s.submissaoid)
13. 9.682 62.782 ↑ 2.2 6,965 1

Hash Join (cost=15.61..8,232.88 rows=15,338 width=406) (actual time=0.173..62.782 rows=6,965 loops=1)

  • Hash Cond: (per.tiporesultadoid = tr.tiporesultadoid)
14. 52.992 52.992 ↓ 1.0 81,079 1

Seq Scan on produtoesperadoresultado per (cost=0.00..7,767.73 rows=81,073 width=333) (actual time=0.010..52.992 rows=81,079 loops=1)

15. 0.002 0.108 ↑ 1.0 7 1

Hash (cost=15.52..15.52 rows=7 width=73) (actual time=0.108..0.108 rows=7 loops=1)

16. 0.005 0.106 ↑ 1.0 7 1

Nested Loop (cost=3.57..15.52 rows=7 width=73) (actual time=0.099..0.106 rows=7 loops=1)

17. 0.017 0.017 ↑ 1.0 1 1

Index Scan using categoriatiporesultado_pk on categoriatiporesultado ctr (cost=0.00..8.27 rows=1 width=30) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (categoriatiporesultadoid = 6)
18. 0.042 0.084 ↑ 1.0 7 1

Hash Join (cost=3.57..7.18 rows=7 width=47) (actual time=0.079..0.084 rows=7 loops=1)

  • Hash Cond: (tr.tiporesultadoid = tr.tiporesultadoid)
19. 0.014 0.014 ↑ 1.0 39 1

Seq Scan on tiporesultado tr (cost=0.00..3.39 rows=39 width=4) (actual time=0.009..0.014 rows=39 loops=1)

20. 0.001 0.028 ↑ 1.0 7 1

Hash (cost=3.49..3.49 rows=7 width=43) (actual time=0.028..0.028 rows=7 loops=1)

21. 0.027 0.027 ↑ 1.0 7 1

Seq Scan on tiporesultado tr (cost=0.00..3.49 rows=7 width=43) (actual time=0.014..0.027 rows=7 loops=1)

  • Filter: (categoriatiporesultadoid = 6)
22. 1.141 16.688 ↓ 3.1 6,016 1

Hash (cost=3,596.01..3,596.01 rows=1,939 width=4) (actual time=16.688..16.688 rows=6,016 loops=1)

23. 15.547 15.547 ↓ 3.1 6,016 1

Seq Scan on submissao s (cost=0.00..3,596.01 rows=1,939 width=4) (actual time=0.036..15.547 rows=6,016 loops=1)

  • Filter: ((codigo IS NOT NULL) AND (statussubmissaoid = ANY ('{16,17,18,24}'::integer[])))
24. 0.002 0.015 ↑ 150.9 11 1

Hash (cost=26.60..26.60 rows=1,660 width=19) (actual time=0.015..0.015 rows=11 loops=1)

25. 0.013 0.013 ↑ 150.9 11 1

Seq Scan on statusalcanceresultado star (cost=0.00..26.60 rows=1,660 width=19) (actual time=0.010..0.013 rows=11 loops=1)

26. 0.019 3.827 ↓ 14.3 43 1

Hash (cost=292.81..292.81 rows=3 width=4) (actual time=3.827..3.827 rows=43 loops=1)

27. 0.003 3.808 ↓ 14.3 43 1

Subquery Scan g (cost=292.75..292.81 rows=3 width=4) (actual time=3.799..3.808 rows=43 loops=1)

28. 0.046 3.805 ↓ 14.3 43 1

HashAggregate (cost=292.75..292.78 rows=3 width=162) (actual time=3.799..3.805 rows=43 loops=1)

29. 0.016 3.759 ↓ 14.3 43 1

Nested Loop (cost=266.91..292.70 rows=3 width=162) (actual time=2.967..3.759 rows=43 loops=1)

30. 0.015 3.700 ↓ 14.3 43 1

Nested Loop (cost=266.91..291.83 rows=3 width=141) (actual time=2.960..3.700 rows=43 loops=1)

31. 0.078 2.926 ↓ 23.0 69 1

HashAggregate (cost=266.91..266.94 rows=3 width=8) (actual time=2.914..2.926 rows=69 loops=1)

32. 2.741 2.848 ↓ 9.1 263 1

Bitmap Heap Scan on membrosubmissao ms (cost=4.87..266.77 rows=29 width=8) (actual time=0.159..2.848 rows=263 loops=1)

  • Recheck Cond: (instituicaoid = 95,654)
  • Filter: (((origempessoa)::text = 'sirh'::text) AND (funcaomembroid = ANY ('{1,2,3}'::integer[])))
33. 0.107 0.107 ↓ 9.5 674 1

Bitmap Index Scan on membrosubmissao_ix_09 (cost=0.00..4.86 rows=71 width=0) (actual time=0.107..0.107 rows=674 loops=1)

  • Index Cond: (instituicaoid = 95,654)
34. 0.759 0.759 ↑ 1.0 1 69

Index Scan using pk_submissao on submissao s (cost=0.00..8.28 rows=1 width=137) (actual time=0.011..0.011 rows=1 loops=69)

  • Index Cond: (s.submissaoid = ms.submissaoid)
  • Filter: (s.statussubmissaoid = ANY ('{16,17,18,24}'::integer[]))
35. 0.043 0.043 ↑ 1.0 1 43

Index Scan using pk_statussubmissao on statussubmissao ss (cost=0.00..0.28 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=43)

  • Index Cond: (ss.statussubmissaoid = s.statussubmissaoid)
36. 0.006 0.023 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=4) (actual time=0.023..0.023 rows=25 loops=1)

37. 0.017 0.017 ↑ 1.0 25 1

Seq Scan on tiporesultadoqualificavel e (cost=0.00..1.25 rows=25 width=4) (actual time=0.015..0.017 rows=25 loops=1)

38. 1.866 9.008 ↓ 1.0 7,632 1

Hash (cost=594.69..594.69 rows=7,625 width=49) (actual time=9.008..9.008 rows=7,632 loops=1)

39. 2.093 7.142 ↓ 1.0 7,632 1

Hash Join (cost=65.22..594.69 rows=7,625 width=49) (actual time=0.053..7.142 rows=7,632 loops=1)

  • Hash Cond: (prdesptiptrlano.statusalcanceresultadoid = staalcres.statusalcanceresultadoid)
40. 2.181 5.044 ↓ 1.0 7,632 1

Hash Join (cost=17.88..432.97 rows=7,625 width=34) (actual time=0.038..5.044 rows=7,632 loops=1)

  • Hash Cond: (prdesptiptrlano.tipotrlid = tiptrl.tipotrlid)
41. 2.845 2.845 ↓ 1.0 7,632 1

Seq Scan on produtoesperadotipotrlano prdesptiptrlano (cost=0.00..310.25 rows=7,625 width=24) (actual time=0.009..2.845 rows=7,632 loops=1)

42. 0.002 0.018 ↑ 35.0 10 1

Hash (cost=13.50..13.50 rows=350 width=14) (actual time=0.018..0.018 rows=10 loops=1)

43. 0.016 0.016 ↑ 35.0 10 1

Seq Scan on tipotrl tiptrl (cost=0.00..13.50 rows=350 width=14) (actual time=0.014..0.016 rows=10 loops=1)

44. 0.004 0.005 ↑ 150.9 11 1

Hash (cost=26.60..26.60 rows=1,660 width=19) (actual time=0.005..0.005 rows=11 loops=1)

45. 0.001 0.001 ↑ 150.9 11 1

Seq Scan on statusalcanceresultado staalcres (cost=0.00..26.60 rows=1,660 width=19) (actual time=0.001..0.001 rows=11 loops=1)

46. 0.225 0.225 ↑ 1.0 1 75

Index Scan using pk_submissao on submissao s (cost=0.00..8.28 rows=1 width=137) (actual time=0.003..0.003 rows=1 loops=75)

  • Index Cond: (s.submissaoid = s.submissaoid)
  • Filter: ((s.codigo IS NOT NULL) AND (s.statussubmissaoid = ANY ('{16,17,18,24}'::integer[])))
47. 0.075 0.075 ↑ 1.0 1 75

Index Scan using pk_statussubmissao on statussubmissao st (cost=0.00..4.27 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=75)

  • Index Cond: (st.statussubmissaoid = s.statussubmissaoid)
48. 0.075 0.075 ↑ 1.0 1 75

Index Scan using pk_tiposubmissao on tiposubmissao tp (cost=0.00..4.27 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=75)

  • Index Cond: (tp.tiposubmissaoid = s.tiposubmissaoid)
49. 0.450 0.450 ↑ 1.0 1 75

Index Scan using membrosubmissao_idx7 on membrosubmissao ms (cost=0.00..8.41 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=75)

  • Index Cond: ((ms.submissaoid = s.submissaoid) AND (ms.funcaomembroid = 1))
50. 12.700 306.444 ↓ 25,132.0 25,132 1

Hash (cost=18,544.46..18,544.46 rows=1 width=90) (actual time=306.444..306.444 rows=25,132 loops=1)

51. 5.586 293.744 ↓ 25,132.0 25,132 1

Nested Loop (cost=8,934.01..18,544.46 rows=1 width=90) (actual time=59.740..293.744 rows=25,132 loops=1)

52. 63.544 164.780 ↓ 20,563.0 61,689 1

Hash Join (cost=8,934.01..18,519.59 rows=3 width=98) (actual time=59.715..164.780 rows=61,689 loops=1)

  • Hash Cond: ((ms.planoacaoid = pa.planoacaoid) AND (ms.submissaoid = pa.submissaoid))
53. 42.393 47.110 ↑ 1.0 61,689 1

Bitmap Heap Scan on membrosubmissao ms (cost=1,165.34..7,808.19 rows=62,068 width=8) (actual time=5.448..47.110 rows=61,689 loops=1)

  • Recheck Cond: (funcaomembroid = 2)
54. 4.717 4.717 ↑ 1.0 61,689 1

Bitmap Index Scan on membrosubmissao_idx6 (cost=0.00..1,149.82 rows=62,068 width=0) (actual time=4.717..4.717 rows=61,689 loops=1)

  • Index Cond: (funcaomembroid = 2)
55. 23.871 54.126 ↓ 1.0 61,749 1

Hash (cost=5,937.47..5,937.47 rows=61,747 width=94) (actual time=54.126..54.126 rows=61,749 loops=1)

56. 30.255 30.255 ↓ 1.0 61,749 1

Seq Scan on planoacao pa (cost=0.00..5,937.47 rows=61,747 width=94) (actual time=0.020..30.255 rows=61,749 loops=1)

57. 123.378 123.378 ↓ 0.0 0 61,689

Index Scan using pk_submissao on submissao s (cost=0.00..8.28 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=61,689)

  • Index Cond: (s.submissaoid = pa.submissaoid)
  • Filter: ((s.codigo IS NOT NULL) AND (s.statussubmissaoid = ANY ('{16,17,18,24}'::integer[])))
Total runtime : 411.557 ms