explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m4Iv : test

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,329.933 43,309.716 ↓ 4,024.8 990,113 1

Nested Loop (cost=369.02..10,751.37 rows=246 width=32) (actual time=29,314.290..43,309.716 rows=990,113 loops=1)

  • Join Filter: (n.br_id = sh.br_id)
2. 0.000 39,989.670 ↓ 4,125.5 990,113 1

Nested Loop (cost=368.87..10,697.68 rows=240 width=70) (actual time=29,314.267..39,989.670 rows=990,113 loops=1)

3. 816.700 37,196.180 ↓ 3,992.4 990,113 1

Hash Join (cost=368.45..9,455.59 rows=248 width=65) (actual time=29,314.259..37,196.180 rows=990,113 loops=1)

  • Hash Cond: ((rtpezocoav.co_id = n.co_id) AND (rere.re_id = n.re_id))
4. 417.284 36,377.053 ↓ 798.9 4,951,030 1

Nested Loop (cost=0.70..9,023.38 rows=6,197 width=60) (actual time=12.267..36,377.053 rows=4,951,030 loops=1)

5. 8.730 83.479 ↓ 812.6 20,315 1

Nested Loop (cost=0.14..1,286.44 rows=25 width=56) (actual time=12.248..83.479 rows=20,315 loops=1)

  • Join Filter: (calc_zones.re_id = rere.re2_id)
  • Rows Removed by Join Filter: 81260
6. 0.024 0.024 ↓ 25.0 25 1

Index Scan using rtseperereav_unique on rtseperereav rere (cost=0.14..8.16 rows=1 width=48) (actual time=0.009..0.024 rows=25 loops=1)

  • Index Cond: ((se_id = 1) AND (pe_id = 3) AND (ad_id = 65))
7. 6.300 74.725 ↑ 1.3 4,063 25

Append (cost=0.00..1,214.80 rows=5,079 width=16) (actual time=0.002..2.989 rows=4,063 loops=25)

8. 32.325 32.325 ↑ 1.0 4,063 25

Seq Scan on calc_zones (cost=0.00..361.89 rows=4,063 width=16) (actual time=0.002..1.293 rows=4,063 loops=25)

  • Filter: ((pe_id = 3) AND (se_id = 1))
  • Rows Removed by Filter: 4063
9. 0.000 36.100 ↓ 0.0 0 25

Subquery Scan on *SELECT* 2 (cost=455.46..852.91 rows=1,016 width=16) (actual time=1.444..1.444 rows=0 loops=25)

10. 11.639 36.100 ↓ 0.0 0 25

Hash Join (cost=455.46..842.75 rows=1,016 width=820) (actual time=1.444..1.444 rows=0 loops=25)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
11. 23.287 24.456 ↓ 2.0 8,126 24

Seq Scan on calc_zones av (cost=454.40..816.29 rows=4,063 width=16) (actual time=0.051..1.019 rows=8,126 loops=24)

  • Filter: ((NOT (hashed SubPlan 1)) AND (se_id = 1))
12.          

SubPlan (forSeq Scan)

13. 0.535 1.169 ↓ 0.0 0 1

Hash Join (cost=1.09..434.08 rows=8,126 width=4) (actual time=1.169..1.169 rows=0 loops=1)

  • Hash Cond: (av_1.pe_id = rtpe_1.id)
14. 0.628 0.628 ↑ 1.0 8,126 1

Seq Scan on calc_zones av_1 (cost=0.00..321.26 rows=8,126 width=8) (actual time=0.002..0.628 rows=8,126 loops=1)

15. 0.003 0.006 ↑ 2.0 2 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.005..0.006 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.003 0.003 ↑ 2.0 2 1

Seq Scan on rtpe rtpe_1 (cost=0.00..1.04 rows=4 width=4) (actual time=0.003..0.003 rows=2 loops=1)

  • Filter: (baseline_id IS NOT NULL)
  • Rows Removed by Filter: 2
17. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=1.05..1.05 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 0.005 0.005 ↑ 1.0 1 1

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

  • Filter: (id = 3)
  • Rows Removed by Filter: 3
19. 35,876.290 35,876.290 ↑ 1.0 244 20,315

Index Scan using rtpezocoav_unique on rtpezocoav (cost=0.56..307.04 rows=244 width=12) (actual time=0.016..1.766 rows=244 loops=20,315)

  • Index Cond: ((pe_id = 3) AND (zo_id = calc_zones.zo_id) AND (ad_id = 12))
  • Filter: (value_num < '30'::numeric)
  • Rows Removed by Filter: 1798
20. 0.798 2.427 ↑ 1.0 5,572 1

Hash (cost=284.17..284.17 rows=5,572 width=33) (actual time=2.427..2.427 rows=5,572 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 435kB
21. 1.629 1.629 ↑ 1.0 5,572 1

Seq Scan on calc_nodes n (cost=0.00..284.17 rows=5,572 width=33) (actual time=0.006..1.629 rows=5,572 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 1))
  • Rows Removed by Filter: 5573
22. 2,970.339 2,970.339 ↑ 1.0 1 990,113

Index Scan using rtsepezobrav_unique on rtsepezobrav com (cost=0.42..5.00 rows=1 width=21) (actual time=0.002..0.003 rows=1 loops=990,113)

  • Index Cond: ((se_id = 1) AND (pe_id = 3) AND (zo_id = n.zo_id) AND (br_id = n.br_id) AND (ad_id = 64))
23. 990.113 990.113 ↑ 1.0 1 990,113

Index Scan using idx_calc_shopconcepts_sepebr on calc_shopconcepts sh (cost=0.14..0.17 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=990,113)

  • Index Cond: ((se_id = 1) AND (pe_id = 3) AND (br_id = com.br_id))