Dear Support Team,

We are encountering a performance bottleneck when retrieving application data from our FusionAuth instance (version 1.48.2) using a specific SQL query. Our aim is to fetch active applications along with their associated roles, but the response times are significantly higher than expected.

The SQL query in question is as follows:

SELECT a.id AS a_id, ...
FROM applications AS a 
LEFT OUTER JOIN application_roles AS ar ON a.id = ar.applications_id
WHERE a.active = true
ORDER BY a.insert_instant DESC

The execution plan generated by EXPLAIN ANALYZE reveals that the query is experiencing issues primarily with sorting and hash right join operations:

Sort (cost=24619.55..24710.22 rows=36271 width=2291) (actual time=118.838..137.319 rows=36271 loops=1)
  Sort Key: a.insert_instant DESC
  Sort Method: external merge Disk: 52888kB
  -> Hash Right Join (cost=369.20..1326.65 rows=36271 width=2291) (actual time=5.003..24.462 rows=36271 loops=1)
        Hash Cond: (ar.applications_id = a.id)
        -> Seq Scan on application_roles ar (cost=0.00..861.93 rows=36293 width=68) (actual time=0.014..4.387 rows=36290 loops=1)
        -> Hash (cost=348.54..348.54 rows=1653 width=2223) (actual time=4.967..4.969 rows=1710 loops=1)
              -> Seq Scan on applications a (cost=0.00..348.54 rows=1653 width=2223) (actual time=0.027..2.841 rows=1710 loops=1)
                    Filter: active
                    Rows Removed by Filter: 1
Planning Time: 1.347 ms
Execution Time: 147.884 ms

As observed, the "Sort Method: external merge Disk: 52888kB" indicates a significant amount of disk usage for sorting, which likely contributes to the slowdown. Additionally, the sequential scans and hash operations suggest there may be optimization opportunities, possibly through indexing or query restructuring.

We seek your guidance on how to optimize this query or our database configuration to improve the performance of this data retrieval process. Any insights or recommendations on best practices for managing large sets of application and role data within FusionAuth would be greatly appreciated.

Thank you for your assistance.

Best regards