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.

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:

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
...