Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

GIN Indexing

Explore how to create and use GIN indexes in PostgreSQL to efficiently index and query array contents, such as hashtags. Understand practical queries for analyzing popular tags, joining location data, and comparing GIN indexing with lookup tables to optimize database performance.

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.

PostgreSQL
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:

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