PostgreSQL index types: which index to use and when
Use a B-tree unless the data type or the operator in your WHERE clause says otherwise. PostgreSQL ships six index methods — B-tree, hash, GiST, SP-GiST, GIN and BRIN — and the choice is made for you by what you are querying: containment and full-text need GIN, geometry and ranges need GiST, huge naturally ordered tables suit BRIN.
Key facts
| Index method | Operators it serves | Use when | |---|---|---| | B-tree | <, <=, =, >=, >, BETWEEN, IN, IS NULL, prefix LIKE, ORDER BY, MIN/MAX | Almost always. It is the default when USING is omitted (CREATE INDEX) | | Hash | = only | Equality-only lookups on a very wide key, where you have measured it smaller than the B-tree | | GIN | @>, <@, ?, ?&, ?\|, &&, @@ — containment and membership over composite values | Arrays, JSONB, tsvector full-text search, trigram substring search | | GiST | &&, @>, <<, -\|-, <-> and whatever the operator class defines | Geometry, ranges, ltree, nearest-neighbour ordering, exclusion constraints | | SP-GiST | Same shape as GiST, via non-overlapping partitions | Naturally, unevenly partitioned data: quadtrees, k-d trees, radix trees over text | | BRIN | <, <=, =, >=, > against per-block-range summaries | Very large tables whose physical order correlates with the indexed column (append-only timestamps) | | pgvector HNSW / IVFFlat | <->, <=>, <#> approximate nearest neighbour | Vector similarity search — supplied by the pgvector extension, not by core PostgreSQL | | Fleet observation | Across 31 monitored instances, 67.7% fail the duplicate-index check and 58.1% carry at least one index with zero recorded scans (MyDBA F3_duplicate_indexes / F1_unused_indexes, 2026-09-17) | The common index mistake is not picking the wrong method — it is keeping indexes nobody uses |
Two of those methods are extensible frameworks rather than single algorithms. GiST and SP-GiST define an interface — consistent, union, penalty, picksplit and friends — that an operator class implements for a particular data type (GiST extensibility). That is how PostGIS, pg_trgm, ltree and hstore add index support without patching PostgreSQL, and it is also how pgvector adds HNSW and IVFFlat as index access methods of their own.
Choosing an index type
The question "which index type should I use?" is nearly always answered before you ask it. You do not pick a method and then find queries for it; you look at the operator in the predicate and the method follows. A B-tree can only be used where there is a total order on the keys — every value is less than, equal to or greater than every other. That assumption is what makes <, = and > cheap, and it is exactly what a polygon, a time range, a JSONB document or a trigram set does not have. When the operator you need has no B-tree operator class, the method is chosen for you.
So the decision procedure is short. Write the predicate down exactly as the query expresses it, operator included. Equality, a range, IN or a prefix LIKE on an ordinary scalar column means B-tree, and you can stop there. A containment or membership operator (@>, ?, &&, @@) puts you in GIN territory; a geometric, range or distance operator puts you in GiST territory. A huge append-only table filtered on the column it is physically ordered by is the BRIN case. Only then is it worth asking whether the non-default method actually beats the default on your data.
| What you are querying | Method | Why | |---|---|---| | Equality or range on a scalar column | B-tree | The default; serves ordering and MIN/MAX too (index types) | | A primary key, unique constraint or ON CONFLICT arbiter | B-tree | Only B-tree indexes can be declared UNIQUE (CREATE INDEX) | | ORDER BY col you want to satisfy without a sort | B-tree | Leaf pages are already in key order (indexes and ORDER BY) | | JSONB key lookup or containment (@>, ?) | GIN | Indexes each key/value inside the document (JSON indexing) | | Array membership (@>, &&) | GIN | One index entry per element, many rows per entry (GIN intro) | | Full-text search on tsvector (@@) | GIN | Roughly three times faster to search than GiST, three times slower to build (text search indexes) | | Substring match LIKE '%foo%' | GIN (or GiST) with pg_trgm | A B-tree has no prefix to seek to (pg_trgm) | | Geometry, bounding-box overlap | GiST | PostGIS and the built-in geometric types ship GiST operator classes (GiST opclasses) | | Range overlap, containment, adjacency | GiST | &&, @>, -\|- on tstzrange and friends (range indexing) | | "Nearest N to this point" (ORDER BY col <-> x LIMIT k) | GiST | The only built-in method with KNN ordering support (ordering operators) | | "This room cannot be double-booked" | GiST | Exclusion constraints use EXCLUDE USING gist (exclusion constraints) | | Unevenly clustered points or shared-prefix text | SP-GiST | Non-overlapping partitions avoid GiST's repeated descents (SP-GiST) | | A date range on a 500-million-row append-only table | BRIN | Summarises block ranges instead of rows, so the index is tiny (BRIN intro) | | Equality on a 200-character external reference | Hash | Stores a 32-bit hash however wide the value is (hash indexes) | | Vector similarity | pgvector HNSW or IVFFlat | Supplied by the extension (pgvector) |
B-tree
A B-tree is a balanced tree of 8 kB pages whose leaves hold sorted index tuples, each an indexed key plus a ctid — the physical address of a row in the heap. Every leaf sits at the same depth, and leaves are linked to their siblings, which is what makes a range scan a sequential walk rather than a repeated descent from the root (B-tree implementation).
Everything a B-tree does well follows from that sorted structure. Anything the planner can express as "start here in the sorted order and walk" works: equality, the four inequality operators, BETWEEN, IN, IS NULL, and prefix LIKE 'abc%' — the last of those only when the index's collation supports it, which in a non-C collation means adding a text_pattern_ops operator class (operator classes). The same ordering lets an index satisfy an ORDER BY without a sort node, and turns MIN/MAX into a one-row scan of the relevant end of the tree.
The one B-tree rule people trip over is the leftmost prefix. An index on (a, b, c) sorts by a, then b within equal a, then c, and the planner can only narrow the scan using a leading prefix of that list (multicolumn indexes). A predicate of b = 2 AND c = 3 has no useful prefix. Worse, a range on the leading column stops the following columns from narrowing anything, because the rows matching a > 1 are not sorted by b overall. Put equality columns first, the range column last.
Read: What is a B-tree index in PostgreSQL and how does it work?
GIN
GIN — Generalised Inverted Index — is for values that contain many indexable items. A JSONB document, a text array, a tsvector: each has keys inside it, and GIN builds an entry per key pointing at every row that contains it (GIN introduction). That is precisely backwards from a B-tree, which indexes the value as an atom, and it is why WHERE tags @> '{sale}' can use a GIN index while a B-tree on tags is useless for it.
The JSONB case is the one most people meet first, and it has a fork in it. The default jsonb_ops operator class indexes every key and every value, supporting containment (@>), key existence (?, ?&, ?|) and path operators. The jsonb_path_ops class indexes only hashes of complete paths: it supports @> alone, but produces a significantly smaller index (JSON indexing). If containment is all your queries do, the narrower class is usually the better trade.
GIN's cost is on the write side. Inserting a row means inserting one index entry per item it contains, which is why GIN maintains a pending list — new entries are appended cheaply and merged into the main structure later, controlled by fastupdate and gin_pending_list_limit (GIN tips). The list makes writes fast and makes the first read after a burst of writes slow, because the scan must also read the unmerged list.
Read: GIN indexes in PostgreSQL · Indexing JSONB · Full-text search with tsvector and GIN
GiST
GiST (Generalised Search Tree) keeps the balanced tree and throws away the ordering assumption. Each internal page holds a predicate that is true of everything below it — a bounding box, a union of ranges, a signature — and a search descends into any subtree whose predicate might match. All the type-specific behaviour lives in the operator class (GiST extensibility).
Two consequences matter in practice. GiST is lossy: the predicate can answer "maybe", so PostgreSQL rechecks candidate rows against the heap, which you see as Rows Removed by Index Recheck in EXPLAIN (ANALYZE, BUFFERS) (index types). A small recheck count is expected; a large one means the bounding predicate has stopped discriminating. And because an operator class may supply a distance function, GiST can return rows in distance order — ORDER BY location <-> point(...) LIMIT 10 answered by the index itself, which no other built-in method offers (ordering operators).
The case where nothing else will do is the exclusion constraint. "No two bookings for the same room may overlap" is not equality, so a unique index cannot express it; EXCLUDE USING gist (room WITH =, during WITH &&) can. Getting the scalar room column to participate needs the btree_gist extension, which supplies GiST operator classes for ordinary scalar types (btree_gist).
Read: What is a GiST index in PostgreSQL and when should you use it? · PostGIS spatial indexing
SP-GiST
SP-GiST is the non-balanced cousin: space-partitioned trees whose partitions do not overlap (SP-GiST) — quadtrees over points, k-d trees, radix trees over text with shared prefixes. It is a narrow method, and you usually arrive at it only after GiST has disappointed you on badly skewed data, where its bounding boxes overlap so much that a search descends repeatedly. Non-overlapping partitions mean one descent. On uniform data, GiST remains the safer default of the two.
BRIN
A BRIN index does not index rows. It stores a summary — typically a min/max pair — per range of table blocks, 128 blocks by default, and a scan reads only the block ranges whose summary could contain a match (BRIN introduction). The result is an index measured in kilobytes where the equivalent B-tree would be measured in gigabytes, and it is much cheaper to maintain on insert.
The entire method rests on one condition: physical/logical correlation. If rows arrive in roughly the order of the indexed column — an append-only events table indexed on created_at — each block range's min/max is narrow and highly selective. If the column is uncorrelated with insert order, every block range summarises nearly the full value domain, every range qualifies, and the index degrades into a sequential scan with extra steps. You can check the correlation before you build anything: pg_stats.correlation for the column reports it as a value between -1 and 1 (pg_stats).
Ranges are summarised as they are filled, so a bulk UPDATE that scatters rows degrades the summaries over time; brin_summarize_new_values() and the autosummarize storage parameter exist to deal with that (BRIN maintenance). A BRIN scan is also always lossy — it produces candidate blocks, never candidate rows — so the heap recheck is unavoidable rather than a symptom.
Read: BRIN indexes in PostgreSQL
Hash
A hash index stores a 32-bit hash code derived from the value instead of the value itself, and uses it to pick a bucket (hash indexes). Everything it cannot do follows: no ranges, no prefix matching, no ORDER BY support, no multicolumn indexes, and — the one that usually ends the discussion — no UNIQUE, because only B-tree indexes can be declared unique (CREATE INDEX).
The caveat that dominates search results is out of date. Before PostgreSQL 10, hash index operations were not written to the write-ahead log, so they were neither crash-safe nor replicated to standbys. PostgreSQL 10 added WAL logging for hash indexes (PostgreSQL 10 release notes). They are safe now; the reason to prefer a B-tree is capability, not risk.
Where a hash index can genuinely win is index size on wide keys. A B-tree leaf entry carries the full key, so a 200-character URL is stored in full in every entry, while the hash index stores 32 bits regardless of width. On a large table with a wide key and purely equality lookups, that can be a materially smaller index — which means more of it stays in cache. Build both, compare pg_relation_size, and keep the B-tree unless the hash index is comfortably smaller on your own row counts.
Read: What is a hash index in PostgreSQL and when should you use it?
Beyond the method
For most people the access method is the least consequential choice on this page. You will pick B-tree, correctly, nine times out of ten. The decisions that actually change query plans are the modifiers you apply to it.
Partial indexes. A WHERE clause on the index itself means only qualifying rows are stored (partial indexes). The classic case is a status column where the interesting value is rare: an index on WHERE status = 'pending' covering a few thousand rows out of fifty million is small, stays in cache, and costs nothing to maintain for rows that do not match. It also sidesteps the low-cardinality problem — a full index on status is mostly repeated keys, and even with B-tree deduplication an index matching half the table loses to a sequential scan. The catch is that the planner must be able to prove your query's predicate implies the index predicate, so the query has to be written to match. Read: Partial indexes in PostgreSQL
Covering indexes. INCLUDE adds payload columns to the leaf entries without making them part of the key (index-only scans). The tree stays narrow and orderable on the key columns, while a query that needs one extra column can be answered without touching the heap. Two things to know: an included column cannot be used to narrow the scan or satisfy an ORDER BY, and an index-only scan still has to prove visibility from the visibility map — which only VACUUM updates. A plan showing Index Only Scan with a large Heap Fetches: is a regular index scan wearing a better name. Read: Covering indexes in PostgreSQL
Unique indexes. A unique index is how PostgreSQL enforces a UNIQUE constraint or a primary key; the constraint form creates one automatically (unique indexes). The difference that matters is that a constraint is declared in the catalog and can be referenced by a foreign key, whereas a bare unique index cannot — but only the index form supports a WHERE clause, so a "unique among active rows" rule has to be a partial unique index rather than a constraint. Read: How do you create a unique index in PostgreSQL?
Expression indexes. Indexing lower(email) rather than email works, and is the standard answer for case-insensitive lookups (expression indexes). The constraint is exactness: the query must contain the identical expression, or the index is ignored. This is where a lot of silent index failure lives — an ORM that wraps a column in a cast or a COALESCE will not match an index built on the bare column, and the plan quietly falls back to a sequential scan.
Multicolumn ordering. Worth repeating from the B-tree section because it is the most common wasted index: equality columns first, range column last, and reordering an existing index usually beats adding a second one.
Creating and maintaining them
Writing the CREATE INDEX is the short part. The operational questions — how to build one without blocking, how to tell whether it is earning its keep, when to rebuild it, how to prove it is safe to remove — are where the time goes.
Creating one. CREATE INDEX takes a SHARE lock: reads continue, writes block until the build finishes (CREATE INDEX). Build speed is governed by maintenance_work_mem (default 64MB) and max_parallel_maintenance_workers (default 2), both of which can be raised per session for a large build. Start here if you are choosing column order, INCLUDE columns or a partial predicate for the first time. Read: How do I create an index in PostgreSQL?
Building without blocking writes. On a live table, CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead, so writes keep working. It costs two table scans plus waits for in-flight transactions, cannot run inside a transaction block, and — the part that catches people — leaves an invalid index behind if it fails: ignored by the planner, still maintained on every write. Use it for anything on a table that takes production traffic. Read: How do you create an index concurrently in PostgreSQL?
Rebuilding. REINDEX rebuilds an index from the table's current data. There are four good reasons to run it — bloat that vacuum cannot reclaim, a corrupt index, an invalid index from a failed concurrent build, and a collation version change after an OS upgrade — and REINDEX CONCURRENTLY (PostgreSQL 12 and later) is almost always the right form (REINDEX). Scheduled reindexing of everything, on a cron, is usually wasted I/O. Read: How and when should you use REINDEX in PostgreSQL?
Removing one. DROP INDEX CONCURRENTLY IF EXISTS avoids the ACCESS EXCLUSIVE lock that a plain DROP INDEX takes on the parent table (DROP INDEX). The command is trivial; proving the index is unused is not, because pg_stat_user_indexes.idx_scan is a cumulative counter on the node you are querying, and an index idle on the primary may be the one holding up reporting on a replica. Read: How do you drop an index in PostgreSQL?
Knowing what to keep. Usage against size is the single most useful index query there is, and the one that decides most arguments. Check pg_stat_get_db_stat_reset_time() before concluding anything from a scan count. Read: PostgreSQL index usage optimization
Knowing what is missing. High sequential-scan counts on a large table are the starting signal, not the conclusion — scan counters say a table is being read exhaustively, but nothing about which columns to index. Only execution-plan evidence can supply that. Read: Missing indexes and sequential scans
How MyDBA covers this
MyDBA's index advisor is cluster-aware rather than node-aware, and that distinction is the whole point of the summary above. pg_stat_user_indexes is per node: an index scanned only by reporting queries on a standby reads as zero scans on the primary, so a single-node check will tell you to drop it. MyDBA aggregates usage across the primary and every replica before an index is listed as unused, and surfaces a separate replica-only usage card for exactly the indexes that would have been the false positives. The summary counts total, unused, duplicate, invalid and bloated indexes for the whole cluster member set, not for whichever node you happened to connect to.
The same page carries missing-index opportunities, and these are deliberately incomplete. A missing-index finding is raised from scan counters, which say nothing about which columns to index — only captured execution plans can supply that. Where the plan filters on expressions rather than plain columns, MyDBA leaves the DDL blank and says so, rather than emitting a plausible-looking CREATE INDEX that would never be used. Where DDL is shown, it carries its provenance: which sampled queries the columns came from, how many samples agreed, and a caution label when only one did.
The indexes health domain runs continuously — there is no "run scan" button — and scores five checks: F0 is a staleness gate that reports the domain as informational rather than grading it when the analysis is more than three days old, F1 counts unused indexes, F2 sums the space they occupy, F3 counts duplicates, and F4 counts missing-index recommendations. Index bloat is scored separately, in the storage domain as B4, because it comes from a different source on a different cadence: a daily statistical estimate, with a bounded pgstatindex() verification substituted in for suspicious candidates where the index is small enough to check exactly.
The fleet numbers say something consistent about which of these actually bites. Across 31 monitored instances, 67.7% fail the duplicate-index check (F3_duplicate_indexes), 58.1% have at least one index with zero recorded scans (F1_unused_indexes), and 54.8% are flagged for the space those unused indexes occupy (F2_unused_index_size) — while only 3.2% fail the missing-index check (F4_missing_indexes). Across 50 instances, 44.0% carry at least one index whose estimated bloat is past the warning threshold (B4_index_bloat). And in the schema domain, 67.9% of 28 instances have at least one foreign key with no supporting index (G2_missing_fk_index). Read together, they suggest the typical instance's index problem is not a missing index or a wrong access method. It is accumulated indexes that were added on a hypothesis and never checked again.
FAQ
How many indexes is too many?
There is no count. The useful test is per index, not per table: does it have scans, and is it larger than the value of those scans? An index that has never been scanned since the last statistics reset is pure cost — write amplification on every INSERT and UPDATE, space, and longer vacuum and backup times. Across 31 monitored instances, 58.1% carry at least one index with zero recorded scans, which is the number to worry about rather than any threshold on index count.
Does an index slow down writes?
Yes, and by more than most people expect. Every index on a table must be updated when a row is inserted or deleted, and when an update cannot be applied as a HOT update — which it cannot whenever an indexed column changes, or when the page has no free space (HOT updates). GIN is the heaviest case, because one row can produce many index entries; that is what the pending list exists to smooth over. This is the real argument against speculative indexes: they cost on every write, and only pay on the reads that actually use them.
Why is my index not being used?
The usual causes, in rough order of frequency: the query's expression does not match the index's expression exactly (a cast or a function wrapper around the column defeats an index on the bare column); the predicate does not constrain a leading prefix of a multicolumn index; the planner estimates the index scan as more expensive than a sequential scan because the condition matches too much of the table; statistics are stale, so run ANALYZE; or a collation mismatch means a prefix LIKE cannot use the index without a text_pattern_ops class. EXPLAIN (ANALYZE, BUFFERS) distinguishes these (using EXPLAIN) — a sequential scan that the planner chose is a different problem from an index it could not use.
Should I index every foreign key?
Usually, but not reflexively. PostgreSQL creates an index for the referenced (parent) side automatically as part of the unique constraint; it creates nothing on the referencing (child) side (foreign keys). Without one, a DELETE or key UPDATE on the parent has to scan the child table to enforce the constraint, and a join on the FK column has no index to use. The exception is a child table small enough that a sequential scan is cheap, or an FK column you never join on and never delete parents for. Across 28 monitored instances, 67.9% have at least one foreign key with no supporting index, so this is a common gap rather than a theoretical one.
What is the default index type in PostgreSQL?
B-tree. CREATE INDEX idx ON t (col); with no USING clause builds a B-tree (CREATE INDEX), and for scalar columns it is the right answer often enough that the other five methods are best thought of as exceptions you are pushed into by an operator or a data type rather than options you choose between. If you want the reasoning for each of those exceptions in one place, this guide covers them: PostgreSQL index types.
Run the free PostgreSQL health check against your own instance to see which of these apply to you.
Last verified against PostgreSQL 18, 2026-09-17.