explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3jDk : Optimization for: test; plan #m4Iv

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 504.482 37,243.041 ↓ 4,180.1 83,603 1

Nested Loop (cost=1.41..9,258.75 rows=20 width=32) (actual time=28,983.255..37,243.041 rows=83,603 loops=1)

  • Join Filter: (n.br_id = sh.br_id)
  • Rows Removed by Join Filter: 5183386
2. 0.046 0.046 ↑ 1.0 63 1

Seq Scan on calc_shopconcepts sh (cost=0.00..4.89 rows=63 width=21) (actual time=0.010..0.046 rows=63 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 1))
  • Rows Removed by Filter: 63
3. 196.212 36,738.513 ↓ 4,180.1 83,603 63

Materialize (cost=1.41..9,234.11 rows=20 width=70) (actual time=460.033..583.151 rows=83,603 loops=63)

4. 12.966 36,542.301 ↓ 4,180.1 83,603 1

Nested Loop (cost=1.41..9,234.01 rows=20 width=70) (actual time=28,982.062..36,542.301 rows=83,603 loops=1)

5. 105.222 36,278.526 ↓ 4,180.1 83,603 1

Nested Loop (cost=0.98..9,133.84 rows=20 width=65) (actual time=28,982.051..36,278.526 rows=83,603 loops=1)

  • Join Filter: (rere.re_id = n.re_id)
  • Rows Removed by Join Filter: 334412
6. 46.182 35,337.224 ↓ 826.2 418,040 1

Nested Loop (cost=0.70..8,967.38 rows=506 width=60) (actual time=13.799..35,337.224 rows=418,040 loops=1)

7. 8.975 85.147 ↓ 812.6 20,315 1

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

  • Join Filter: (calc_zones.re_id = rere.re2_id)
  • Rows Removed by Join Filter: 81260
8. 0.022 0.022 ↓ 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.022 rows=25 loops=1)

  • Index Cond: ((se_id = 1) AND (pe_id = 3) AND (ad_id = 65))
9. 7.025 76.150 ↑ 1.3 4,063 25

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

10. 32.575 32.575 ↑ 1.0 4,063 25

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

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

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

12. 11.824 36.550 ↓ 0.0 0 25

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

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
13. 23.548 24.720 ↓ 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.030 rows=8,126 loops=24)

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

SubPlan (forSeq Scan)

15. 0.555 1.172 ↓ 0.0 0 1

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

  • Hash Cond: (av_1.pe_id = rtpe_1.id)
16. 0.612 0.612 ↑ 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.001..0.612 rows=8,126 loops=1)

17. 0.000 0.005 ↑ 2.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.005 0.005 ↑ 2.0 2 1

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

  • Filter: (baseline_id IS NOT NULL)
  • Rows Removed by Filter: 2
19. 0.001 0.006 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
20. 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
21. 35,205.895 35,205.895 ↓ 1.1 21 20,315

Index Scan using rtpezocoav_unique on rtpezocoav (cost=0.56..307.04 rows=20 width=12) (actual time=0.215..1.733 rows=21 loops=20,315)

  • Index Cond: ((pe_id = 3) AND (zo_id = calc_zones.zo_id) AND (ad_id = 12))
  • Filter: (value_num < '10'::numeric)
  • Rows Removed by Filter: 2021
22. 836.080 836.080 ↑ 1.0 1 418,040

Index Scan using idx_calc_nodes_sepeco on calc_nodes n (cost=0.29..0.32 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=418,040)

  • Index Cond: ((se_id = 1) AND (pe_id = 3) AND (co_id = rtpezocoav.co_id))
23. 250.809 250.809 ↑ 1.0 1 83,603

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

  • 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))