Скачать или смотреть Understanding PostgreSQL Partition Pruning Issues with UNNEST() in Subqueries

Discover why `PostgreSQL` fails to utilize execution-time partition pruning when using `UNNEST()` in subqueries and how to effectively solve it.
---
This video is based on the question https://stackoverflow.com/q/71322555/ asked by the user 'Tyken' ( https://stackoverflow.com/u/3392710/ ) and on the answer https://stackoverflow.com/a/71329209/ 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: Postgresql partition pruning not working when using unnest() in a subquery

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 PostgreSQL Partition Pruning Issues with UNNEST() in Subqueries

When working with PostgreSQL, especially in version 13.4 or later, many users encounter unexpected behavior with partition pruning while using UNNEST() in subqueries. This guide provides an in-depth exploration of the issue and offers concrete solutions to ensure efficient SQL queries.

The Problem: UNNEST() and Partition Pruning

Partition pruning is a feature in PostgreSQL that allows the database to skip over partitions that cannot possibly contain relevant data, therefore increasing performance. However, there's a specific case when using the UNNEST() function within a subquery where this feature seems to fail.

Example Scenario

Consider these two tables:

[[See Video to Reveal this Text or Code Snippet]]

When inserting data into these tables and running the following query:

[[See Video to Reveal this Text or Code Snippet]]

The execution plan reveals that all partitions are scanned, which is inefficient and counterproductive.

Why Partition Pruning Fails with UNNEST()

The main issue is linked to the way UNNEST() operates within a subquery. The execution plan does not leverage the filter condition effectively, resulting in PostgreSQL examining all partitions rather than narrowing down the search to only those that contain relevant data.

Additionally, if you do a similar query without UNNEST():

[[See Video to Reveal this Text or Code Snippet]]

In this case, PostgreSQL properly uses partition pruning and does not scan unnecessary partitions, demonstrating that UNNEST() specifically might hinder performance.

The Solution: Add More Data

One of the recommended solutions to this problem is to artificially increase the size of the dataset.

Steps to Implement the Solution

Insert More Rows: Populate your tables with a larger volume of data, ideally tens of thousands of rows.

Re-run the Query: Execute the original query with the UNNEST() function again.

Analyze the New Execution Plan: You should find that the execution plan now utilizes partition pruning effectively.

A user reported an experience where increasing the number of rows to approximately 100,000 enabled PostgreSQL to conduct valid partition pruning during execution, leading to much faster query responses.

Example Query After Adding Rows

[[See Video to Reveal this Text or Code Snippet]]

With sufficient data, the query expands into an execution plan that avoids scanning partitions that are guaranteed not to hold relevant data.

Conclusion

This peculiar behavior of PostgreSQL regarding UNNEST() and partition pruning can be counterintuitive, but by increasing the size of the dataset, you can ensure your SQL queries run efficiently. Next time you face this issue, remember to consider how much data you're working with before concluding that partition pruning isn't functioning as expected.

By understanding the mechanics behind PostgreSQL's query planning and execution, you can leverage its powerful partitioning features to optimize your database operations.