Closed ewmson closed 3 years ago
This pull request is being automatically deployed with Vercel (learn more).
To see the status of your deployment, click below or on the icon next to each commit.
🔍 Inspect: https://vercel.com/downforacross/downforacrosscom/ibbv1kefy
✅ Preview: https://downforacrosscom-git-fork-ewmson-postgresindexperf.downforacross1.now.sh
Just merged master, then ran
psql < create_puzzles.sql
in both staging & production
~/downforacross.com/server/sql(postgres_index_perf ✔) psql -c '\d puzzles'
Table "public.puzzles"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
uid | text | | |
pid | text | | not null |
is_public | boolean | | |
uploaded_at | timestamp without time zone | | |
content | jsonb | | |
Indexes:
"puzzles_pkey" PRIMARY KEY, btree (pid)
"puzzle_name_and_title_trigrams" gist (((((content -> 'info'::text) ->> 'title'::text) || ' '::text) || ((content -> 'info'::text) ->> 'author'::text)) gist_trgm_ops)
~/downforacross.com/server/sql(postgres_index_perf ✔) PGDATABASE=production psql -c '\d puzzles'
Table "public.puzzles"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
uid | text | | |
pid | text | | not null |
is_public | boolean | | |
uploaded_at | timestamp without time zone | | |
content | jsonb | | |
Indexes:
"puzzles_pkey" PRIMARY KEY, btree (pid)
"puzzle_name_and_title_trigrams" gist (((((content -> 'info'::text) ->> 'title'::text) || ' '::text) || ((content -> 'info'::text) ->> 'author'::text)) gist_trgm_ops)
will deploy change to server after merging https://github.com/downforacross/downforacross.com/pull/153.
We add an index to speed up the queries from 100ms to 10ms.
This does make the code slightly less readable, but we should not need to change this often so I think it is fine.