explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pl5l

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

Nested Loop (cost=21,410.40..22,851.44 rows=1 width=32) (actual time=6,834.557..12,992.116 rows=990,113 loops=1)

2. 670.303 9,916.089 ↓ 990,113.0 990,113 1

Nested Loop (cost=21,410.25..22,851.22 rows=1 width=86) (actual time=6,834.533..9,916.089 rows=990,113 loops=1)

3. 422.124 7,265.560 ↓ 990,113.0 990,113 1

Hash Join (cost=21,409.83..22,848.12 rows=1 width=77) (actual time=6,834.512..7,265.560 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. 0.946 8.946 ↓ 1.3 16,252 1

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

5. 2.646 2.646 ↑ 1.0 8,126 1

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

  • Filter: (se_id = 1)
6. 0.538 5.354 ↓ 2.0 8,126 1

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

7. 2.367 4.816 ↓ 2.0 8,126 1

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

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

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

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

SubPlan (forSeq Scan)

10. 0.540 1.348 ↓ 0.0 0 1

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

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

12. 0.002 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.002 0.002 ↑ 2.0 2 1

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

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

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

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

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

16. 1,053.208 6,834.490 ↓ 3,125.4 4,950,565 1

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

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

Hash Join (cost=452.74..21,382.11 rows=1,584 width=77) (actual time=3.814..5,781.282 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,885.107 3,008.216 ↓ 625.0 24,755,150 1

Hash Join (cost=1.39..20,320.80 rows=39,608 width=60) (actual time=0.055..3,008.216 rows=24,755,150 loops=1)

  • Hash Cond: (dt.pe_id = rere.pe_id)
19. 123.088 123.088 ↑ 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.030..123.088 rows=990,206 loops=1)

20. 0.010 0.021 ↓ 50.0 50 1

Hash (cost=1.38..1.38 rows=1 width=48) (actual time=0.021..0.021 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.004..0.011 rows=50 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 869kB
23. 1.995 1.995 ↑ 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..1.995 rows=11,145 loops=1)

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

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

  • Index Cond: ((se_id = 1) AND (pe_id = n.pe_id) AND (zo_id = n.zo_id) AND (br_id = n.br_id) AND (ad_id = 64))
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.534 ms
Execution time : 13,034.903 ms