explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 221S

Settings
# exclusive inclusive rows x rows loops node
1. 2,067.702 12,654.948 ↓ 990,113.0 990,113 1

Nested Loop (cost=21,410.40..22,850.86 rows=1 width=32) (actual time=6,826.571..12,654.948 rows=990,113 loops=1)

2. 362.408 9,597.133 ↓ 990,113.0 990,113 1

Nested Loop (cost=21,410.25..22,850.64 rows=1 width=86) (actual time=6,826.546..9,597.133 rows=990,113 loops=1)

3. 418.932 7,254.499 ↓ 990,113.0 990,113 1

Hash Join (cost=21,409.83..22,848.12 rows=1 width=77) (actual time=6,826.486..7,254.499 rows=990,113 loops=1)

  • Hash Cond: ((calc_zones.pe_id = n.pe_id) AND (calc_zones.zo_id = dt.zo_id) AND (calc_zones.re_id = rere.re2_id))
4. 1.132 9.103 ↓ 1.3 16,252 1

Append (cost=0.00..1,255.45 rows=12,189 width=16) (actual time=0.008..9.103 rows=16,252 loops=1)

5. 2.770 2.770 ↑ 1.0 8,126 1

Seq Scan on calc_zones (cost=0.00..341.57 rows=8,126 width=16) (actual time=0.007..2.770 rows=8,126 loops=1)

  • Filter: (se_id = 1)
6. 0.564 5.201 ↓ 2.0 8,126 1

Subquery Scan on *SELECT* 2 (cost=455.49..913.87 rows=4,063 width=16) (actual time=1.412..5.201 rows=8,126 loops=1)

7. 2.185 4.637 ↓ 2.0 8,126 1

Hash Join (cost=455.49..873.24 rows=4,063 width=820) (actual time=1.412..4.637 rows=8,126 loops=1)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
8. 1.094 2.437 ↓ 2.0 8,126 1

Seq Scan on calc_zones av (cost=454.40..816.29 rows=4,063 width=16) (actual time=1.383..2.437 rows=8,126 loops=1)

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

SubPlan (forSeq Scan)

10. 0.551 1.343 ↓ 0.0 0 1

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

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

12. 0.001 0.004 ↑ 2.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 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.002..0.003 rows=2 loops=1)

  • Filter: (baseline_id IS NOT NULL)
  • Rows Removed by Filter: 2
14. 0.007 0.015 ↑ 2.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on rtpe (cost=0.00..1.04 rows=4 width=8) (actual time=0.007..0.008 rows=4 loops=1)

16. 1,054.927 6,826.464 ↓ 3,125.4 4,950,565 1

Hash (cost=21,382.11..21,382.11 rows=1,584 width=77) (actual time=6,826.464..6,826.464 rows=4,950,565 loops=1)

  • Buckets: 8388608 (originally 2048) Batches: 1 (originally 1) Memory Usage: 490970kB
17. 2,782.095 5,771.537 ↓ 3,125.4 4,950,565 1

Hash Join (cost=452.74..21,382.11 rows=1,584 width=77) (actual time=3.884..5,771.537 rows=4,950,565 loops=1)

  • Hash Cond: ((dt.co_id = n.co_id) AND (dt.pe_id = n.pe_id) AND (rere.re_id = n.re_id))
18. 2,908.580 2,985.604 ↓ 625.0 24,755,150 1

Hash Join (cost=1.39..20,320.80 rows=39,608 width=60) (actual time=0.026..2,985.604 rows=24,755,150 loops=1)

  • Hash Cond: (dt.pe_id = rere.pe_id)
19. 77.004 77.004 ↑ 1.0 990,206 1

Seq Scan on tmp_test_drivetimes dt (cost=0.00..16,210.06 rows=990,206 width=12) (actual time=0.003..77.004 rows=990,206 loops=1)

20. 0.009 0.020 ↓ 50.0 50 1

Hash (cost=1.38..1.38 rows=1 width=48) (actual time=0.020..0.020 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.011 0.011 ↓ 50.0 50 1

Seq Scan on rtseperereav rere (cost=0.00..1.38 rows=1 width=48) (actual time=0.003..0.011 rows=50 loops=1)

  • Filter: ((se_id = 1) AND (ad_id = 65))
22. 1.799 3.838 ↑ 1.0 11,145 1

Hash (cost=256.31..256.31 rows=11,145 width=33) (actual time=3.838..3.838 rows=11,145 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 869kB
23. 2.039 2.039 ↑ 1.0 11,145 1

Seq Scan on calc_nodes n (cost=0.00..256.31 rows=11,145 width=33) (actual time=0.005..2.039 rows=11,145 loops=1)

  • Filter: (se_id = 1)
24. 1,980.226 1,980.226 ↑ 1.0 1 990,113

Index Scan using idx_tmp_test_comfort_pezo on tmp_test_comfort com (cost=0.42..2.51 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=990,113)

  • Index Cond: ((zo_id = n.zo_id) AND (br_id = n.br_id) AND (pe_id = n.pe_id) AND (se_id = 1))
25. 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: ((br_id = n.br_id) AND (pe_id = n.pe_id) AND (se_id = 1))
Planning time : 4.601 ms
Execution time : 12,698.989 ms