Optimize worker results deletion on child elements

Erwan Rouchet
2025-04-09T13:09:34Z

https://redmine.teklia.com/issues/10247

Deleting worker results on one element will actually delete worker results twice, first on all the child elements of an element, and then on the element itself. This is much faster than doing both at once because it means we never need OR in any condition, something so bad for performance that it is discouraged in our best practices.

When deleting all worker results on all child elements, the task will filter all results by corpus_id, then by path, looking for all elements that have paths that contain the parent element's ID. It then applies all the worker version, worker run, model version or worker configuration filters on top of that. It starts by deleting elements, the most complex part, because deleting those can often delete a lot of other types of results that will not need to be deleted in later queries.

I experimented with all the queries that we helpfully have in a test fixture, and found that in most cases, PostgreSQL uses a very inefficient method to look for element paths when we filter both by path and by corpus_id, compared to filtering by path alone.

Query plans

This uses the very first query that the worker results deletion will run, a SELECT to get element path sizes for optimized element deletions, with and without a corpus_id filter.

arkindex_preprod=> EXPLAIN (VERBOSE, COSTS, SETTINGS, BUFFERS, SUMMARY, MEMORY) SELECT MIN(length), MAX(length) FROM (
    SELECT array_length(p.path, 1) AS length
    FROM documents_elementpath AS p
    INNER JOIN (
        SELECT "documents_element"."id"
        FROM "documents_element"
        INNER JOIN "documents_elementpath" ON ("documents_element"."id" = "documents_elementpath"."element_id")
        WHERE
            "documents_element"."corpus_id" = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid
            AND "documents_element"."worker_version_id" = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid
            AND "documents_elementpath"."path" && (ARRAY['5f8a97ab-7533-46e9-9107-467e80e45685'::uuid])::uuid[]
    ) AS input ON (ARRAY[input.id] && p.path)
) AS lengths;

Aggregate  (cost=6849892.69..6849892.70 rows=1 width=8)
  Output: min(array_length(p.path, 1)), max(array_length(p.path, 1))
  ->  Nested Loop  (cost=18779.77..6849884.09 rows=860 width=80)
        Output: p.path
        ->  Nested Loop  (cost=504.87..526.38 rows=1 width=16)
              Output: documents_element.id
              ->  Bitmap Heap Scan on public.documents_element  (cost=504.18..512.21 rows=2 width=16)
                    Output: documents_element.id, documents_element.created, documents_element.updated, documents_element.name, documents_element.corpus_id, documents_element.type_id, documents_element.worker_version_id, documents_element.image_id, documents_element.polygon, documents_element.mirrored, documents_element.rotation_angle, documents_element.creator_id, documents_element.confidence, documents_element.worker_run_id
                    Recheck Cond: ((documents_element.worker_version_id = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid) AND (documents_element.corpus_id = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid))
                    ->  BitmapAnd  (cost=504.18..504.18 rows=2 width=0)
                          ->  Bitmap Index Scan on documents_element_worker_version_id_29d47f5a  (cost=0.00..228.85 rows=20838 width=0)
                                Index Cond: (documents_element.worker_version_id = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid)
                          ->  Bitmap Index Scan on documents_element_corpus_id_fc956c58  (cost=0.00..275.07 rows=24867 width=0)
                                Index Cond: (documents_element.corpus_id = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid)
              ->  Index Only Scan using unique_element_paths on public.documents_elementpath  (cost=0.70..7.08 rows=1 width=16)
                    Output: documents_elementpath.element_id, documents_elementpath.path
                    Index Cond: (documents_elementpath.element_id = documents_element.id)
                    Filter: (documents_elementpath.path && '{5f8a97ab-7533-46e9-9107-467e80e45685}'::uuid[])
        ->  Bitmap Heap Scan on public.documents_elementpath p  (cost=18274.89..6823441.74 rows=2591598 width=80)
              Output: p.id, p.path, p.element_id, p.ordering
              Recheck Cond: (ARRAY[documents_element.id] && p.path)
              ->  Bitmap Index Scan on documents_e_path_15a4b8_gin  (cost=0.00..17626.99 rows=2591598 width=0)
                    Index Cond: (p.path && ARRAY[documents_element.id])

arkindex_preprod=> EXPLAIN (VERBOSE, COSTS, SETTINGS, BUFFERS, SUMMARY, MEMORY) SELECT MIN(length), MAX(length) FROM (
    SELECT array_length(p.path, 1) AS length
    FROM documents_elementpath AS p
    INNER JOIN (
        SELECT "documents_element"."id"
        FROM "documents_element"
        INNER JOIN "documents_elementpath" ON ("documents_element"."id" = "documents_elementpath"."element_id")
        WHERE
            AND "documents_element"."worker_version_id" = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid
            AND "documents_elementpath"."path" && (ARRAY['5f8a97ab-7533-46e9-9107-467e80e45685'::uuid])::uuid[]
    ) AS input ON (ARRAY[input.id] && p.path)
) AS lengths;

Finalize Aggregate  (cost=6927438.37..6927438.38 rows=1 width=8)
  Output: min(array_length(p.path, 1)), max(array_length(p.path, 1))
  ->  Gather  (cost=6927438.15..6927438.36 rows=2 width=8)
        Output: (PARTIAL min(array_length(p.path, 1))), (PARTIAL max(array_length(p.path, 1)))
        Workers Planned: 2
        ->  Partial Aggregate  (cost=6926438.15..6926438.16 rows=1 width=8)
              Output: PARTIAL min(array_length(p.path, 1)), PARTIAL max(array_length(p.path, 1))
              ->  Nested Loop  (cost=17458.15..6889124.97 rows=3731318 width=80)
                    Output: p.path
                    ->  Nested Loop  (cost=234.76..124319.37 rows=1 width=16)
                          Output: documents_element.id
                          ->  Parallel Bitmap Heap Scan on public.documents_element  (cost=234.06..80244.73 rows=8682 width=16)
                                Output: documents_element.id, documents_element.created, documents_element.updated, documents_element.name, documents_element.corpus_id, documents_element.type_id, documents_element.worker_version_id, documents_element.image_id, documents_element.polygon, documents_element.mirrored, documents_element.rotation_angle, documents_element.creator_id, documents_element.confidence, documents_element.worker_run_id
                                Recheck Cond: (documents_element.worker_version_id = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid)
                                ->  Bitmap Index Scan on documents_element_worker_version_id_29d47f5a  (cost=0.00..228.85 rows=20838 width=0)
                                      Index Cond: (documents_element.worker_version_id = '5f8a97ab-7533-46e9-9107-467e80e45685'::uuid)
                          ->  Index Only Scan using unique_element_paths on public.documents_elementpath  (cost=0.70..5.07 rows=1 width=16)
                                Output: documents_elementpath.element_id, documents_elementpath.path
                                Index Cond: (documents_elementpath.element_id = documents_element.id)
                                Filter: (documents_elementpath.path && '{5f8a97ab-7533-46e9-9107-467e80e45685}'::uuid[])
                    ->  Bitmap Heap Scan on public.documents_elementpath p  (cost=17223.39..6738889.62 rows=2591598 width=80)
                          Output: p.id, p.path, p.element_id, p.ordering
                          Recheck Cond: (ARRAY[documents_element.id] && p.path)
                          ->  Bitmap Index Scan on documents_e_path_15a4b8_gin  (cost=0.00..16575.50 rows=2591598 width=0)
                                Index Cond: (p.path && ARRAY[documents_element.id])

Without a corpus_id filter, PostgreSQL uses two parallel workers to filter all elements before filtering on paths. I found this to be almost exactly twice as fast when testing on random elements locally and in prod. We should thus avoid filtering by corpus_id when we can filter by element or path instead.

Another easy optimisation we can make is to bypass the child results deletion entirely if we know there are no child paths with a not .exists(). On elements with a very large amount of paths, PostgreSQL may decide to use a sequential scan, which can be a little slow, but this only occurs on deletions that will take a very long time anyway. On the majority of cases, this will use the GIN index and be near-instantaneous.