explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HnSS : old query

Settings
# exclusive inclusive rows x rows loops node
1. 99.248 9,518.858 ↓ 0.0 0 1

Delete on fact_paths fp (cost=860,911,367,928.48..997,382,278,149.41 rows=41,020 width=62) (actual time=9,518.858..9,518.858 rows=0 loops=1)

2.          

CTE live_paths

3. 497.559 4,822.056 ↑ 7,195,172.3 1,387,297 1

Recursive Union (cost=0.00..400,031,052,858.48 rows=9,981,841,003,200 width=96) (actual time=0.336..4,822.056 rows=1,387,297 loops=1)

4. 124.530 2,139.589 ↓ 1.0 1,025,789 1

Subquery Scan on base_case (cost=0.00..16,794.48 rows=1,003,200 width=96) (actual time=0.333..2,139.589 rows=1,025,789 loops=1)

5. 2,015.059 2,015.059 ↓ 1.0 1,025,789 1

Seq Scan on factsets (cost=0.00..6,762.48 rows=1,003,200 width=64) (actual time=0.332..2,015.059 rows=1,025,789 loops=1)

6. 208.692 2,184.908 ↑ 30,373,174.3 32,864 11

Subquery Scan on candidates (cost=0.00..20,039,421,600.00 rows=998,184,000,000 width=96) (actual time=0.051..198.628 rows=32,864 loops=11)

  • Filter: (candidates.sub_path IS NOT NULL)
  • Rows Removed by Filter: 113311
7. 1,976.216 1,976.216 ↑ 6,863,006.7 146,175 11

WorkTable Scan on live_paths live_paths_1 (cost=0.00..5,016,501,600.00 rows=1,003,200,000,000 width=96) (actual time=0.015..179.656 rows=146,175 loops=11)

8. 2,715.739 9,419.610 ↓ 1.7 70,764 1

Hash Anti Join (cost=460,880,315,070.00..597,351,225,290.93 rows=41,020 width=62) (actual time=6,795.042..9,419.610 rows=70,764 loops=1)

  • Hash Cond: (fp.path = live_paths.path)
9. 26.934 26.934 ↑ 1.0 80,085 1

Seq Scan on fact_paths fp (cost=0.00..3,750.40 rows=82,040 width=105) (actual time=0.016..26.934 rows=80,085 loops=1)

10. 927.779 6,676.937 ↑ 7,195,172.3 1,387,297 1

Hash (cost=199,636,820,064.00..199,636,820,064.00 rows=9,981,841,003,200 width=88) (actual time=6,676.937..6,676.937 rows=1,387,297 loops=1)

  • Buckets: 1048576 Batches: 8388608 Memory Usage: 9866kB
11. 5,749.158 5,749.158 ↑ 7,195,172.3 1,387,297 1

CTE Scan on live_paths (cost=0.00..199,636,820,064.00 rows=9,981,841,003,200 width=88) (actual time=0.346..5,749.158 rows=1,387,297 loops=1)