Skip to content

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.