The Art of PostgreSQL/

...

/

GIN Indexing

GIN Indexing

Learn about the GIN index and job opportunities.

We'll cover the following...

Before processing the tags, we create a specialized GIN index. This index access method allows PostgreSQL to index the contents of the arrays, the tags themselves, rather than each array as an opaque value.

Press + to interact
create index on hashtag using gin (hashtags);

The explain and @>

A popular tag in the dataset is #job, and we can easily see how many times it’s been used and confirm if our previous index makes sense by looking inside the hashtags array:

Press + to interact
explain (analyze, verbose, costs off, buffers)
 select count(*)
   from hashtag
  where hashtags @> array['#job'];

It will output the query plan like the following:


                              QUERY PLAN                              
══════════════════════════════════════════════════════════════════════
 Aggregate (actual time=27.227..27.227 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=3715
   ->  Bitmap Heap Scan on public.hashtag (actual time=13.023..23.453…
… rows=17763 loops=1)
         Output: id, date, uname, message, location, hashtags
         Recheck Cond: (hashtag.hashtags @> '{#job}'::text[])
         Heap Blocks: exact=3707
         Buffers: shared hit=3715
         ->  Bitmap Index
...