explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hpaC

Settings
# exclusive inclusive rows x rows loops node
1. 1,140.067 6,714,493.715 ↓ 3.0 1,156,721 1

Sort (cost=601,207.93..602,158.84 rows=380,366 width=44) (actual time=6,714,335.381..6,714,493.715 rows=1,156,721 loops=1)

  • Sort Key: parents.id
  • Sort Method: external merge Disk: 65656kB
  • Functions: 36
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.960 ms, Inlining 133.532 ms, Optimization 221.948 ms, Emission 130.827 ms, Total 493.267 ms
2.          

CTE parents

3. 2,693.057 6,711,877.230 ↓ 3.0 1,156,721 1

Recursive Union (cost=1,101.74..546,642.33 rows=380,366 width=44) (actual time=226.666..6,711,877.230 rows=1,156,721 loops=1)

4. 11.283 269.895 ↑ 1.0 3,766 1

Gather (cost=1,101.74..19,910.27 rows=3,766 width=44) (actual time=226.660..269.895 rows=3,766 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 42.550 258.612 ↑ 1.3 1,255 3

Hash Join (cost=101.74..18,533.67 rows=1,569 width=44) (actual time=160.719..258.612 rows=1,255 loops=3)

  • Hash Cond: (delta.id = refs.id)
6. 55.618 55.618 ↑ 1.2 420,622 3

Parallel Seq Scan on delta (cost=0.00..17,051.77 rows=525,777 width=44) (actual time=0.020..55.618 rows=420,622 loops=3)

7. 159.927 160.444 ↑ 1.0 3,766 3

Hash (cost=54.66..54.66 rows=3,766 width=4) (actual time=160.444..160.444 rows=3,766 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
8. 0.517 0.517 ↑ 1.0 3,766 3

Seq Scan on refs (cost=0.00..54.66 rows=3,766 width=4) (actual time=0.022..0.517 rows=3,766 loops=3)

9. 1,579,648.554 6,708,914.278 ↑ 836.9 45 25,762

Merge Join (cost=3,615.93..51,912.47 rows=37,660 width=44) (actual time=97.314..260.419 rows=45 loops=25,762)

  • Merge Cond: (d.id = p.parentdeltaid)
10. 5,128,492.864 5,128,492.864 ↑ 1.1 1,099,535 25,762

Index Scan using delta_pkey on delta d (cost=0.43..44,577.41 rows=1,261,865 width=44) (actual time=0.011..199.072 rows=1,099,535 loops=25,762)

11. 566.764 772.860 ↑ 836.9 45 25,762

Sort (cost=3,615.50..3,709.65 rows=37,660 width=4) (actual time=0.021..0.030 rows=45 loops=25,762)

  • Sort Key: p.parentdeltaid
  • Sort Method: quicksort Memory: 25kB
12. 206.096 206.096 ↑ 836.9 45 25,762

WorkTable Scan on parents p (cost=0.00..753.20 rows=37,660 width=4) (actual time=0.002..0.008 rows=45 loops=25,762)

13. 6,713,353.648 6,713,353.648 ↓ 3.0 1,156,721 1

CTE Scan on parents (cost=0.00..7,607.32 rows=380,366 width=44) (actual time=226.671..6,713,353.648 rows=1,156,721 loops=1)

Planning time : 0.542 ms
Execution time : 6,714,563.154 ms