CreateElements causes a full table scan when retrieving the next available path ordering
Some processes failed in prod due to timeouts on CreateElements
:
https://arkindex.teklia.com/process/25a8c0ae-4947-46f0-b102-39c9ab524ba2/0
https://arkindex.teklia.com/process/9b73ef5f-0bfc-4d4a-806c-f3d0f673e7c7/0
Testing with a dev server showed nothing was amiss, but the endpoint was slow in preprod and timed out almost every time in prod. Running each query one by one in psql
in preprod showed that out of about 5 seconds of total time for a random test element with almost no children, 4.5 seconds were taken by the next_ordering query.
This query used path__last
, which when used alone causes a full table scan of documents_elementpath
. Adding path__overlap
along with it causes Postgres to use the GIN index first, then filter the remaining paths, which took 40 milliseconds instead of 4.5 seconds. This can still be slow with elements with tons of descendants such as a Balsac register, but adding elements in bulk to those huge elements is much rarer.
We can look into adding path__overlap
to every single query where path__last
is used, and see if this makes query run times better.