Скачать или смотреть Understanding the Performance Discrepancies in PostgreSQL Queries with Indexing and Sequential Scans

Explore how to optimize PostgreSQL queries effectively using indexes versus sequential scans, particularly in scenarios requiring specific row retrievals.
---
This video is based on the question https://stackoverflow.com/q/77397374/ asked by the user 'AlwaysLearning' ( https://stackoverflow.com/u/2725810/ ) and on the answer https://stackoverflow.com/a/77411687/ provided by the user 'jjanes' ( https://stackoverflow.com/u/1721239/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Using index better than sequential scan when every hundredth row is needed, but only with explicit list of values

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Optimizing PostgreSQL Queries: Indexing vs. Sequential Scans

When working with large datasets in PostgreSQL, the choice between using an index scan or a sequential scan can significantly impact query performance. This becomes even more pronounced in scenarios where specific rows are needed, such as retrieving every 100th entry from a vast table. In this guide, we’ll break down the fundamental differences between these scanning techniques, using a practical example from a PostgreSQL database.

The Problem

You have a large table consisting of 100 million rows with a B-tree index on the content_id column. Each content_id has 100 corresponding part values, while the vector column holds specific data (384-byte size) for every 100th content_id. This setup leads to a situation where you need to retrieve rows specifically for every 100th content_id, posing questions about the efficiency of using an index versus performing a sequential scan.

Key Questions

Why is sequential scanning not better than using an index scan when retrieving every 100th row?

What changes when the explicit list of values is replaced with a generate_series query?

The Solution

Background on Query Performance

In order to understand the performance implications of your queries, it’s essential to grasp two critical concepts: sequential scans and index scans.

Sequential Scan: The database reads all rows in the table, checking each one against the query criteria. This can be efficient when most of the table's rows are relevant to the query.

Index Scan: This approach utilizes an index to quickly find rows that match the query criteria, often skipping irrelevant rows and improving performance.

Examining the Queries

Let’s compare three distinct query execution plans:

The original query with an explicit list of values.

The query using SET enable_seqscan = off;.

The query using generate_series instead of an explicit list.

1. Original Query Outcome

The original query takes approximately 6 seconds to execute.

2. With Sequential Scans Disabled

When you specify SET enable_seqscan = off;, execution drops to about 3 seconds. This is likely because the optimizer is forced to use the index, allowing it to skip directly to relevant rows instead of scanning the entire dataset.

3. Using generate_series

Switching to a generate_series function introduces a significant execution time bump back to about 6 seconds. PostgreSQL fails to parallelize effectively for this method, leading to a performance drop as it reverts to a less efficient plan that resembles a sequential scan.

Key Insights from the Plans

Plans 2 vs 3: The performance difference can be attributed to how PostgreSQL handles parallelization. The index scan in Plan 2 effectively filters out unrelated tuples, significantly reducing the number of rows processed.

Plans 1 vs 2: Index scanning is favored due to the reduced time spent in I/O operations. Plan 1 processes all tuples sequentially, while Plan 2 intelligently uses indexing to limit processing only to relevant rows.

Why is the Planner Overestimating Costs?

The PostgreSQL planner may struggle with estimating the true cost of I/O operations and, in turn, may inaccurately predict the utility of various scan methods. It can overestimate the number of tuples returned from sequential scans, leading to suboptimal plan selections. Regularly analyzing your tables can help improve these estimates and the overall performance of your queries.

Conclusion

In the context of PostgreSQL, understanding the mechanics of index versus sequential scans can lead to dramatic differences in query performance, especially in large datasets. By recognizing