Are there any plans to further develop GPORCA to support all query performance optimizations?

[KrasnovidKE]

The query performance optimizations listed below (taken from the documentation at https://cloudberry.apache.org/docs/performance/) work exclusively for the Postgres query optimizer AKA Legacy Optimizer:

• Parallel Queries Execution
• Aggregation pushdown
• IndexScan on AO Tables
• RuntimeFilter

However, this legacy optimizer produces extremely complex and generally poor plans for large queries compared to GPORCA.

So is it planned to improve GPORCA or maybe there are plans to somehow improve Legacy optimizer?

In short, the question can be generalized to a request to disclose plans for the future of both optimizers

[my-ship-it]

Hi @KrasnovidKE
In fact, we plan to enable those features you mentioned in GPORCA in our roadmap. In the long term, we plan to narrow the difference between GPORCA and Legacy optimizer, so that there is no need for users to be torn about which optimizer to choose.

There are two ways:
The first is to cherry pick GPORCA's special features back to Legacy planner, and contribute those features to PostgreSQL, and maintain one planner finally, but it would take time for PostgreSQL community to accept those changes. Comparing benchmark(TPC-H/TPC-DS) results based on Legacy planner and GPORCA is the guideline for us to do the work.

Another approach is, enabling features that comes from PostgreSQL kernel upgrading in GPORCA. For example, clean up FIXMEs in GPORCA, enable incremental sort, and etc.

[avamingli]

However, this legacy optimizer produces extremely complex and generally poor plans for large queries compared to GPORCA.

Max and I previously discussed a related topic and plan to enhance our AQUMV feature to support complex SQL queries. Specifically, we aim to ensure that all examples from TPC-DS can utilize the AQUMV functionality.
This will greatly address the efficiency issues related to complex queries, especially in cases like those in TPC-DS.

Additionally, we intend to support two different optimizers so that users can quickly obtain query results regardless of which one they choose to use.

This is a complex and long-term task, and it is already on my list of plans.

[KrasnovidKE]

Hi @avamingli @my-ship-it

Great news! :)

Thank you for your detailed answers.