db: add index to settings.org_id

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 ms

After:

  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 ms

Co-authored-by: Erik Seliger [email protected]