Skip to content

perf: Faster global settings lookup

Administrator requested to merge es/faster-settings into main

Created by: eseliger

Saw this be "rather slow", so I spent 10 minutes investigating. Outcome: We now save around 2ms on reading global settings. Given this query is run pretty often, I think it's worth the additional very small index.

Before

                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.58..6.92 rows=1 width=485) (actual time=1.907..1.908 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=0.58..11307.08 rows=1785 width=485) (actual time=1.906..1.906 rows=1 loops=1)
         ->  Index Scan Backward using settings_pkey on settings s  (cost=0.29..9517.39 rows=1785 width=485) (actual time=1.899..1.899 rows=1 loops=1)
               Filter: ((user_id IS NULL) AND (org_id IS NULL))
               Rows Removed by Filter: 3705
         ->  Index Scan using users_pkey on users  (cost=0.29..1.00 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1)
               Index Cond: (id = s.author_user_id)
 Planning Time: 0.287 ms
 Execution Time: 1.932 ms

After

                                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1.70 rows=1 width=485) (actual time=0.014..0.014 rows=1 loops=1)
   ->  Nested Loop Left Join  (cost=0.56..2032.29 rows=1785 width=485) (actual time=0.013..0.013 rows=1 loops=1)
         ->  Index Scan using erik_test_settings on settings s  (cost=0.28..242.61 rows=1785 width=485) (actual time=0.010..0.010 rows=1 loops=1)
         ->  Index Scan using users_pkey on users  (cost=0.29..1.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
               Index Cond: (id = s.author_user_id)
 Planning Time: 0.283 ms
 Execution Time: 0.033 ms
(7 rows)

Merge request reports

Loading