Practical PostgreSQL Indexing and Query Optimization

A production alert fires: an endpoint's P99 latency has surged to 8 seconds. You open the slow-query log and find a SELECT taking several seconds on a table with millions of rows. Add an index, and 8 seconds becomes 30 milliseconds. This story repeats every day.

PostgreSQL has a powerful query optimizer, but it can only choose among the indexes that already exist. If the right index has not been created, even the smartest optimizer has no choice but to scan the table. This article focuses on one core question: how do you make indexes actually work for you?

1. Why indexes are the biggest lever for database performance

Here is a counterintuitive fact: when a database is slow, nine times out of ten the problem is not insufficient CPU, memory, or disk capacity. It is that an index is not being used.

Data is stored on disk in pages, also called blocks, which are 8 KB by default. A full-table scan, or Sequential Scan, means PostgreSQL must read every page in the table. For a table with ten million rows, that may mean tens of gigabytes of disk I/O. An Index Scan or Bitmap Index Scan usually needs to read only a few dozen pages.

The difference is not merely "a little faster." It is an order-of-magnitude difference.

Seq Scan on orders    rows=10,000,000   actual time=8234.521..8234.521
Index Scan on orders  rows=42           actual time=0.038..0.041

More importantly, indexes are one of the few ways to trade almost no cost for a hundredfold performance gain. A hardware upgrade provides a roughly linear improvement at best, while turning a Seq Scan into an Index Scan often produces a gain of 100x or more.

Therefore, whenever you encounter a slow query, your first reaction should never be "add more servers." Open EXPLAIN ANALYZE first and see whether the query is brute-force scanning the table.

2. Choosing an index type: not every index is a B-tree

PostgreSQL indexes are not a single mechanism. There are six types, each designed for a different query pattern. Choosing the wrong type is equivalent to having no index at all.

1. B-tree (default)

If you do not specify a type when creating an index, PostgreSQL uses B-tree. It is suitable for equality lookups, range queries (>, <, BETWEEN), and sorting (ORDER BY). This is the correct choice for 95% of use cases.

CREATE INDEX idx_orders_created_at ON orders(created_at);
-- 等值、范围、ORDER BY created_at 都能用上

2. Hash

Hash indexes support only equality matching (=), not ranges or sorting. In theory, they are slightly faster and smaller than B-trees, but the benefit is minimal. Before PG 10, Hash indexes were not written to WAL and were unsafe after a crash, so historically almost nobody used them. Practical recommendation: skip Hash and use B-tree.

3. GIN (inverted index)

GIN handles cases where "one field contains multiple values": **arrays, full-text search, jsonb, and tsvector**. For example, suppose you have a tags text[]field and want to find all articles containing the'python'` tag:

CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags @> ARRAY['python'];

Without GIN, this query can only perform a full-table scan. With it, the query returns in milliseconds. The same applies to jsonb fields: GIN (data jsonb_path_ops) is the standard PostgreSQL approach to indexing JSON.

4. GiST

GiST is suitable for geometric data such as PostGIS, range types such as tsrange and int4range, and queries involving semantics such as intersection or containment. For example, in a meeting-room booking system, you may need to check whether a time range conflicts with an existing booking:

CREATE INDEX idx_booking_range ON bookings USING GIST(during);
SELECT * FROM bookings WHERE during && '[2026-07-17 14:00, 2026-07-17 15:00)';

5. BRIN (block range index)

BRIN is designed specifically for very large tables where physical row order is highly correlated with the indexed column. Consider a log table containing hundreds of millions of rows inserted in created_at order. BRIN records only the range covered by each data block, making it several orders of magnitude smaller than a B-tree and highly efficient for queries that scan a time range.

CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at);

Rule of thumb: use B-tree for equality and range queries, GIN for arrays, full-text search, and jsonb, GiST for geometry and ranges, and BRIN for extremely large time-series tables. Generally avoid Hash.

3. Composite indexes and the leftmost-prefix rule

Single-column indexes are easy to understand. Composite indexes are where problems begin. The most common beginner mistake is assuming that index column order does not matter. It does, critically.

PostgreSQL composite indexes follow the leftmost-prefix rule: an index on (a, b, c) can serve WHERE a = ?, WHERE a = ? AND b = ?, and WHERE a = ? AND b = ? AND c = ?, but it cannot directly serve WHERE b = ? or WHERE c = ?. Without the leading column, PostgreSQL cannot navigate the index tree.

CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 能用上索引:
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 42;

-- 用不上(或只能用到部分):
SELECT * FROM orders WHERE status = 'paid';           -- 没有最左前缀
SELECT * FROM orders WHERE user_id = 42 AND created_at > NOW();  -- 只用到 user_id

Practical ordering rules:

1. Put columns used in equality conditions (=) first.
2. Put columns used in range conditions (>, BETWEEN) last. After a range condition, subsequent index columns generally cannot narrow the range further.
3. Prioritize columns with high selectivity, although this is secondary to the equality-versus-range rule.
4. If a column is also used in ORDER BY, weigh the tradeoff carefully: arrange sorting and filtering columns so the database can use an Index Scan directly and avoid an extra Sort.

A counterexample: many people habitually write CREATE INDEX (created_at, user_id) because created_at appears highly selective. But if most queries use WHERE user_id = ? ORDER BY created_at DESC, the correct index is (user_id, created_at), not the reverse. Indexes exist to serve queries, not to look intuitively reasonable.

4. Use EXPLAIN ANALYZE to see the truth

EXPLAIN displays an estimated execution plan, while EXPLAIN ANALYZE actually runs the SQL and reports real measurements. Always use EXPLAIN (ANALYZE, BUFFERS) to inspect actual data.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

The output looks roughly like this:

Index Scan using idx_user_status_created on orders  (cost=0.42..28.71 rows=8 width=240) (actual time=0.038..0.041 rows=7 loops=1)
  Index Cond: ((user_id = 42) AND (status = 'paid'::text))
  Buffers: shared hit=5
Planning Time: 0.245 ms
Execution Time: 0.062 ms

Focus on four things in the execution plan:

1. Node type: Seq Scan is a full-table scan and a warning sign. Index Scan uses an index directly. Bitmap Index Scan + Bitmap Heap Scan first retrieves a batch of row pointers from an index and then fetches the rows from the heap, which suits queries matching more rows. Index Only Scan uses a covering index and returns data directly from the index without touching the heap, making it the fastest option.

2. rows (estimated vs. actual): rows=8 is the optimizer's estimate, while (actual ... rows=7) is the actual count. A large discrepancy indicates stale statistics. Running ANALYZE orders; will usually fix it.

3. actual time: the value before .. is the time to the first row, and the value after it is the time to the last row. Look at Execution Time, not Planning Time.

4. Buffers: shared hit means the page came from cache, while shared read means it came from disk. hit=5 shows that only a few pages were read, which is characteristic of an index scan. If you see shared read=125000, the query is almost certainly grinding through the disk with a brute-force scan.

A common misconception is that Seq Scan is always bad. For a small table, a full-table scan is faster than using an index because an index adds an extra random I/O operation. PostgreSQL deliberately chooses Seq Scan when a table is below the seq_page_cost threshold. Do not panic when you see a Seq Scan on a small table.

5. Why an index is not being used: four common causes

You created an index, but the execution plan still shows Seq Scan. In 90% of cases, one of the following four issues is responsible.

1. A function wraps the column

-- 索引在 email 上
CREATE INDEX idx_users_email ON users(email);

-- 用不上!
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

Applying any function to a column makes a normal index unusable because the B-tree stores the original value while the query searches for a transformed value.

The solution is an expression index: create an index directly on LOWER(email) as shown in Section 7, or rewrite the condition into a form such as WHERE email = '[email protected]' that can match the original value.

2. Implicit type conversion

-- user_id 是 bigint,但代码传了字符串
SELECT * FROM orders WHERE user_id = '42';

PostgreSQL is usually more forgiving than MySQL, but cross-type comparisons can still prevent index use, especially when comparing a varchar column with an integer. Rule: the parameter type must exactly match the column type. Parameterized queries, or Prepared Statements, eliminate this class of problem at its source and should be used instead of string concatenation.

3. LIKE with a leading wildcard

-- 用不上 B-tree 索引
SELECT * FROM users WHERE name LIKE '%smith';
SELECT * FROM users WHERE name ILIKE '%smith';

A B-tree index is ordered lexicographically. A leading wildcard means "any prefix," so the index cannot use binary search.

• A trailing-wildcard match such as LIKE 'smith%' can use an index, with support from the default text_pattern_ops operator class.
• If you genuinely need prefix or infix fuzzy search, use the pg_trgm extension with a GIN index, or use full-text search directly with tsvector and GIN.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);
SELECT * FROM users WHERE name % 'smith';  -- 模糊匹配,走索引

4. Both sides of an OR condition are not indexed

SELECT * FROM users WHERE email = '[email protected]' OR phone = '13800000000';

For this query to use BitmapOr, both email and phone must have indexes. If either side is unindexed, the optimizer can only fall back to a Seq Scan. When a query has many OR conditions, consider rewriting it with UNION ALL; the semantics are clearer and optimization is easier.

6. When not to add an index: indexes have a cost

Adding an index may look harmless, but every INSERT / UPDATE / DELETE must update all relevant indexes. More indexes mean slower writes, larger tables, slower VACUUM operations, and greater memory pressure.

Do not add indexes in the following situations:

1. Write-heavy, read-light tables. Log and event-tracking tables are examples where writes dominate the workload. Adding five indexes may make inserts three times slower.
2. Small tables. For dictionary or configuration tables with only a few thousand rows, Seq Scan is faster than using an index because the index adds random I/O. An index is simply wasteful.
3. Low-selectivity columns. Examples include gender, with only "male/female/unknown," or is_deleted, with only true/false. The optimizer will probably ignore such an index because it still has to scan a large volume of matching data.
4. Columns that are almost never queried. Indexes pay for hot queries; do not index cold fields.

Decision rule: before adding an index, ask three questions: Is this query frequent? Is this column highly selective? Is this table read-heavy or write-heavy? Add the index only when all three answers support it.

Regular cleanup is also necessary. The pg_stat_user_indexes view reports how each index is actually used:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- 从来没被用过的索引

An index with idx_scan = 0 is pure liability. DROP it directly.

7. Advanced techniques: partial, expression, and covering indexes

Once you understand the previous six sections, these three advanced techniques can take your indexing from merely functional to finely tuned.

1. Partial Index

A partial index covers only rows that satisfy a condition. It is smaller, cheaper to maintain, and more precisely targeted.

-- 只给"未完成订单"建索引,完成订单直接走 Seq Scan 也无所谓
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

If 99% of orders are completed and 1% are pending, an index dedicated to frequent queries over that 1% can shrink from several GB to a few dozen MB.

2. Expression Index

An expression index directly indexes a transformation of a column, solving the function-wrapped-column problem from Section 5.

CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';  -- 现在能用上索引

Classic use cases include case-insensitive email or username lookups and aggregations by month. The query condition must exactly match the index expression, or the index is useless.

3. Covering Index (INCLUDE)

When a query needs only the indexed columns plus a few additional fields, use INCLUDE to place those fields in the index. PostgreSQL can then perform an Index Only Scan, completely avoiding heap access and improving performance further.

CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total_amount, created_at);

-- 这个查询能走 Index Only Scan,不需要回表
SELECT user_id, total_amount, created_at
FROM orders
WHERE user_id = 42;

Columns after INCLUDE do not participate in index ordering, so they help only with coverage and do nothing for WHERE or ORDER BY. This is the key difference between them and columns added to the main index key: key columns are used for navigation, while INCLUDE columns are merely carried along.

To decide whether you need a covering index, inspect EXPLAIN ANALYZE and see whether an Index Scan still performs heap accesses, which become expensive when many rows match. If it does, and those fields are consistently returned by a frequent query, use INCLUDE.

Summary

PostgreSQL index optimization is fundamentally about seeing the truth first, then applying the right remedy.

1. Your first response should always be EXPLAIN (ANALYZE, BUFFERS). Do not add indexes based on intuition; inspect the execution plan first.
2. The index type must match the query pattern. Use B-tree for equality and ranges, GIN for arrays, full-text search, and jsonb, BRIN for large time-series tables, and generally avoid Hash.
3. Composite-index column order can make or break performance. Put equality columns first and range columns last, following the leftmost-prefix rule.
4. The four main causes of unusable indexes are function-wrapped columns, type mismatches, leading-wildcard LIKE expressions, and OR conditions with only one side indexed.
5. Indexes have a cost; more is not always better. Avoid them on write-heavy, read-light tables, small tables, and low-selectivity columns. Regularly remove dead indexes with idx_scan = 0.

Key takeaway: The best indexing strategy is not to have more indexes, but to have more precise ones: make indexes match queries, and make queries deliver performance.