Proposal: Enable Parallel DQA Plans (with streaming hash agg)

[avamingli]

Description

We have successfully enabled various parallel query execution capabilities within our system, significantly boosting performance for many types of queries. However, we have yet to incorporate DQA into our parallel plan. This absence limits our ability to optimize parallel processing, especially for DISTINCT aggregation queries.

DQA with streaming hash aggregation could be with parallel plans. Integrating this functionality could substantially enhance performance for aggregation-heavy queries, leveraging the benefits of parallelism.

While many parallel queries are supported, DQA has been excluded mainly due to the need to ensure uniqueness with DISTINCT.

Key Points:

• DQA: parallel path
• Streaming Hash Aggregation: parallel path
• Challenges: We need to address uniqueness guarantees under parallel execution, considering UNIQUE paths and TupleSplit.

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[avamingli]

Hi, we recently encountered the same issue in a customer's environment, so I'd like to revisit this topic.

In the customer's setup, there are numerous DISTINCT queries running against very large tables, resulting in slow query performance. The customer's requirement is to improve query efficiency by parallelizing DISTINCT operations.

In PostgreSQL, DISTINCT operations cannot be parallelized because deduplication requires ensuring uniqueness across all records. In a single-server database like PostgreSQL, this isn't feasible - worker processes launched by the Gather node would randomly compete when processing data.

However, in a distributed environment, DISTINCT operations can indeed be parallelized. In fact, DISTINCT processing based on data distribution strategies already involves multiple processes (a Gang of workers) performing deduplication simultaneously across different nodes.

Inspired by this approach, we could implement distributed parallel processing for DISTINCT operations by redistributing data to parallel worker processes based on appropriate conditions for subsequent processing.

[avamingli]

Done in #1173