Slow performance adding new Schema.

[whitingjr]

Describe the bug

UI responsiveness is less than expected when adding a new Schema.

To Reproduce

Add a new Schema. Takes around 60 seconds which is considered too long.

Version

0.12.1

Java

17

PostgreSQL

16

[lampajr]

A bit of consideration as a result of several discussions on the subject.

Root cause

The root cause has been identified in the query https://github.com/Hyperfoil/Horreum/blob/3849f043bb457deb86df5fb8ecab5fc8fc3de588/horreum-backend/src/main/java/io/hyperfoil/tools/horreum/svc/RunServiceImpl.java#L91-L103

Query that is performed during the creation or update of a Schema object. Its goal is to identify all possible runs that are referencing the updated/created schema (it is possible to upload a run referencing a schema that does not exist yet), then for each run (I) update the run_schemas table; (II) delete validation errors and (III) trigger async run recalculation.

The issue causing this slowness is that the query performs a full (sequential) scan over all the runs analyzing every jsonb data object looking for the $schema key.

                                                                                                                                                                       QUERY PLAN                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on run  (cost=0.00..103758.07 rows=7746 width=8) (actual time=37775.492..37775.493 rows=0 loops=1)
   Filter: ((NOT trashed) AND (can_view(access, owner, token) OR has_role('horreum.system'::text)) AND (((data ->> '$schema'::text) = 'urn:achme:0.1'::text) OR CASE WHEN (jsonb_typeof(data) = 'object'::text) THEN (SubPlan 1) WHEN (jsonb_typeof(data) = 'array'::text) THEN (SubPlan 2) ELSE false END OR ((metadata IS NOT NULL) AND (SubPlan 3))))
   Rows Removed by Filter: 28414
   SubPlan 1
     ->  Function Scan on jsonb_each "values"  (cost=0.00..1.25 rows=100 width=32) (actual time=0.653..0.785 rows=6 loops=25827)
   SubPlan 2
     ->  Result  (cost=0.00..2.02 rows=100 width=32) (actual time=7.914..8.241 rows=2 loops=95)
           ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32) (actual time=7.912..8.239 rows=2 loops=95)
                 ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=95)
   SubPlan 3
     ->  Result  (cost=0.00..2.02 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=6345)
           ->  ProjectSet  (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=6345)
                 ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=6345)
 Planning Time: 0.470 ms
 Execution Time: 37775.565 ms
(15 rows)

Fix proposal

Given that, in order to fix this issue, we might refactor how run_schemas table is populated and then change the query to avoid full scan over all jsonb data of all runs but rather use the run_schemas table as single source of truth for run<-->schemas association, we decided to proceed in two steps:

• [x] (short-term goal / https://github.com/Hyperfoil/Horreum/issues/1579): make the query invocation, i.e., the run_schemas update asynchronous, this way the creation/update of a schema is quite immediate.
• [ ] (long-term goal / https://github.com/Hyperfoil/Horreum/issues/1580): optimize the query to fetch all runs referencing the affected schema, this could require some broader refactoring.

[johnaohara]

On Hold due to https://github.com/Hyperfoil/Horreum/discussions/1603

[lampajr]

Given that https://github.com/Hyperfoil/Horreum/issues/1580 has been closed as not planned and https://github.com/Hyperfoil/Horreum/issues/1579 has been already fixed we can close this one as completed.