Something went wrong on our end. Please try again.
Created by: keegancsmith
Loading user settings is on the critical path. In some search requests we do this multiple times. To load the cascaded user settings we do several SQL queries. Most of the queries take 1-2ms. However, the query involving org_id took 30-35ms.
This commit adds in an index for settings.org_id to speed up this query. It now also takes 1 or 2ms.
SELECT s.id, s.org_id, s.user_id,
CASE WHEN users.deleted_at IS NULL THEN s.author_user_id ELSE NULL
END,
s.contents, s.created_at FROM settings s
LEFT JOIN users ON users.id=s.author_user_id
WHERE org_id=103
ORDER BY id DESC LIMIT 1;Below is an EXPLAIN ANALYSE before run against a staging environment:
Limit (cost=0.58..1517.08 rows=1 width=402) (actual time=20.476..20.478 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.58..6066.57 rows=4 width=402) (actual time=20.474..20.475 rows=0 loops=1)
-> Index Scan Backward using settings_pkey on settings s (cost=0.29..6056.55 rows=4 width=402) (actual time=20.473..20.473 rows=0 loops=1)
Filter: (org_id = 103)
Rows Removed by Filter: 75409
-> Index Scan using users_pkey on users (cost=0.29..2.51 rows=1 width=12) (never executed)
Index Cond: (id = s.author_user_id)
Planning Time: 0.199 ms
Execution Time: 20.507 msAfter:
Limit (cost=14.08..14.08 rows=1 width=402) (actual time=0.040..0.041 rows=0 loops=1)
-> Sort (cost=14.08..14.09 rows=4 width=402) (actual time=0.039..0.040 rows=0 loops=1)
Sort Key: s.id DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.58..14.06 rows=4 width=402) (actual time=0.009..0.009 rows=0 loops=1)
-> Index Scan using settings_org_id_idx on settings s (cost=0.29..4.04 rows=4 width=402) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (org_id = 103)
-> Index Scan using users_pkey on users (cost=0.29..2.51 rows=1 width=12) (never executed)
Index Cond: (id = s.author_user_id)
Planning Time: 0.247 ms
Execution Time: 0.067 msCo-authored-by: Erik Seliger erikseliger@me.com