Postgres’s B-tree index is so good, it’s often the only one you’ll ever need, making the others feel like niche tools for specific, often overlooked, problems.
Let’s see how these indexes actually work with some data. Imagine we have a table documents with two columns: id (an integer) and tags (an array of text).
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
tags TEXT[]
);
INSERT INTO documents (content, tags) VALUES
('This is the first document.', ARRAY['post', 'tech', 'guide']),
('Another document with tech.', ARRAY['tech', 'news']),
('A guide for beginners.', ARRAY['guide', 'beginner']),
('Tech news update.', ARRAY['tech', 'news', 'update']),
('PostgreSQL tips and tricks.', ARRAY['post', 'guide', 'tips']);
A common query might be to find all documents tagged with 'tech':
SELECT id, content FROM documents WHERE 'tech' = ANY(tags);
If we only had a B-tree index on id (the primary key), this query would scan the entire documents table. This is what a Seq Scan looks like in EXPLAIN ANALYZE:
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on documents (cost=0.00..45.50 rows=5 width=39) (actual time=0.015..0.250 rows=5 loops=1)
Filter: ('tech' = ANY(tags))
Rows Removed by Filter: 0
Planning Time: 0.080 ms
Execution Time: 0.270 ms
Now, let’s add a B-tree index on the tags array. B-trees are great for equality and range queries.
CREATE INDEX idx_documents_tags_btree ON documents USING btree (tags);
Running the same query again with EXPLAIN ANALYZE:
QUERY PLAN
----------------------------------------------------------------------------------------------------
Index Scan using idx_documents_tags_btree on documents (cost=0.29..23.54 rows=5 width=39) (actual time=0.030..0.100 rows=5 loops=1)
Index Cond: ('tech' = ANY(tags))
Planning Time: 0.150 ms
Execution Time: 0.120 ms
See how it switched to an Index Scan? The B-tree can efficiently find entries where 'tech' is present in the tags array. This is because a B-tree organizes data in a sorted, hierarchical structure, allowing it to quickly narrow down the search space.
But what if we want to search for multiple tags, or use more complex pattern matching within the array? For instance, finding documents that contain either 'post' or 'guide':
SELECT id, content FROM documents WHERE 'post' = ANY(tags) OR 'guide' = ANY(tags);
With just the B-tree index on tags, Postgres might have to perform two separate index scans and combine the results, or it might still opt for a sequential scan if the conditions are complex enough.
This is where GIN (Generalized Inverted Index) shines. GIN is designed for indexing composite values like arrays, JSONB, or full-text search documents, where an element can appear multiple times and we want to efficiently query for the presence of specific elements.
Let’s create a GIN index on our tags column:
DROP INDEX idx_documents_tags_btree; -- Remove the B-tree index for comparison
CREATE INDEX idx_documents_tags_gin ON documents USING gin (tags);
Now, let’s re-run our query for documents tagged with 'tech':
SELECT id, content FROM documents WHERE 'tech' = ANY(tags);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Index Scan using idx_documents_tags_gin on documents (cost=0.25..10.50 rows=5 width=39) (actual time=0.025..0.070 rows=5 loops=1)
Index Cond: ('tech' = ANY(tags))
Planning Time: 0.180 ms
Execution Time: 0.090 ms
The GIN index is also efficient for single-element checks. However, its real power comes when querying for multiple elements or using operators like @> (contains) or <@ (contained by).
Let’s query for documents that have both 'tech' and 'news' tags:
SELECT id, content FROM documents WHERE tags @> ARRAY['tech', 'news'];
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=12.50..21.50 rows=2 width=39) (actual time=0.040..0.060 rows=2 loops=1)
Recheck Cond: (tags @> ARRAY['tech', 'news']::text[])
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_documents_tags_gin (cost=0.00..12.50 rows=2 width=0) (actual time=0.025..0.025 rows=2 loops=1)
Index Cond: (tags @> ARRAY['tech', 'news']::text[])
Planning Time: 0.200 ms
Execution Time: 0.080 ms
A GIN index works by creating an index entry for each distinct value within the indexed data type (like each tag in our array). It then maps these values back to the rows that contain them. For an array, it’s like building a lookup table where each tag points to all the document IDs that have that tag. When you query for tags @> ARRAY['tech', 'news'], Postgres can efficiently find the entries for 'tech' and 'news' in the GIN index and then find the rows common to both.
GiST (Generalized Search Tree) is another powerful index type, often used for geometric data, full-text search, and range data. It’s a more general-purpose index than B-tree and can handle complex data types and queries. For array types, GiST can be used similarly to GIN for containment queries, but its internal structure and performance characteristics differ. GiST is often more space-efficient than GIN and can be faster for certain types of updates.
Let’s create a GiST index on tags:
DROP INDEX idx_documents_tags_gin;
CREATE INDEX idx_documents_tags_gist ON documents USING gist (tags);
Querying for documents with 'tech':
SELECT id, content FROM documents WHERE 'tech' = ANY(tags);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on documents (cost=0.00..45.50 rows=5 width=39) (actual time=0.015..0.250 rows=5 loops=1)
Filter: ('tech' = ANY(tags))
Rows Removed by Filter: 0
Planning Time: 0.090 ms
Execution Time: 0.270 ms
Notice how the GiST index on tags didn’t automatically speed up the ANY() operator for single-element checks. GiST indexes are often better suited for operators like @> (contains) when dealing with arrays.
Let’s try the containment query with GiST:
SELECT id, content FROM documents WHERE tags @> ARRAY['tech', 'news'];
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=12.50..21.50 rows=2 width=39) (actual time=0.040..0.060 rows=2 loops=1)
Recheck Cond: (tags @> ARRAY['tech', 'news']::text[])
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_documents_tags_gist (cost=0.00..12.50 rows=2 width=0) (actual time=0.025..0.025 rows=2 loops=1)
Index Cond: (tags @> ARRAY['tech', 'news']::text[])
Planning Time: 0.210 ms
Execution Time: 0.080 ms
This shows that GiST can be very effective for containment queries on arrays. The choice between GIN and GiST often comes down to the specific query patterns and the update frequency of the data. GIN is generally better for exact matches and multiple element checks, while GiST can be more flexible and efficient for range-like operations and certain complex types.
Hash indexes are the simplest type. They only support equality (=) comparisons. They work by hashing the indexed value and storing the hash. When you query, Postgres hashes your search value and looks up the matching hash in the index. They can be very fast for equality lookups but offer no support for range queries or other operators. Hash indexes are less common in modern Postgres usage compared to B-trees and GIN/GiST because B-trees are often just as fast for equality and also support range queries.
Let’s create a Hash index on id:
CREATE INDEX idx_documents_id_hash ON documents USING hash (id);
A query for a specific ID:
SELECT content FROM documents WHERE id = 3;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Scan using idx_documents_id_hash on documents (cost=0.00..4.01 rows=1 width=39) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (id = 3)
Planning Time: 0.050 ms
Execution Time: 0.030 ms
This is very fast. However, if you tried WHERE id > 3, a hash index would be useless.
BRIN (Block Range Index) indexes are designed for very large tables where data is physically correlated with the index key. Instead of indexing every single row, BRIN indexes store summary information about ranges of table blocks. If your table is ordered by id, a BRIN index on id would store the minimum and maximum id for each block range. This makes BRIN indexes very small and fast to build, but they are only effective if the data is physically ordered.
Let’s try to create a BRIN index on id, assuming id values are naturally increasing as rows are inserted.
CREATE INDEX idx_documents_id_brin ON documents USING brin (id);
Querying for a specific ID:
SELECT content FROM documents WHERE id = 3;
QUERY PLAN
--------------------------------------------------------------------------------------
Index Scan using idx_documents_id_brin on documents (cost=0.00..4.01 rows=1 width=39) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (id = 3)
Planning Time: 0.060 ms
Execution Time: 0.030 ms
For a single equality lookup on a small table, BRIN might perform similarly to other indexes. However, its advantage appears on much larger tables where it can quickly skip large blocks of data that do not contain the desired range of values. If the id values were not correlated with physical storage, the BRIN index would be ineffective, and Postgres would likely fall back to a sequential scan.
The most surprising aspect of Postgres indexing is how its query planner intelligently chooses the best index (or no index) based on the query, the available indexes, and statistics about the data. It’s not just about picking the "right" index type; it’s about Postgres knowing when and how to use it, and sometimes, realizing that a full table scan is actually the most efficient path.
The next step in mastering Postgres indexing is understanding how to tune these indexes, especially GIN and GiST, with different operator classes and how to effectively use partial and expression indexes.