Optimize worker results deletion in a parent element

Some worker_results_delete tasks on parent elements (not on a whole corpus) have timed out after an hour in prod. This is likely due to issues with ElementPaths, since this only occurs when using a parent_id argument.

I tried to run the task with some SQL query logging to see the timings in prod directly:

import logging
from django.db import connections
logging.getLogger('django.db.backends').setLevel(logging.DEBUG)
for connection in connections:
    connection.force_debug_cursor = True

from arkindex.documents.tasks import worker_results_delete
worker_results_delete(corpus_id='d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f', version_id='7fa9cbc8-768e-48ce-977b-fdf5c5e2207d', parent_id='01509c7a-f8f8-4f87-8b17-14ec713af644')

DEBUG (70.760) 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" LEFT OUTER JOIN "documents_elementpath" ON ("documents_element"."id" = "documents_elementpath"."element_id") WHERE ("documents_element"."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND "documents_element"."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND ("documents_element"."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR "documents_elementpath"."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))) as input on (array[input.id] && p.path)
                ) as lengths
                ; args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (7.552) DELETE FROM "documents_transcriptionentity" WHERE "documents_transcriptionentity"."id" IN (SELECT V0."id" FROM "documents_transcriptionentity" V0 INNER JOIN "documents_transcription" V1 ON (V0."transcription_id" = V1."id") WHERE V1."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[])))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (106.087) DELETE FROM "documents_transcription" WHERE "documents_transcription"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (106.459) DELETE FROM "dataimport_workeractivity" WHERE "dataimport_workeractivity"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (109.711) DELETE FROM "documents_metadata" WHERE "documents_metadata"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (108.520) DELETE FROM "documents_classification" WHERE "documents_classification"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (12.538) DELETE FROM "documents_selection" WHERE "documents_selection"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
&DEBUG (95.349) DELETE FROM "dataimport_dataimportelement" WHERE "dataimport_dataimportelement"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (8.596) UPDATE "dataimport_dataimport" SET "element_id" = NULL WHERE "dataimport_dataimport"."element_id" IN (SELECT U0."id" FROM "documents_element" U0 LEFT OUTER JOIN "documents_elementpath" U3 ON (U0."id" = U3."element_id") WHERE (U0."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U3."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (107.326) 
            WITH element_ids (id) AS (
                DELETE FROM documents_elementpath
                WHERE element_id IN (SELECT "documents_element"."id" FROM "documents_element" LEFT OUTER JOIN "documents_elementpath" ON ("documents_element"."id" = "documents_elementpath"."element_id") WHERE ("documents_element"."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND "documents_element"."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND ("documents_element"."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR "documents_elementpath"."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[])))
                RETURNING element_id
            )
            DELETE FROM documents_element element
            USING element_ids
            WHERE element.id = element_ids.id
            ; args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (65.921) SELECT "documents_element"."id" FROM "documents_element" LEFT OUTER JOIN "documents_elementpath" ON ("documents_element"."id" = "documents_elementpath"."element_id") WHERE ("documents_element"."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND "documents_element"."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND ("documents_element"."id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR "documents_elementpath"."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[])); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (0.060) DELETE FROM "documents_classification" WHERE "documents_classification"."id" IN (SELECT U0."id" FROM "documents_classification" U0 INNER JOIN "documents_element" U1 ON (U0."element_id" = U1."id") LEFT OUTER JOIN "documents_elementpath" U4 ON (U1."id" = U4."element_id") WHERE (U1."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."element_id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U4."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (0.044) DELETE FROM "documents_transcriptionentity" WHERE "documents_transcriptionentity"."id" IN (SELECT U0."id" FROM "documents_transcriptionentity" U0 INNER JOIN "documents_transcription" U1 ON (U0."transcription_id" = U1."id") INNER JOIN "documents_element" U2 ON (U1."element_id" = U2."id") LEFT OUTER JOIN "documents_elementpath" U5 ON (U2."id" = U5."element_id") WHERE (U2."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U1."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U1."element_id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U5."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (0.044) DELETE FROM "documents_transcriptionentity" WHERE "documents_transcriptionentity"."id" IN (SELECT U0."id" FROM "documents_transcriptionentity" U0 INNER JOIN "documents_transcription" U1 ON (U0."transcription_id" = U1."id") INNER JOIN "documents_element" U2 ON (U1."element_id" = U2."id") INNER JOIN "documents_entity" U4 ON (U0."entity_id" = U4."id") LEFT OUTER JOIN "documents_elementpath" U7 ON (U2."id" = U7."element_id") WHERE ((U2."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid OR U4."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid) AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U1."element_id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U7."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (0.005) DELETE FROM "documents_transcription" WHERE "documents_transcription"."id" IN (SELECT U0."id" FROM "documents_transcription" U0 INNER JOIN "documents_element" U1 ON (U0."element_id" = U1."id") LEFT OUTER JOIN "documents_elementpath" U4 ON (U1."id" = U4."element_id") WHERE (U1."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."element_id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U4."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (182.238) DELETE FROM "documents_metadata" WHERE "documents_metadata"."id" IN (SELECT U0."id" FROM "documents_metadata" U0 INNER JOIN "documents_element" U1 ON (U0."element_id" = U1."id") LEFT OUTER JOIN "documents_elementpath" U4 ON (U1."id" = U4."element_id") WHERE (U1."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."element_id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U4."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))
DEBUG (48.758) DELETE FROM "dataimport_workeractivity" WHERE "dataimport_workeractivity"."id" IN (SELECT U0."id" FROM "dataimport_workeractivity" U0 INNER JOIN "documents_element" U1 ON (U0."element_id" = U1."id") LEFT OUTER JOIN "documents_elementpath" U4 ON (U1."id" = U4."element_id") WHERE (U1."corpus_id" = 'd8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'::uuid AND U0."worker_version_id" = '7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'::uuid AND (U0."element_id" = '01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid OR U4."path" && ARRAY['01509c7a-f8f8-4f87-8b17-14ec713af644'::uuid]::uuid[]))); args=(UUID('d8e5f6ef-ec89-46a5-9a0e-35687fa6bc5f'), UUID('7fa9cbc8-768e-48ce-977b-fdf5c5e2207d'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'), UUID('01509c7a-f8f8-4f87-8b17-14ec713af644'))

Edited Jan 24, 2022 by Erwan Rouchet