B-tree vs GIN vs GiST indexes: a practical PostgreSQL guide

What you are really choosing when you pick an index

Most PostgreSQL index problems start the same way: a list view feels fast at 1,000 rows, then drags at 1,000,000. Or a search box that worked in testing turns into a second-long pause in production. When that happens, it’s tempting to ask, “Which index is best?” A better question is: “What does this screen ask the database to do?”

The same table can need different index types because different screens read it in different ways. One view filters by a single status and sorts by created_at. Another does full-text search. Another checks whether a JSON field contains a key. Another finds items near a point on a map. Those are different access patterns, so one index type won’t win everywhere.

That’s what you’re choosing when you pick an index: how the app accesses the data. Are you mostly doing exact matches, ranges, and sorting? Are you searching inside documents or arrays? Are you asking “what’s close to this location” or “what overlaps this range”? The answer drives whether B-tree, GIN, or GiST is the right fit.

B-tree, GIN, and GiST in plain language

Picking an index is less about the column type and more about what your queries do with it. PostgreSQL chooses indexes based on operators like =, <, @>, or @@, not on whether the column is “text” or “json”. That’s why the same field can need different indexes on different screens.

B-tree: fast for ordered lookups

B-tree is the default and the most common choice. It shines when you filter by exact value, filter by a range, or need results in a specific order.

A typical example is an admin list filtered by status and sorted by created_at. A B-tree index on (status, created_at) can help both the filter and the sort. B-tree is also the usual tool for uniqueness (unique constraints).

GIN: fast when each row contains many searchable keys

GIN is built for “does this row contain this term/value?” questions, where one row can match many keys. Common examples are full-text search (a document contains words) and JSONB/array membership (JSON contains a key/value).

Think of a customer record with a JSONB preferences object, and a screen that filters for users where preferences contains {"newsletter": true}. That’s a GIN-style lookup.

GiST: flexible for ranges, geo, and similarity

GiST is a general framework used by data types that don’t fit simple ordering. It’s a natural fit for ranges (overlaps, contains), geometric and geographic queries (near, within), and some similarity searches.

When deciding between B-tree vs GIN vs GiST indexes, start by writing down the operators your busiest screens use. The right index is usually clearer after that.

Decision table for common screens (filters, search, JSON, geo)

Most apps only need a few index patterns. The trick is matching screen behavior to the operators your queries use.

Two indexes can coexist when endpoints differ. For example, an admin list might need a B-tree on (tenant_id, created_at) for fast sorting, while a search page needs a GIN index for @@. Keep both only if both query shapes are common.

If you’re unsure, look at the operator first. Indexes help when the database can use them to skip large parts of the table.

Filters and sorting: where B-tree usually wins

For most everyday screens, B-tree is the boring choice that works. If your query looks like “pick rows where a column equals a value, maybe sort them, then show page 1,” B-tree is usually the first thing to try.

Equality filters are the classic case. Columns like status, user_id, account_id, type, or tenant_id show up constantly in dashboards and admin panels. A B-tree index can jump straight to matching values.

Range filters also fit B-tree well. When you filter by time or numeric ranges, the ordered structure helps: created_at >= ..., price BETWEEN ..., id > .... If your UI offers “Last 7 days” or “$50 to $100,” B-tree is doing exactly what you want.

Sorting and pagination are where B-tree can save you the most work. If the index order matches your ORDER BY, PostgreSQL can often return rows already sorted instead of sorting a big set in memory.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Composite indexes follow one simple rule: PostgreSQL can only use the leading part of the index efficiently. Think “left to right.” With (user_id, status, created_at), queries that filter by user_id (and optionally status) benefit. A query that filters only by status usually won’t.

Partial indexes are a strong upgrade when your screen only cares about a slice of data. Common slices are “only active rows,” “not soft-deleted,” or “recent activity.” They keep the index smaller and faster.

High-cardinality columns and the cost of extra indexes

High-cardinality columns have lots of unique values, like user_id, order_id, email, or created_at down to the second. Indexes tend to shine here because a filter can quickly narrow results to a tiny slice of the table.

Low-cardinality columns are the opposite: booleans and small enums like is_active, status IN ('open','closed'), or plan IN ('free','pro'). An index on these often disappoints because each value matches a big chunk of rows. PostgreSQL may correctly choose a sequential scan since jumping through the index still means reading many table pages.

Another subtle cost is fetching rows. Even if an index finds matching IDs quickly, the database may still have to visit the table for the rest of the columns. If your query only needs a few fields, a covering index can help, but it also makes the index bigger and more expensive to maintain.

Every extra index has a write price. Inserts must write into each index. Updates that change indexed columns must update those entries too. Adding indexes “just in case” can slow down the whole app, not only one screen.

Practical guidance:

• Start with 1-2 workhorse indexes per busy table, based on real filters and sorts.
• Favor high-cardinality columns used in WHERE and ORDER BY.
• Be cautious indexing booleans and tiny enums unless they combine with another selective column.
• Add a new index only after you can name the exact query it will speed up.

Example: a support ticket list filtered by assignee_id (high-cardinality) benefits from an index, while is_archived = false alone often doesn’t.

Search screens: full-text, prefixes, and “contains”

Search boxes look simple, but users expect a lot: multiple words, different word forms, and reasonable ranking. In PostgreSQL, that’s usually full-text search: you store a tsvector (prepared text) and query it with a tsquery (what the user typed, parsed into terms).

For full-text search, GIN is the common default because it’s fast at answering “does this document contain these terms?” across many rows. The tradeoff is heavier writes: inserting and updating rows tends to cost more.

GiST can also work for full-text search. It’s often smaller and cheaper to update, but usually slower for reads than GIN. If your data changes constantly (for example, event-like tables), that read-write balance can matter.

Prefix search is not full-text

Prefix search means “starts with,” like searching customers by an email prefix. That’s not what full-text search is built for. For prefix patterns, a B-tree index can help (often with the right operator class) because it matches the way strings are ordered.

For “contains” searches like ILIKE '%error%', B-tree usually can’t help. That’s where trigram indexing or a different search approach becomes relevant.

When users want filters plus text search

Most real screens combine search with filters: status, assignee, date range, tenant, and so on. A practical setup is:

• A GIN (or sometimes GiST) index for the tsvector column.
• B-tree indexes for the most selective filters (for example, account_id, status, created_at).
• A strict “keep it minimal” rule, because too many indexes make writes slower.

Example: a support tickets screen that searches “refund delayed” and filters to status = 'open' and a specific account_id. Full-text gets you relevant rows, while B-tree helps PostgreSQL narrow to the right account and status quickly.

JSONB fields: choosing between GIN and targeted B-tree indexes

JSONB is great for flexible data, but it can turn into slow queries if you treat it like a normal column. The core decision is simple: do you search “anywhere in this JSON,” or do you filter on a few specific paths over and over?

For containment queries like metadata @> '{"plan":"pro"}', a GIN index is usually the first pick. It’s built for “does this document contain this shape?” and it also supports key-existence checks such as ?, ?|, and ?&.

If your app mostly filters by one or two JSON fields, a targeted B-tree expression index is often faster and smaller. It also helps when you need sorting or numeric comparisons on extracted values.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

A good rule of thumb:

• Use GIN when users search multiple keys, tags, or nested structures.
• Use B-tree expression indexes when users filter on specific paths repeatedly.
• Index what appears on real screens, not everything.
• If performance depends on a few JSON keys you always use, consider promoting them into real columns.

Example: a support screen might filter tickets by metadata->>'priority' and sort by created_at. Index the JSON priority path and the normal created_at column. Skip a broad GIN index unless users also search tags or nested attributes.

Geo and range queries: where GiST fits best

Geo and range screens are where GiST often becomes the obvious choice. GiST is built for “does this thing overlap, contain, or sit near that thing?” rather than “is this value equal to that value?”

Geo data usually means points (a store location), lines (a route), or polygons (a delivery zone). Common screens include “stores near me,” “jobs within 10 km,” “show items inside this map box,” or “is this address inside our service area?” A GiST index (most often through PostGIS geometry or geography types) speeds up these spatial operators so the database can skip most rows instead of checking every shape.

Ranges are similar. PostgreSQL has range types like daterange and int4range, and the typical question is overlap: “does this booking collide with an existing booking?” or “show subscriptions active during this week.” GiST supports overlap and containment operators efficiently, which is why it’s common in calendars, scheduling, and availability checks.

B-tree can still matter on geo-like screens. Many pages first filter by tenant, status, or time, then apply a spatial condition, then sort. For example: “only my company’s deliveries, from the last 7 days, nearest first.” GiST handles the spatial part, but B-tree helps with selective filters and sorting.

How to choose an index step by step

Index choice is mostly about the operator, not the column name. The same column can need different indexes depending on whether you use =, >, LIKE 'prefix%', full-text search, JSON containment, or geo distance.

Read the query like a checklist: WHERE decides what rows qualify, JOIN decides how tables connect, ORDER BY decides the output order, and LIMIT decides how many rows you actually need. The best index is often the one that helps you find the first 20 rows fast.

A simple process that works for most app screens:

1. Write down the exact operators your screen uses (example: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Start with an index that matches the most selective filter or the default sort. If the screen sorts by created_at DESC, begin there.
3. Add a composite index only when you see the same filters together all the time. Put equality columns first, then range columns, then the sort key.
4. Use a partial index when you always filter to a subset (example: only status = 'open'). Use an expression index when you query a computed value (example: lower(email) for case-insensitive lookups).
5. Validate with EXPLAIN ANALYZE. Keep it if it cuts execution time and reduces rows read by a lot.

Concrete example: a support dashboard might filter tickets by status and sort by newest. A B-tree on (status, created_at DESC) is a strong first try. If the same screen also filters on a JSONB flag like meta @> '{"vip": true}', that’s a different operator and usually needs a separate JSON-focused index.

Common mistakes that waste time (and slow down writes)

A common way to get disappointed is to pick the “right” index type for the wrong operator. PostgreSQL can only use an index when the query matches what the index was built to answer. If your app uses ILIKE '%term%', a plain B-tree on that text column will sit unused, and you’ll still scan the table.

Another trap is building giant multi-column indexes “just in case.” They look safe, but they’re expensive to maintain and often don’t match real query patterns. If the leftmost columns aren’t used in the filter, the rest of the index might not help.

Low-selectivity columns are also easy to over-index. A B-tree on a boolean like is_active or a status with only a few values can be nearly useless unless you make it a partial index that matches what you actually filter for.

JSONB adds its own gotchas. A broad GIN index can be great for flexible filters, but many JSONB path checks are faster with an expression index on the extracted value. If your screen always filters by payload->>'customer_id', indexing that expression is often smaller and faster than indexing the whole document.

Finally, every extra index taxes writes. On frequently updated tables (think tickets or orders), each insert and update has to update every index.

Before adding an index, pause and check:

• Does the index match the exact operator your query uses?
• Can you replace a wide multi-column index with one or two focused ones?
• Should this be a partial index to avoid low-selectivity noise?
• For JSONB, would an expression index fit the screen better?
• Is the table write-heavy enough that the index cost outweighs the read gain?

Quick checks before you add (or keep) an index

Before you create a new index, get specific about what the app actually does. A “nice to have” index often turns into slower writes and more storage with little payoff.

Start with your top three screens (or API endpoints) and write down the exact query shape: filters, sort order, and what the user types. Many “index problems” are really “unclear query problems,” especially when people debate B-tree vs GIN vs GiST indexes without naming the operator.

A simple checklist:

• Pick 3 real screens and list their exact WHERE and ORDER BY patterns (including direction and NULL handling).
• Confirm the operator type: equality (=), range (>, BETWEEN), prefix, contains, overlap, or distance.
• Choose one index per common screen pattern, test it, and keep only the ones that measurably reduce time or reads.
• If the table is write-heavy, be strict: extra indexes multiply write cost and can increase vacuum pressure.
• Re-check after feature changes. A new filter, a new default sort, or switching from “starts with” to “contains” can make the old index irrelevant.

Example: a dashboard adds a new default sort last_activity DESC. If you only indexed status, the filter may still be fast, but the sort now forces extra work.

Example: mapping real app screens to the right index

A decision table only helps if you can map it to real screens you ship. Here are three common screens and how they line up with index choices.

A practical way to apply this is to start from the UI:

• List every control that narrows results (filters).
• Note the default sort order.
• Be specific about search behavior (full-text vs starts-with vs contains).
• Call out “special” fields (JSONB, geo, ranges).

Next steps: make indexing part of your build process

Good indexes follow your screens: the filters people click, the sort order they expect, and the search box they actually use. Treat indexing as a habit during development and you’ll avoid most performance surprises later.

Keep it repeatable: identify the 1-3 queries a screen runs, add the smallest index that matches them, test with realistic data, then remove what doesn’t pull its weight.

If you’re building an internal tool or customer portal, plan index needs early because these apps often grow by adding more filters and more list screens. If you build with AppMaster (appmaster.io), it helps to treat each screen’s filter and sort configuration as a concrete query pattern, then add only the indexes that match those real clicks.