PostgreSQL Index Types and Index Selection

Performance / Guide 34 of 47 / Advanced

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Test data400,000 rows, 2 core / 4 GB host
• TimeAbout 17 min

GIN is the index type for containment queries on jsonb and arrays. It cannot help extraction predicates like payload->>'tier' = 'gold', which need a btree on the expression instead.

This guide includes

Use this when choosing an index type rather than reaching for a btree by reflex. This matters because a partial index can be a fifth the size and still answer its query faster - and a GIN index can turn out not to help at all.

• indexing only the rows you actually query
• measuring what the partial index does, including the buffers it touches
• adding a GIN index, and finding that it does not help

Before you start

• The events table with its jsonb payload column.

1. 1

   Index only the rows you query

2. 2

   Measure what the partial index does

3. 3

   Add a GIN index, and find it does not help

Official sources

• PostgreSQL 18 Documentation - Using EXPLAIN
• PostgreSQL 18 Documentation - Index Types
• PostgreSQL 18 Documentation - Partial Indexes
• PostgreSQL 18 Documentation - Routine Vacuuming
• PostgreSQL 18 Documentation - pg_stat_statements