PostgreSQL: Query Optimization and Understanding Index Operations

PostgreSQL Index Optimization: Why Your Indexes Are Ignored and How to Fix It

Creating an index doesn't always guarantee PostgreSQL will use it. Developers often encounter scenarios where, despite an index being present, queries run slowly, resorting to a full table scan (Seq Scan). Understanding PostgreSQL's query planner mechanisms, interpreting EXPLAIN ANALYZE metrics, and knowing the factors influencing execution strategy choices are crucial for database performance optimization. In this article, we'll delve into these aspects in detail, using real-world examples with large datasets.

Preparation: 4 Million Rows for Experiments

To demonstrate how indexes work, we'll create a test table named t_test without a primary key or any indexes, containing 4 million records. This will clearly illustrate the performance difference before and after indexing.

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

When executing a query without an index, for instance, EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;, we'll observe a Seq Scan that iterates through all 4 million rows, taking approximately 126 milliseconds. This is a classic example of the problem indexes are designed to solve.

Understanding EXPLAIN and the cost Metric

EXPLAIN ANALYZE is the primary tool for analyzing query execution plans in PostgreSQL. It provides detailed information on how the planner intends to execute a query, including chosen operators (e.g., Seq Scan, Index Scan), execution time, and, crucially, the cost metric.

Let's examine the cost=0.00..71622.00 output from the previous example. This number isn't actual time in milliseconds but a relative coefficient PostgreSQL uses to compare different execution plans. Think of it as "parrots" – abstract units of cost. For a cleaner experiment, it's worth disabling parallelism:

SET max_parallel_workers_per_gather TO 0;

The cost is derived from several components, such as the number of disk blocks and the cost of processing each row:

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 8KB blocks
SHOW cpu_tuple_cost;    -- 0.01 (cost of processing a row)
SHOW cpu_operator_cost; -- 0.0025 (cost of an operator/function)

The approximate cost formula for our Seq Scan would look like this:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

The result will be close to 71622. It's crucial to understand that cost doesn't account for system hardware specifics, so comparing the cost of two different queries to estimate actual execution time is inaccurate. However, within a single query, it's useful for identifying the most "expensive" parts of the plan.

Basic Index Usage: BTree and Its Advantages

The most common index type in PostgreSQL is BTree. It provides efficient searching, sorting, and high concurrency. Let's create a BTree index on the id column:

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

After creating the index, the query's cost significantly decreases (from 71,622 to 8.45), and retrieval time drops to fractions of a millisecond. BTree indexes are also effective for sorting operations and finding minimum/maximum values:

• Sorting: PostgreSQL can use an index to perform ORDER BY by simply traversing the index in the desired direction (Index Scan Backward for DESC) and stopping once the LIMIT is reached.

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

• Min/Max: To determine min(id) or max(id), the planner uses an Index Only Scan, reading the first or last entry from the index.

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

Handling Multiple Conditions: Bitmap Scan

PostgreSQL can efficiently handle queries with multiple OR conditions on a single index using a Bitmap Scan.

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

In this scenario, PostgreSQL performs a Bitmap Index Scan for each condition, then combines the results into a bitmap using BitmapOr, and only then accesses the main table (Bitmap Heap Scan) to retrieve the full rows. This avoids multiple table scans and optimizes data access.

Why the Planner Ignores an Index: Selectivity and Statistics

One of the most common reasons an index isn't used is the low selectivity of the query condition. Selectivity is the proportion of rows in a table that match a given condition. If a condition affects a large portion of the table, the planner might decide that a Seq Scan will be more efficient than an Index Scan.

Let's consider an example. We'll create an index on the name field:

CREATE INDEX idx_name ON t_test (name);

If we search for a non-existent name (EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';), the index will be used, and rows will be 1, as PostgreSQL always expects at least one row. However, if the query covers a large portion of the table, for example, 'hans' OR 'paul' (which constitutes 100% of our test table):

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

In this case, PostgreSQL will perform a Seq Scan. The reason is simple: scanning the entire index and then accessing the table for each of the 4 million rows is more expensive than simply reading the entire table sequentially. The planner makes its decision based on data distribution statistics. If these statistics are outdated (e.g., after a large volume of changes), the decision might be suboptimal.

Impact of Physical Data Layout: Correlation and CLUSTER

The effectiveness of an index heavily depends on the physical arrangement of data on disk. If the data accessed by the index is widely scattered, it can significantly slow down retrieval. Let's create a copy of our table, but with a randomized row order:

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Let's compare the performance of a query retrieving the first 10,000 records for the original (ordered) and randomized tables:

• Original table (ordered data):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

Here, we'll observe a low number of buffer accesses (e.g., Buffers: shared hit=3 read=82), indicating sequential data reading.

• Randomized table (scattered data):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

In this case, the number of buffer accesses will be significantly higher (e.g., Buffers: shared hit=801 read=7210). This occurs because the data is scattered, forcing PostgreSQL to perform many random disk reads, which substantially increases execution time. The planner might even switch to a Bitmap Heap Scan.

Correlation

PostgreSQL tracks the degree of data orderliness using the correlation metric, available in pg_stats:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

• correlation ~ 1: Data is physically ordered, allowing sequential reading of disk blocks.
• correlation ~ 0: Data is randomly scattered, leading to many individual disk accesses for each row.

CLUSTER

The CLUSTER command allows you to physically sort data in a table according to a specified index:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

After CLUSTER, retrieval will become fast again. However, CLUSTER has significant drawbacks:

• Table Locking: The operation locks the entire table, including SELECT statements, for its duration.
• Limitation: It only works with a single index.
• No Automatic Maintenance: The data order is not automatically maintained; after new insertions or updates, the data can become unordered again.

Optimization via Index Only Scan and INCLUDE

When a query only accesses columns that are entirely contained within an index, PostgreSQL can perform an Index Only Scan. This avoids accessing the main table (heap), significantly speeding up query execution.

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Here, id is already in the idx_id index, so an Index Only Scan is possible. However, if you query for all columns, including name, which isn't in idx_id:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL will perform a regular Index Scan, as it will need to access the table for the name column. To enable an Index Only Scan even for SELECT *, you can use a covering index with the INCLUDE clause:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Now, name is included in the index, and an Index Only Scan is performed again, minimizing disk accesses.

Advanced Indexing Techniques: Functional and Partial Indexes

Beyond standard BTree indexes, PostgreSQL offers more specialized solutions:

• Functional Indexes: These allow you to index the results of functions. The only requirement is that the function must be deterministic (always returning the same result for the same input).

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

A typical use case is indexing lower(email) for case-insensitive searches.

• Partial Indexes: These indexes cover only a subset of table rows that satisfy a specific WHERE condition.

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

Such an index will be significantly smaller and updated less frequently, which is beneficial when the majority of the data is rarely involved in search queries.

Other Index Types: GiST and pg_trgm

PostgreSQL supports various index types, each optimized for specific tasks. Besides BTree, there are GIN, GiST, SP-GiST, BRIN, and Bloom. For instance, GiST indexes are often used for geospatial data, full-text search, and other complex data types.

The pg_trgm extension enables fuzzy string matching by breaking strings into trigrams and calculating the distance between them. This is useful for typo-tolerant searches or partial string matches.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- a number between 0 and 1
SELECT show_trgm('abcdef');

Key Takeaways

• EXPLAIN ANALYZE is your best friend: Use it to understand query plans and identify bottlenecks. cost is a relative metric, useful for comparing parts of a single plan.
• Selectivity drives choice: Indexes are effective for highly selective queries (few rows). With low selectivity (many rows), PostgreSQL might prefer a Seq Scan.
• Physical data layout matters: High correlation between logical and physical data order improves Index Scan performance. CLUSTER can help but has significant drawbacks.
• Index Only Scan and INCLUDE: Use these mechanisms to minimize access to the main table when all necessary columns are already present in the index.
• Advanced Indexes: Functional and partial indexes allow you to create more specialized and efficient structures for specific scenarios.

— Editorial Team