explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 00z

Settings
# exclusive inclusive rows x rows loops node
1. 1.401 480,512.178 ↑ 1.0 1 1

Aggregate (cost=1,284.28..1,284.29 rows=1 width=8) (actual time=480,512.178..480,512.178 rows=1 loops=1)

2.          

CTE to_be_deleted_rows

3. 0.342 469,197.153 ↑ 1.0 1,000 1

Limit (cost=52.08..296.28 rows=1,000 width=94) (actual time=3.815..469,197.153 rows=1,000 loops=1)

4. 4.051 469,196.811 ↑ 19,984.9 1,000 1

LockRows (cost=52.08..4,880,406.59 rows=19,984,923 width=94) (actual time=3.814..469,196.811 rows=1,000 loops=1)

5. 2.258 469,192.760 ↑ 19,984.9 1,000 1

Hash Left Join (cost=52.08..4,680,557.36 rows=19,984,923 width=94) (actual time=3.807..469,192.760 rows=1,000 loops=1)

  • Hash Cond: (b.group_id = fg.uid)
  • Filter: ((fg.uid IS NULL) OR (NOT fg.disabled))
6. 469,190.010 469,190.010 ↑ 20,008.9 1,000 1

Index Scan using blocks_cleaner_true_idx on blocks b (cost=0.55..4,627,841.53 rows=20,008,886 width=88) (actual time=3.303..469,190.010 rows=1,000 loops=1)

  • Index Cond: ((reference_count = 0) AND (is_file_map = true))
  • Filter: ((storage_node_block_id IS NOT NULL) AND (status <> 3) AND (location <> ALL ('{4774,5110,4022,3677}'::bigint[])))
  • Rows Removed by Filter: 537,820
7. 0.196 0.492 ↑ 1.0 1,577 1

Hash (cost=31.79..31.79 rows=1,579 width=15) (actual time=0.491..0.492 rows=1,577 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 92kB
8. 0.296 0.296 ↑ 1.0 1,577 1

Seq Scan on folders_groups_states fg (cost=0.00..31.79 rows=1,579 width=15) (actual time=0.013..0.296 rows=1,577 loops=1)

9.          

CTE inserted_rows

10. 743.133 469,942.705 ↑ 1.0 1,000 1

Insert on deleted_mapfile_blocks (cost=0.00..20.00 rows=1,000 width=653) (actual time=6.327..469,942.705 rows=1,000 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: deleted_mapfile_blocks_pkey
  • Tuples Inserted: 1,000
  • Conflicting Tuples: 0
11. 469,199.572 469,199.572 ↑ 1.0 1,000 1

CTE Scan on to_be_deleted_rows (cost=0.00..20.00 rows=1,000 width=653) (actual time=3.818..469,199.572 rows=1,000 loops=1)

12.          

CTE deleted_rows

13. 7.581 480,508.719 ↑ 1.0 1,000 1

Delete on blocks (cost=23.08..945.50 rows=1,000 width=38) (actual time=469,966.801..480,508.719 rows=1,000 loops=1)

14. 2.321 480,501.138 ↑ 1.0 1,000 1

Nested Loop (cost=23.08..945.50 rows=1,000 width=38) (actual time=469,966.791..480,501.138 rows=1,000 loops=1)

15. 3.559 469,954.817 ↓ 5.0 1,000 1

HashAggregate (cost=22.50..24.50 rows=200 width=40) (actual time=469,952.956..469,954.817 rows=1,000 loops=1)

  • Group Key: inserted_rows.uid
16. 469,951.258 469,951.258 ↑ 1.0 1,000 1

CTE Scan on inserted_rows (cost=0.00..20.00 rows=1,000 width=40) (actual time=13.432..469,951.258 rows=1,000 loops=1)

17. 10,544.000 10,544.000 ↑ 1.0 1 1,000

Index Scan using block_uid on blocks (cost=0.58..4.59 rows=1 width=14) (actual time=10.070..10.544 rows=1 loops=1,000)

  • Index Cond: (uid = inserted_rows.uid)
18. 480,510.777 480,510.777 ↑ 1.0 1,000 1

CTE Scan on deleted_rows (cost=0.00..20.00 rows=1,000 width=0) (actual time=469,966.802..480,510.777 rows=1,000 loops=1)