A Simple Guide to PostgreSQL Index Types

You ran EXPLAIN ANALYZE and found a slow query. The plan shows a Sequential Scan on a huge table. You know the answer is an index. So you add one.

-- This seems right...
CREATE INDEX ON users (id);

But sometimes this doesn’t work. Or you are trying to do something more complex like search inside a JSON document. The default index type is not always the right tool. PostgreSQL has several index types and knowing the basics can save you hours of debugging.

The default index is a B-Tree. It’s what you get if you don’t specify a type. Let’s start there.

The Default: B-Tree

B-Tree stands for Balanced Tree. You can imagine it like a sorted tree structure that lets the database quickly find a specific value without scanning the whole table. It is good at finding a single value or a range of values.

This makes B-Tree indexes perfect for most of your needs. They excel at queries using comparison operators.

• = (equality)
• > or < (range)
• >= or <= (range)
• BETWEEN

If you have a users table and you frequently look up users by their email or find users who signed up in a certain date range your WHERE clauses will look like this.

-- Find a specific user
SELECT * FROM users WHERE email = 'test@example.com';

-- Find users who signed up last month
SELECT * FROM users WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01';

For these queries a standard B-Tree index is exactly what you need.

-- Create an index on the email column
CREATE INDEX idx_users_on_email ON users (email);

-- Create an index on the created_at column
CREATE INDEX idx_users_on_created_at ON users (created_at);

This covers perhaps 90% of use cases. So when are B-Trees not enough?

Searching Inside Documents with GIN

Imagine you store user preferences in a JSONB column called settings. Your data might look like this.

{“theme”: “dark”, “notifications”: [“email”, “push”]}

Now you want to find all users who have push notifications enabled. A B-Tree index on the settings column won’t help you here. The database would still have to scan every row load the JSON document and check if it contains the right value.

This is where a GIN index is useful. GIN stands for Generalized Inverted Index. It’s designed to handle “composite” values where a single column contains multiple values to be searched. Think of the index in the back of a book. It lists keywords and then points to all the pages where each keyword appears. GIN works the same way for elements inside a JSONB document or words in a full-text search vector.

To find all users with push notifications you might run this query.

-- Find users with push notifications
SELECT * FROM users WHERE settings @> '{“notifications”: [“push”]}';

Without the right index this query is very slow on a large table. With a GIN index it’s fast.

-- Create a GIN index on the settings column
CREATE INDEX idx_users_on_settings_gin ON users USING GIN (settings);

Now PostgreSQL can use this index to jump directly to the rows that match your criteria without checking every single one. GIN is your tool for searching inside JSONB arrays or full-text search documents.

Geospatial Queries with GiST

What if your application deals with locations? Maybe you need to find all cafes within one kilometer of a user’s current position. This is another case where a B-Tree index on latitude and longitude columns won’t work well. It can handle ranges on one dimension at a time but not both at once.

For this kind of two-dimensional or multi-dimensional data you need a GiST index. GiST stands for Generalized Search Tree. It’s designed to handle complex data types like geometric shapes and is the foundation for PostGIS the popular geospatial extension for PostgreSQL.

A GiST index can answer questions like “which objects are closest to this point?” or “which polygons overlap with this polygon?”.

Let’s say you have a locations table with a coordinates column of type point. To find all locations within a certain bounding box you would need an index like this.

-- This assumes you are using PostGIS or a similar extension
CREATE INDEX idx_locations_on_coordinates_gist ON locations USING GIST (coordinates);

This allows the database to efficiently narrow down the search space. If your work involves maps or any kind of spatial data GiST is the index you’ll need.

For Huge Ordered Tables: BRIN

There’s one more index type worth knowing about for special cases. BRIN stands for Block Range Index.

BRIN indexes are different. They don’t store a pointer to every single row. Instead they store the minimum and maximum value for a large block of rows. This makes them very small and cheap to maintain.

They only work well when the data in a column is naturally correlated with its physical order in the table. The classic example is a created_at timestamp on a very large logging table. As new rows are inserted the created_at value naturally increases.

If you query for logs from a specific day PostgreSQL can look at the BRIN index and know it only needs to scan a small range of table blocks instead of the whole table.

-- For a massive, ordered events table
CREATE INDEX idx_events_on_created_at_brin ON events USING BRIN (created_at);

A B-Tree index on this column would also work but it would be much larger. If you have a truly enormous table and your data has this natural ordering a BRIN index can be a great way to get good performance without the storage overhead.

How to Choose

It seems complex but the choice is usually simple.

For most queries on standard data types like text numbers or dates use a B-Tree. This is the default and usually the right answer.

If you need to search for values inside a JSONB document or an array use a GIN index.

If you are working with geospatial data or need to find the “nearest” things use a GiST index.

If you have a huge table with a column that is naturally ordered like a timestamp consider a BRIN index to save space.

Understanding these tools means you are no longer just guessing when a query is slow. You can look at the query and the data and make an intelligent choice. Now think about the slowest query you've had to fix. What was the problem?

— Rishi Banerjee
September 2025