How PostgreSQL Selects Between B-Tree and GIN Indexes

When PostgreSQL runs a query, its query planner, the part of PostgreSQL that chooses how to retrieve rows, decides how to find matching rows. A B-tree index stores values in sorted order and supports comparisons such as equality and ranges. A GIN index, short for Generalized Inverted Index, breaks values made of parts, such as arrays or JSONB documents, into searchable components. PostgreSQL uses an index only when it supports the query’s expression (the value or calculation the query uses) and operator (the comparison or search symbol). Among the plans that meet those requirements, PostgreSQL estimates which will cost less.

PostgreSQL's choice depends on the query and the data, as well as the number of rows it expects to return. A JSONB containment search, which checks whether a document includes specified content, suits GIN. A comparison against one value extracted from a JSONB document suits a B-tree expression index. PostgreSQL can also combine indexes or read the table without using an index. These rules help you read a query plan and judge whether an index fits the query.

First, check whether the index supports the query

An index’s access method, such as B-tree or GIN, defines the kinds of searches it supports. An operator class sets the rules for using an index with a particular data type, including which search operators it supports. PostgreSQL’s index-type documentation describes B-tree’s support for equality and range comparisons, along with certain beginning-anchored string patterns. B-tree can also return rows in sorted order.

GIN suits values made up of searchable parts. For JSONB, its operator class determines which document searches the index supports. PostgreSQL’s built-in JSONB GIN classes support operators such as containment, key existence, and JSONPath matching, with different operator coverage depending on the class. The JSONB indexing documentation describes those choices.

For example, a condition that checks whether data contains the JSONB value on the right, such as data @> $1::jsonb, can use a suitable JSONB GIN index. A query that extracts one value from the document and compares it, such as data ->> 'name' = $1, needs an index on that expression for PostgreSQL to use an index for the comparison:

CREATE INDEX ON records ((data ->> 'name'));

Expression indexes store the result of an expression, so the query needs to use a matching expression. For a numeric range, cast the extracted text to the intended numeric type in both the index definition and query. A whole-document GIN index does not automatically support that scalar comparison.

Then, compare the cost of eligible plans

After checking whether an index supports the query, PostgreSQL estimates the cost of possible plans. A plan is the set of steps PostgreSQL will use to find and return rows. Its estimated cost measures expected work in planner-defined units, not elapsed time in milliseconds. EXPLAIN shows the chosen plan, the estimated number of rows it will return, and its estimated startup and total costs. PostgreSQL’s EXPLAIN documentation explains how to read these figures.

The number of matching rows matters. An index scan finds matching entries, then may need to fetch the corresponding table rows from many different locations. For a small table, or when a condition matches many rows, reading the whole table can cost less. The documentation illustrates this trade-off with examples involving 1,000 of 100,000 rows and 1 of 100 rows. These figures show how the share of rows that match affects the choice; they are not universal break-even points.

PostgreSQL bases its estimates on statistics about the table. Run ANALYZE after substantial data changes when the planner’s estimates no longer match the data. If values in multiple columns tend to occur together, extended statistics can help PostgreSQL estimate the combined effect of conditions on those columns.

A query can use both indexes, or favor sorting

For some queries with separate conditions supported by B-tree and GIN indexes, PostgreSQL can combine the indexes. For some queries, it scans both indexes and makes a bitmap, a map of matching row locations, from each set of results. It combines the maps with BitmapAnd, or uses BitmapOr for suitable OR conditions. The bitmap scan documentation describes how PostgreSQL combines indexes.

A bitmap scan visits table rows in physical order, so it does not preserve the order of the original indexes. PostgreSQL may then need to sort the results. Because scanning another index and sorting add work, the planner can choose one index instead of combining them.

Sorting can also make B-tree attractive even when GIN supports the filter. A B-tree can provide rows in index order, which helps when a query uses ORDER BY with LIMIT; PostgreSQL can return the first requested rows without sorting every match. GIN does not provide ordered output. See the documentation on indexes and ORDER BY.

For queries that repeatedly filter on multiple columns, consider whether one multicolumn index fits better than separate indexes. In a multicolumn B-tree, the first columns matter most. PostgreSQL scans less of the index when the query checks those leading columns for exact values, then checks the next relevant column against a span of values. The best column order depends on the query’s filters and sort order, as described in the multicolumn index documentation.

Check the plan before changing indexes

Run EXPLAIN on the exact query first. Look for Index Scan and Seq Scan steps. Also check for Bitmap Index Scan and BitmapAnd. Compare the estimated row count with the chosen path. If PostgreSQL expects many rows to match, reading the table in a sequential scan can be a sensible choice even when an index supports the condition.

To compare estimates with what happens during execution, EXPLAIN ANALYZE runs the query and reports the actual row count and execution time. Use it with care when the query changes data or puts a heavy load on the database. Add BUFFERS to see buffer activity, which shows how much data PostgreSQL read from memory or storage. The EXPLAIN reference covers these options.

If actual row counts differ sharply from estimates, refresh the planner statistics with ANALYZE. Also check whether conditions on columns whose values tend to occur together need extended statistics. Test candidate indexes with data and queries that represent your workload; small or unrepresentative test tables can give misleading results. You can temporarily discourage a plan type to investigate alternatives, but forcing PostgreSQL to use an index does not show that the index will perform better under normal settings.

Include write and retrieval costs

Indexes speed up some reads while adding work when rows change. A changed JSONB value can generate multiple GIN index entries because GIN indexes its components. PostgreSQL can defer some GIN updates through a pending-entry list, which helps with update performance but adds work to searches; cleanup can also make some updates slower. The GIN documentation describes this behavior.

A B-tree index can support an index-only scan when it contains all the columns a query needs. PostgreSQL can then avoid fetching table rows if it has enough information to confirm that those rows are visible to the query. GIN cannot support index-only scans because its entries commonly store only parts of the original value. This difference can affect the planner’s choice even when GIN can filter the query; see index-only scan requirements.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com