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)
