Скачать или смотреть Understanding Index Usage with PostgreSQL: The Impact of ORDER BY Clauses

Discover why PostgreSQL may not use an index when multiple ORDER BY clauses are present, and learn how to leverage PostgreSQL 13 improvements for better query performance.
---
This video is based on the question https://stackoverflow.com/q/67690904/ asked by the user 'ekzyis' ( https://stackoverflow.com/u/13555687/ ) and on the answer https://stackoverflow.com/a/67691785/ 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: Why is postgres no longer using the index if I use another ORDER BY?

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.
---
Understanding Index Usage with PostgreSQL: The Impact of ORDER BY Clauses

Introduction

PostgreSQL is a powerful relational database system, but it can sometimes leave users puzzled when it behaves unexpectedly. A common question users encounter involves the usage of indexes in combination with the ORDER BY clause. In this guide, we will explore the scenario presented by a user: "Why is PostgreSQL no longer using the index if I use another ORDER BY?" We will examine the behavior of PostgreSQL when handling indexes and multiple ORDER BY clauses and provide insights into how to remedy this issue.

The Problem

Consider a user with a large table named report containing approximately 18 million rows. The user noticed that when employing the ORDER BY created DESC LIMIT 25 clause, PostgreSQL utilized an index, resulting in a fast query execution time. However, when the user compounded the ORDER BY clause with an additional id ordering (ORDER BY created DESC, id ASC LIMIT 25), PostgreSQL reverted to a slower parallel sequential scan instead of using the index.

Key Observations

Index Usage: The user expected PostgreSQL to use an index even with an additional ordering condition.

When Problem Occurs: The issue arises when combining two ORDER BY conditions along with a limit on results.

Diving Deeper: Why Does This Happen?

To understand why PostgreSQL does not use an index in this scenario, it's essential to recognize the database's behavior concerning sorting and result ordering:

Multiple ORDER BY Clauses: When two columns are used in the ORDER BY clause, PostgreSQL evaluates how to effectively return results while maintaining that specific order.

Potential Results Variation: Introducing additional sorting criteria, such as id ASC, introduces the possibility of returning different sets of results, especially within the limit specified. This is because the same grouping of created timestamps can yield different id values.

Optimization Decisions: PostgreSQL must ensure data consistency when returning results, especially when limits are involved. This is another reason it may opt for parallel sequential scans rather than potentially incomplete index scans followed by additional sorting.

Example Queries and their Impact

To showcase the difference in execution plans, here are simplified representation examples:

When using ORDER BY created DESC LIMIT 25:

PostgreSQL efficiently leverages the index idx_report_created_desc leading to a quick return of the top 25 results.

When using ORDER BY created DESC, id ASC LIMIT 25:

The execution plan opts for a Parallel Seq Scan, indicating that the complexity of sorting by both fields makes index usage less favorable due to the potential for unmatched results.

Suggested Solution: Upgrade and Optimization

The key takeaway here is that PostgreSQL is not infallible—it has its limitations, especially concerning complex queries. However, upgrades can significantly improve its capabilities:

Upgrade to PostgreSQL 13: The newer version comes with enhancements, such as the incremental sort feature. This means that PostgreSQL can perform a more efficient sort operation when ORDER BY conditions are present while also utilizing index scanning effectively.

Performance Boost: The incremental sort works by rapidly narrowing down results and breaking ties in sorting based on conditions such as created effectively, leading to better query execution performance.

Conclusion

Understanding why PostgreSQL may not choose to use an index with a multi-part ORDER BY clause is crucial for optimizing your SQL queries. By keeping in mind the behavior of the database in regard to index scanning and leveraging recent innovations in