- Pretty permalinks for this post:
- https://fireburn.ru/posts/kittybox-postgres-experiment-2
Thinking about creating a Microsub server, I remembered that my new shiny Postgres backend can easily answer if I already left a like on a post or not:
kittybox=# CREATE INDEX likes ON kittybox.mf2_json USING GIN ((mf2['properties']['like-of']));
CREATE INDEX
kittybox=# EXPLAIN SELECT EXISTS (SELECT uid FROM kittybox.mf2_json WHERE mf2['properties']['like-of'] ? 'https://aaronparecki.com/2018/12/25/17/');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Result (cost=11.28..11.29 rows=1 width=1)
InitPlan 1 (returns $0)
-> Bitmap Heap Scan on mf2_json (cost=8.09..43.20 rows=11 width=0)
Recheck Cond: (mf2['properties'::text]['like-of'::text] ? 'https://aaronparecki.com/2018/12/25/17/'::text)
-> Bitmap Index Scan on likes (cost=0.00..8.08 rows=11 width=0)
Index Cond: (mf2['properties'::text]['like-of'::text] ? 'https://aaronparecki.com/2018/12/25/17/'::text)
(6 rows)
Nice. Indexes are awesome.