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 msAfter
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)