Skip to content

Remove unreferenced lfs objects in batches

Igor Drozdov requested to merge id-destroy-unreferenced-lfs-objects-in-batches into master

What does this MR do?

Related issue: #199261 (closed)

The LEFT JOIN query causes timeout, the NOT EXISTS query has the following query plan:

Limit  (cost=78.11..158.97 rows=1000 width=4) (actual time=0.662..2595.578 rows=1000 loops=1)
  Buffers: shared hit=142842 read=5949 written=658
  I/O Timings: read=2087.089 write=67.030
  ->  Merge Anti Join  (cost=78.11..3236332.49 rows=40022533 width=4) (actual time=0.661..2595.460 rows=1000 loops=1)
        Merge Cond: (lfs_objects.id = lfs_objects_projects.lfs_object_id)
        Buffers: shared hit=142842 read=5949 written=658
        I/O Timings: read=2087.089 write=67.030
        ->  Index Only Scan using lfs_objects_pkey on lfs_objects  (cost=0.56..828839.74 rows=41151504 width=4) (actual time=0.019..16.506 rows=52817 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=4619 read=132 written=17
              I/O Timings: read=4.311 write=0.428
        ->  Index Only Scan using index_lfs_objects_projects_on_lfs_object_id on lfs_objects_projects  (cost=0.57..1436562.49 rows=69444120 width=4) (actual time=0.022..2485.534 rows=817405 loops=1)
              Heap Fetches: 11886
              Buffers: shared hit=138223 read=5817 written=641
              I/O Timings: read=2082.778 write=66.601
Planning Time: 0.835 ms
Execution Time: 2595.793 ms

Merge request reports