AnalyticDB for PostgreSQL is equipped with the transactional processing (TP) and analytical processing (AP) optimizers. The TP optimizer is designed to support simple SQL queries. The AP optimizer is designed to support complex SQL queries. AnalyticDB for PostgreSQL optimizes database performance in hybrid transactional/analytical processing (HTAP) scenarios.

Magic set pushdown

Magic set pushdown is an efficient rewriting rule that is suitable for online analytical processing (OLAP) scenarios. Magic set pushdown supports data filtering, which reduces the costs of aggregation on large tables and improves join performance. AnalyticDB for PostgreSQL uses the magic set pushdown rule for the optimizers, which improves the query performance in OLAP scenarios.

Short-circuit tuning for condition filtering

The execution engine uses short-circuit filters to improve the execution efficiency of scalar Boolean expressions. Example:
condition_1  AND condition_2 AND ... condition_k 

In the preceding expression, if condition_1 is false, the entire expression is false based on the AND conjunction. The compute engine skips remaining conditions.

The optimizers of AnalyticDB for PostgreSQL can use the filter operator to adjust the order of condition branches in an expression and modify an algorithm. The condition branch whose result is most likely to be false takes precedence over other condition branches. This way, short-circuit filters are fully used in conjunctive Boolean expressions to reduce costs.

Adaptive selection

AnalyticDB for PostgreSQL introduces the adaptive selection algorithm for the HTAP optimizers. The system dynamically selects an optimizer for optimal performance in OLAP or OLTP scenarios. You do not need to manually select an optimizer.