explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kA5 : new pg 10 query w/o temp tables

Settings
# exclusive inclusive rows x rows loops node
1. 90.426 7,120.882 ↓ 0.0 0 1

Delete on fact_paths fp (cost=13,477,981,128.48..14,987,141,349.41 rows=41,020 width=62) (actual time=7,120.882..7,120.882 rows=0 loops=1)

2.          

CTE live_paths

3. 829.184 4,036.822 ↑ 742,517.9 148,620 1

Recursive Union (cost=0.00..8,382,776,058.48 rows=110,353,003,200 width=64) (actual time=0.350..4,036.822 rows=148,620 loops=1)

4. 2,004.689 2,004.689 ↓ 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.341..2,004.689 rows=1,025,789 loops=1)

5. 289.240 1,202.949 ↑ 1,286,153.8 8,580 11

Nested Loop (cost=23.04..617,569,920.00 rows=11,035,200,000 width=64) (actual time=0.213..109.359 rows=8,580 loops=11)

6. 21.989 21.989 ↑ 742.5 13,511 11

WorkTable Scan on live_paths live_paths_1 (cost=0.00..200,640.00 rows=10,032,000 width=64) (actual time=0.001..1.999 rows=13,511 loops=11)

7. 445.860 891.720 ↑ 1,100.0 1 148,620

HashAggregate (cost=23.04..34.04 rows=1,100 width=64) (actual time=0.006..0.006 rows=1 loops=148,620)

  • Group Key: ((jsonb_each(live_paths_1.value)).key), ((jsonb_each(live_paths_1.value)).value)
8. 0.000 445.860 ↑ 1,100.0 1 148,620

Append (cost=0.01..17.54 rows=1,100 width=64) (actual time=0.003..0.003 rows=1 loops=148,620)

9. 445.860 445.860 ↑ 100.0 1 148,620

Result (cost=0.01..0.52 rows=100 width=64) (actual time=0.002..0.003 rows=1 loops=148,620)

  • One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'object'::text)
10. 0.000 0.000 ↓ 0.0 0 148,620

Result (cost=0.01..5.03 rows=1,000 width=64) (actual time=0.000..0.000 rows=0 loops=148,620)

  • One-Time Filter: (jsonb_typeof(live_paths_1.value) = 'array'::text)
11. 2,492.178 7,030.456 ↓ 1.7 70,764 1

Hash Anti Join (cost=5,095,205,070.00..6,604,365,290.93 rows=41,020 width=62) (actual time=4,607.041..7,030.456 rows=70,764 loops=1)

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

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

13. 378.210 4,509.320 ↑ 742,517.9 148,620 1

Hash (cost=2,207,060,064.00..2,207,060,064.00 rows=110,353,003,200 width=88) (actual time=4,509.320..4,509.320 rows=148,620 loops=1)

  • Buckets: 1048576 Batches: 262144 Memory Usage: 8197kB
14. 4,131.110 4,131.110 ↑ 742,517.9 148,620 1

CTE Scan on live_paths (cost=0.00..2,207,060,064.00 rows=110,353,003,200 width=88) (actual time=0.359..4,131.110 rows=148,620 loops=1)