Query to get a form's submission encryption keys can sometimes take a long time

[lognaturel]

Problem description

Specific forms' submission pages can take a long time to load. This seems to be because a request to submissions/keys takes several seconds to get a response.

URL of the page

https://test.getodk.cloud/#/projects/533/forms/add_student/submissions

Steps to reproduce the problem

The link above should systematically reproduce an ~8s response time. Other submission lists like this one which seem like they should be similar are fast to load.

Screenshot

Please don't attach images of QR codes, as those provide access to the server.

Expected behavior

Submission tables load quickly

Central version shown in version.txt

versions:
f21b33acc8dae215e54485849e7dd59167e6f433 (v2024.1.0)
 3fb0c22b1cbdc3a6004963afcc3847a82c09307d client (v2024.1.0)
 b4754cf52bfa64b1ca841bc9ccb64a38726398e8 server (v2024.1.0)

Browser version

Around when did you see the problem (in UTC)?

Other notes (if any)

[ktuite]

I believe it's related to the # of versions of the form! There are 14 past versions of that form.

Using EXPLAIN ANALYZE on that keys query for that form and comparing it to a new copy of the same form, I see

 Merge Join  (cost=11545.97..11546.59 rows=1 width=430) (actual time=3271.469..3271.474 rows=0 loops=1)
   Merge Cond: (keys.id = form_defs."keyId")
   ->  Sort  (cost=12.34..12.64 rows=120 width=430) (actual time=0.103..0.105 rows=1 loops=1)
         Sort Key: keys.id DESC
         Sort Method: quicksort  Memory: 86kB
         ->  Seq Scan on keys  (cost=0.00..8.20 rows=120 width=430) (actual time=0.045..0.062 rows=120 loops=1)
   ->  Sort  (cost=11533.63..11533.63 rows=1 width=4) (actual time=3271.363..3271.366 rows=0 loops=1)
         Sort Key: form_defs."keyId" DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Group  (cost=11533.60..11533.61 rows=1 width=4) (actual time=3271.361..3271.364 rows=0 loops=1)
               Group Key: form_defs."keyId"
               ->  Sort  (cost=11533.60..11533.60 rows=1 width=4) (actual time=3271.360..3271.363 rows=0 loops=1)
                     Sort Key: form_defs."keyId"
                     Sort Method: quicksort  Memory: 25kB
                     ->  Nested Loop  (cost=0.70..11533.59 rows=1 width=4) (actual time=3271.357..3271.360 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.28..11529.68 rows=1 width=8) (actual time=3271.357..3271.358 rows=0 loops=1)
                                 Join Filter: (form_defs.id = submission_defs."formDefId")
                                 Rows Removed by Join Filter: 30212
                                 ->  Index Scan using form_defs_formid_publishedat_index on form_defs  (cost=0.28..8.30 rows=1 width=8) (actual time=0.007..0.071 rows=14 loops=1)
                                       Index Cond: ("formId" = 5353)
                                 ->  Seq Scan on submission_defs  (cost=0.00..11494.06 rows=2186 width=8) (actual time=1.228..233.430 rows=2158 loops=14)
                                       Filter: (current AND ("localKey" IS NOT NULL))
                                       Rows Removed by Filter: 152109
                           ->  Index Scan using submissions_pkey on submissions  (cost=0.42..3.90 rows=1 width=4) (never executed)
                                 Index Cond: (id = submission_defs."submissionId")
                                 Filter: (NOT draft)
 Planning Time: 0.568 ms
 Execution Time: 3271.543 ms
(28 rows)

in particular

->  Index Scan using form_defs_formid_publishedat_index on form_defs 
(cost=0.28..8.30 rows=1 width=8) (actual time=0.007..0.071 rows=14 loops=1)

when another form with a faster query only had rows=2. It seems like the form_defs (formId, publishedAt) index is returning 14 rows instead of 1 or 2, which is getting used in the rest of the query in an inefficient way. Adding versions definitely slows this down again.

(note: i did get llama3's help interpreting this)

I'm thinking now about how to optimize the query...