[Proposal] Switch between Postgres and gporca optimizers

[leborchuk]

Proposers

No response

Proposal Status

Under Discussion

Abstract

Switch between Postgres and gporca optimizers to improve query execution time

Motivation

CB has a unique feature - two optimizers! Out of the databases I know, only Oracle has RBO and CBO. And RBO is almost never better than CBO. Whereas in CB, each of the optimizers is unique, and for different queries either one or the other will be better. Now the database user must now decide for himself which optimizer to use. However, this choice is too time-consuming and difficult for the user.

Let's implement a feature that will select the appropriate optimizer for an SQL query. Such that the final planning time + execution time is minimal.

The task is difficult in its full formulation. But we can start by solving a simpler problem. Some of our users use CB as a unify database, a replacement of Oracle/MSSQL. And their typical workload is: perform a lot of simple database queries in a loop. In that pattern, simple queries should be executed as quickly as possible. An example of such a workload is the pgbench test.

My experiments in #900 It has been shown that using the postgres optimizer significantly (at times) speeds up execution time. Because when using GPOCA, most of the time is spent on query planning.

Let's

• agree that a feature is needed,
• define what a simple query is,
• agree on an interface for working with the feature.

Implementation

I proposed the implementation: let's use postgres optimizer if we do not have any of: aggregation, with clause, recurse clause, window functions. And the number of relations in a query less or equal optimizer_relations_threshold. Otherwise, use gporca.

avamingli@ opposed:

Besides all the concerns which are not resolved: subquery, proportion of these so-called simple queries are actually insert statements.

I believe there are fundamental issues with the current implementation regarding the optimizer_relations_threshold.

1.Partitioned Tables:

Partitioned Tables: When using a partitioned table (let's call it par) with optimizer_relations_threshold set to 1, this means that ORCA is not chosen as the optimizer.
However, when there might be two or more range tables involved in a query, the optimizer_relations_threshold can prevent ORCA from being utilized effectively. This undermines one of ORCA's key features: partition pruning. Consequently, the benefits of ORCA are lost.

CREATE TABLE partrl (a int, b int, c int)
DISTRIBUTED BY (a)
PARTITION BY range(b)
SUBPARTITION BY list(c)
(
        PARTITION p1 START (10) END (20) EVERY (5)
        (
                SUBPARTITION sp1 VALUES (1, 2)
        ),
        PARTITION p2 START (0) END (10)
        (
                SUBPARTITION sp2 VALUES (3, 4),
                SUBPARTITION sp1 VALUES (1, 2),
                DEFAULT SUBPARTITION others
        )
);

SELECT * FROM patrl; 

A single entry in rtable but in fact there would be all children tables during planner expanding those.

2.Inherited Tables:

Inherited Tables: The same logic applies to inherited tables. When selecting from a parent table, there can be multiple child tables. This scenario also violates the notion of a "simple query," which is defined as having fewer range table entries than the optimizer_relations_threshold.

3. Union/INTESECT/xxx

Union Operations: Similarly, in cases involving UNION, such as SELECT * FROM t1 UNION ALL SELECT * FROM t2, the planner expands these into multiple range tables ex: Void RTE, leading to scenarios where the number of range table entries exceeds the threshold.

4. GUC compatibility

Adding this code may create complications in the future, especially if we later resolve the issues with ORCA. We could end up with redundant code and GUCs that are no longer useful, which could lead to confusion and compatibility issues for users who have already adopted them.

5. Put the ORCA codes into ORCA

BTW, one simple rule is that ORCA GUCs should be used in ORCA codes, not pg planner side.
optimizer_xxxx means it belongs to ORCA c++ codes, even you tried to implement it, please obey the rule if you want to fallback to pg planner for some reasons.

These examples illustrate how the current design of optimizer_relations_threshold is problematic. I suspect there may be additional issues that I have not identified.
While I have outlined these concerns, I do not expect later fixes on this PR.
My point is that we should address the root problems with ORCA rather than introducing workarounds that complicate the kernel codes for planner maintainers (don't push DBA's workaround to kernel).
I'm not convinced that it's worth discussing further unless there are stronger reasons and test data presented.
Instead of pursuing this approach, our focus should be on fixing the real problems with ORCA.

Rollout/Adoption Plan

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!