explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MnYS

Settings
# exclusive inclusive rows x rows loops node
1. 3,075.582 9,048.454 ↓ 1.4 5,612,451 1

WindowAgg (cost=573,132.57..661,782.10 rows=3,939,979 width=67) (actual time=5,176.770..9,048.454 rows=5,612,451 loops=1)

2. 5,060.650 5,972.872 ↓ 1.4 5,612,451 1

Sort (cost=573,132.57..582,982.52 rows=3,939,979 width=30) (actual time=5,176.758..5,972.872 rows=5,612,451 loops=1)

  • Sort Key: rtsepebrbrav.br2_id, rtsepezocoshav.zo_id, rtsepezocoshav.value_num DESC
  • Sort Method: quicksort Memory: 635081kB
3. 621.312 912.222 ↓ 1.4 5,612,451 1

Hash Join (cost=46,293.88..141,512.67 rows=3,939,979 width=30) (actual time=101.676..912.222 rows=5,612,451 loops=1)

  • Hash Cond: (rtsepezocoshav.co_id = co.lo_id)
4. 42.529 205.346 ↑ 3.6 143,909 1

Merge Join (cost=43,753.71..93,101.17 rows=517,737 width=21) (actual time=16.051..205.346 rows=143,909 loops=1)

  • Merge Cond: ((rtsepezocoshav.zo_id = rtsepezocoshav_1.zo_id) AND (rtsepezocoshav.co_id = rtsepezocoshav_1.co_id))
5. 8.332 63.260 ↓ 1.0 143,909 1

Merge Append (cost=21,819.89..41,583.69 rows=143,123 width=25) (actual time=0.052..63.260 rows=143,909 loops=1)

  • Sort Key: rtsepezocoshav.zo_id, rtsepezocoshav.co_id
6. 54.896 54.896 ↓ 1.0 143,909 1

Index Scan using rtsepezocoshav_unique on rtsepezocoshav (cost=0.42..17,975.18 rows=143,122 width=25) (actual time=0.018..54.896 rows=143,909 loops=1)

  • Index Cond: ((se_id = 0) AND (pe_id = 5) AND (ad_id = 74))
7. 0.007 0.032 ↓ 0.0 0 1

Sort (cost=21,819.46..21,819.47 rows=1 width=25) (actual time=0.032..0.032 rows=0 loops=1)

  • Sort Key: "*SELECT* 2".zo_id, "*SELECT* 2".co_id
  • Sort Method: quicksort Memory: 25kB
8. 0.001 0.025 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=4.98..21,819.45 rows=1 width=25) (actual time=0.025..0.025 rows=0 loops=1)

9. 0.001 0.024 ↓ 0.0 0 1

Nested Loop (cost=4.98..21,819.44 rows=1 width=65) (actual time=0.024..0.024 rows=0 loops=1)

10.          

Initplan (forNested Loop)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtad (cost=0.00..2.95 rows=1 width=4) (never executed)

  • Filter: ((alias)::text = 'active'::text)
12. 0.000 0.023 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.61..19,965.53 rows=1 width=25) (actual time=0.023..0.023 rows=0 loops=1)

  • Join Filter: (av_1.pe_id = rtpe.id)
13. 0.005 0.023 ↓ 0.0 0 1

Hash Join (cost=1.19..7,749.62 rows=20,446 width=29) (actual time=0.023..0.023 rows=0 loops=1)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
14. 0.011 0.011 ↑ 143,122.0 1 1

Seq Scan on rtsepezocoshav av (cost=0.00..7,007.27 rows=143,122 width=29) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: ((se_id = 0) AND (ad_id = 74))
15. 0.001 0.007 ↓ 0.0 0 1

Hash (cost=1.18..1.18 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on rtpe (cost=0.00..1.18 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: (id = 5)
  • Rows Removed by Filter: 13
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepezocoshav_unique on rtsepezocoshav av_1 (cost=0.42..0.58 rows=1 width=24) (never executed)

  • Index Cond: ((se_id = av.se_id) AND (se_id = 0) AND (pe_id = 5) AND (zo_id = av.zo_id) AND (co_id = av.co_id) AND (sh_id = av.sh_id) AND (ad_id = av.ad_id) AND (ad_id = 74))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..1,850.94 rows=2 width=12) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Index Scan using rtsepecoav_ad_id_pe_id_co_id_index on rtsepecoav (cost=0.42..0.48 rows=1 width=12) (never executed)

  • Index Cond: ((ad_id = $0) AND (pe_id = 5) AND (co_id = av.co_id))
  • Filter: ((value_num = '1'::numeric) AND (se_id = 0))
20. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_3 (cost=884.77..1,850.46 rows=1 width=12) (never executed)

  • Filter: (av.co_id = "*SELECT* 2_3".co_id)
21. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=884.77..1,850.37 rows=7 width=84) (never executed)

  • Join Filter: (av_1_1.pe_id = rtpe_1.id)
22.          

Initplan (forNested Loop Anti Join)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtad rtad_1 (cost=0.00..2.95 rows=1 width=4) (never executed)

  • Filter: ((alias)::text = 'active'::text)
24. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=881.41..1,801.39 rows=14 width=16) (never executed)

  • Join Filter: (av_1_1_1.pe_id = rtpe_1_1.id)
25. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=880.99..1,712.62 rows=27 width=32) (never executed)

  • Hash Cond: (av_2.co_id = av_1_2.co_id)
26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=30.37..845.32 rows=424 width=16) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtpe rtpe_1 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: (id = 5)
28. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on rtsepecoav av_2 (cost=30.37..843.43 rows=71 width=16) (never executed)

  • Recheck Cond: ((ad_id = $0) AND (se_id = 0) AND (pe_id = rtpe_1.baseline_id))
  • Filter: (value_num = '1'::numeric)
29. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on rtsepecoav_unique (cost=0.00..30.36 rows=475 width=0) (never executed)

  • Index Cond: ((ad_id = $0) AND (se_id = 0) AND (pe_id = rtpe_1.baseline_id))
30. 0.000 0.000 ↓ 0.0 0

Hash (cost=845.32..845.32 rows=424 width=16) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=30.37..845.32 rows=424 width=16) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtpe rtpe_1_1 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: (id = 5)
33. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on rtsepecoav av_1_2 (cost=30.37..843.43 rows=71 width=16) (never executed)

  • Recheck Cond: ((ad_id = $1) AND (se_id = 0) AND (pe_id = rtpe_1_1.baseline_id))
  • Filter: (value_num = '1'::numeric)
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on rtsepecoav_unique (cost=0.00..30.36 rows=475 width=0) (never executed)

  • Index Cond: ((ad_id = $1) AND (se_id = 0) AND (pe_id = rtpe_1_1.baseline_id))
35. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepecoav_unique on rtsepecoav av_1_1_1 (cost=0.42..3.29 rows=1 width=16) (never executed)

  • Index Cond: ((ad_id = av_1_2.ad_id) AND (ad_id = $1) AND (se_id = av_1_2.se_id) AND (se_id = 0) AND (pe_id = 5) AND (co_id = av_1_2.co_id))
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepecoav_unique on rtsepecoav av_1_1 (cost=0.42..3.29 rows=1 width=16) (never executed)

  • Index Cond: ((ad_id = av_2.ad_id) AND (ad_id = $0) AND (se_id = av_2.se_id) AND (se_id = 0) AND (pe_id = 5) AND (co_id = av_2.co_id))
  • Heap Fetches: 0
37. 14.766 99.557 ↑ 1.0 143,909 1

Materialize (cost=21,933.81..42,103.21 rows=144,697 width=16) (actual time=15.997..99.557 rows=143,909 loops=1)

38. 8.940 84.791 ↑ 1.0 143,909 1

Merge Append (cost=21,933.81..41,741.47 rows=144,697 width=16) (actual time=15.995..84.791 rows=143,909 loops=1)

  • Sort Key: rtsepezocoshav_1.zo_id, rtsepezocoshav_1.co_id
39. 59.867 59.867 ↑ 1.0 143,909 1

Index Only Scan using rtsepezocoshav_unique on rtsepezocoshav rtsepezocoshav_1 (cost=0.42..17,999.36 rows=144,696 width=16) (actual time=0.009..59.867 rows=143,909 loops=1)

  • Index Cond: ((se_id = 0) AND (pe_id = 5) AND (ad_id = 75))
  • Heap Fetches: 143909
40. 0.002 15.984 ↓ 0.0 0 1

Sort (cost=21,933.38..21,933.39 rows=1 width=16) (actual time=15.984..15.984 rows=0 loops=1)

  • Sort Key: "*SELECT* 2_1".zo_id, "*SELECT* 2_1".co_id
  • Sort Method: quicksort Memory: 25kB
41. 0.000 15.982 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=4.98..21,933.37 rows=1 width=16) (actual time=15.982..15.982 rows=0 loops=1)

42. 0.000 15.982 ↓ 0.0 0 1

Nested Loop (cost=4.98..21,933.36 rows=1 width=92) (actual time=15.982..15.982 rows=0 loops=1)

43.          

Initplan (forNested Loop)

44. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtad rtad_2 (cost=0.00..2.95 rows=1 width=4) (never executed)

  • Filter: ((alias)::text = 'active'::text)
45. 0.002 15.982 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.61..20,079.45 rows=1 width=16) (actual time=15.982..15.982 rows=0 loops=1)

  • Join Filter: (av_1_3.pe_id = rtpe_2.id)
46. 0.006 15.980 ↓ 0.0 0 1

Hash Join (cost=1.19..7,757.78 rows=20,671 width=24) (actual time=15.980..15.980 rows=0 loops=1)

  • Hash Cond: (av_3.pe_id = rtpe_2.baseline_id)
47. 15.967 15.967 ↑ 144,696.0 1 1

Seq Scan on rtsepezocoshav av_3 (cost=0.00..7,007.27 rows=144,696 width=24) (actual time=15.967..15.967 rows=1 loops=1)

  • Filter: ((se_id = 0) AND (ad_id = 75))
  • Rows Removed by Filter: 143909
48. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=1.18..1.18 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
49. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on rtpe rtpe_2 (cost=0.00..1.18 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (id = 5)
  • Rows Removed by Filter: 13
50. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepezocoshav_unique on rtsepezocoshav av_1_3 (cost=0.42..0.58 rows=1 width=24) (never executed)

  • Index Cond: ((se_id = av_3.se_id) AND (se_id = 0) AND (pe_id = 5) AND (zo_id = av_3.zo_id) AND (co_id = av_3.co_id) AND (sh_id = av_3.sh_id) AND (ad_id = av_3.ad_id) AND (ad_id = 75))
  • Heap Fetches: 0
51. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..1,850.94 rows=2 width=12) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Index Scan using rtsepecoav_ad_id_pe_id_co_id_index on rtsepecoav rtsepecoav_1 (cost=0.42..0.48 rows=1 width=12) (never executed)

  • Index Cond: ((ad_id = $2) AND (pe_id = 5) AND (co_id = av_3.co_id))
  • Filter: ((value_num = '1'::numeric) AND (se_id = 0))
53. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_4 (cost=884.77..1,850.46 rows=1 width=12) (never executed)

  • Filter: (av_3.co_id = "*SELECT* 2_4".co_id)
54. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=884.77..1,850.37 rows=7 width=84) (never executed)

  • Join Filter: (av_1_4.pe_id = rtpe_3.id)
55.          

Initplan (forNested Loop Anti Join)

56. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtad rtad_3 (cost=0.00..2.95 rows=1 width=4) (never executed)

  • Filter: ((alias)::text = 'active'::text)
57. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=881.41..1,801.39 rows=14 width=16) (never executed)

  • Join Filter: (av_1_1_2.pe_id = rtpe_1_2.id)
58. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=880.99..1,712.62 rows=27 width=32) (never executed)

  • Hash Cond: (av_4.co_id = av_1_5.co_id)
59. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=30.37..845.32 rows=424 width=16) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtpe rtpe_3 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: (id = 5)
61. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on rtsepecoav av_4 (cost=30.37..843.43 rows=71 width=16) (never executed)

  • Recheck Cond: ((ad_id = $2) AND (se_id = 0) AND (pe_id = rtpe_3.baseline_id))
  • Filter: (value_num = '1'::numeric)
62. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on rtsepecoav_unique (cost=0.00..30.36 rows=475 width=0) (never executed)

  • Index Cond: ((ad_id = $2) AND (se_id = 0) AND (pe_id = rtpe_3.baseline_id))
63. 0.000 0.000 ↓ 0.0 0

Hash (cost=845.32..845.32 rows=424 width=16) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=30.37..845.32 rows=424 width=16) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtpe rtpe_1_2 (cost=0.00..1.18 rows=1 width=8) (never executed)

  • Filter: (id = 5)
66. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on rtsepecoav av_1_5 (cost=30.37..843.43 rows=71 width=16) (never executed)

  • Recheck Cond: ((ad_id = $3) AND (se_id = 0) AND (pe_id = rtpe_1_2.baseline_id))
  • Filter: (value_num = '1'::numeric)
67. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on rtsepecoav_unique (cost=0.00..30.36 rows=475 width=0) (never executed)

  • Index Cond: ((ad_id = $3) AND (se_id = 0) AND (pe_id = rtpe_1_2.baseline_id))
68. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepecoav_unique on rtsepecoav av_1_1_2 (cost=0.42..3.29 rows=1 width=16) (never executed)

  • Index Cond: ((ad_id = av_1_5.ad_id) AND (ad_id = $3) AND (se_id = av_1_5.se_id) AND (se_id = 0) AND (pe_id = 5) AND (co_id = av_1_5.co_id))
  • Heap Fetches: 0
69. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepecoav_unique on rtsepecoav av_1_4 (cost=0.42..3.29 rows=1 width=16) (never executed)

  • Index Cond: ((ad_id = av_4.ad_id) AND (ad_id = $2) AND (se_id = av_4.se_id) AND (se_id = 0) AND (pe_id = 5) AND (co_id = av_4.co_id))
  • Heap Fetches: 0
70. 35.446 85.564 ↓ 5.1 289,575 1

Hash (cost=1,833.87..1,833.87 rows=56,504 width=17) (actual time=85.564..85.564 rows=289,575 loops=1)

  • Buckets: 524288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 17953kB
71. 33.738 50.118 ↓ 5.1 289,575 1

Merge Join (cost=640.09..1,833.87 rows=56,504 width=17) (actual time=0.676..50.118 rows=289,575 loops=1)

  • Merge Cond: (co.br_id = rtsepebrbrav.br_id)
72. 1.157 1.157 ↑ 1.0 7,425 1

Index Scan using rtco_br_id_idx on rtco co (cost=0.28..327.94 rows=7,425 width=8) (actual time=0.012..1.157 rows=7,425 loops=1)

73. 14.727 15.223 ↓ 190.1 289,362 1

Sort (cost=639.81..643.61 rows=1,522 width=13) (actual time=0.662..15.223 rows=289,362 loops=1)

  • Sort Key: rtsepebrbrav.br_id
  • Sort Method: quicksort Memory: 120kB
74. 0.073 0.496 ↑ 1.0 1,521 1

Append (cost=63.68..559.36 rows=1,522 width=13) (actual time=0.133..0.496 rows=1,521 loops=1)

75. 0.284 0.409 ↑ 1.0 1,521 1

Bitmap Heap Scan on rtsepebrbrav (cost=63.68..136.29 rows=1,521 width=13) (actual time=0.133..0.409 rows=1,521 loops=1)

  • Recheck Cond: ((se_id = 0) AND (pe_id = 5) AND (ad_id = 71))
  • Heap Blocks: exact=45
76. 0.125 0.125 ↑ 1.0 1,521 1

Bitmap Index Scan on rtsepebrbrav_unique (cost=0.00..63.30 rows=1,521 width=0) (actual time=0.125..0.125 rows=1,521 loops=1)

  • Index Cond: ((se_id = 0) AND (pe_id = 5) AND (ad_id = 71))
77. 0.002 0.014 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=379.05..423.06 rows=1 width=13) (actual time=0.013..0.014 rows=0 loops=1)

78. 0.000 0.012 ↓ 0.0 0 1

Merge Anti Join (cost=379.05..423.05 rows=1 width=61) (actual time=0.012..0.012 rows=0 loops=1)

  • Merge Cond: ((av_5.br_id = av_1_6.br_id) AND (av_5.br2_id = av_1_6.br2_id))
  • Join Filter: ((av_1_6.pe_id = rtpe_4.id) AND (av_1_6.se_id = av_5.se_id) AND (av_1_6.ad_id = av_5.ad_id))
79. 0.002 0.012 ↓ 0.0 0 1

Sort (cost=220.15..223.41 rows=1,304 width=25) (actual time=0.012..0.012 rows=0 loops=1)

  • Sort Key: av_5.br_id, av_5.br2_id
  • Sort Method: quicksort Memory: 25kB
80. 0.001 0.010 ↓ 0.0 0 1

Nested Loop (cost=63.68..152.68 rows=1,304 width=25) (actual time=0.010..0.010 rows=0 loops=1)

81. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on rtpe rtpe_4 (cost=0.00..1.18 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (id = 5)
  • Rows Removed by Filter: 13
82. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on rtsepebrbrav av_5 (cost=63.68..136.29 rows=1,521 width=25) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: ((se_id = 0) AND (pe_id = rtpe_4.baseline_id) AND (ad_id = 71))
83. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on rtsepebrbrav_unique (cost=0.00..63.30 rows=1,521 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((se_id = 0) AND (pe_id = rtpe_4.baseline_id) AND (ad_id = 71))
84. 0.000 0.000 ↓ 0.0 0

Sort (cost=158.90..162.70 rows=1,521 width=20) (never executed)

  • Sort Key: av_1_6.br_id, av_1_6.br2_id
85. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtsepebrbrav_unique on rtsepebrbrav av_1_6 (cost=0.28..78.50 rows=1,521 width=20) (never executed)

  • Index Cond: ((se_id = 0) AND (pe_id = 5) AND (ad_id = 71))
  • Heap Fetches: 0
Planning time : 4.670 ms
Execution time : 9,207.052 ms