AnalyticDB for PostgreSQL includes two optimizers: the transactional processing (TP) optimizer for simple SQL queries and the analytical processing (AP) optimizer for complex SQL queries. Together, they optimize performance across hybrid transactional/analytical processing (HTAP) workloads.
| Optimizer | Best for |
|---|---|
| TP optimizer | Simple SQL queries |
| AP optimizer | Complex SQL queries |
How it works
Each query is routed to the optimizer best suited for it. The adaptive selection algorithm analyzes the query at runtime and automatically picks the TP or AP optimizer — no manual configuration required.
Three techniques drive query performance:
| Technique | What it does |
|---|---|
| Magic set pushdown | Reduces aggregation costs on large tables and improves join performance in OLAP workloads |
| Short-circuit tuning | Reorders condition branches so the branch most likely to be false runs first, cutting evaluation costs in AND-joined expressions |
| Adaptive selection | Dynamically routes each query to the TP or AP optimizer based on query characteristics |
Magic set pushdown
Magic set pushdown is an efficient rewriting rule for online analytical processing (OLAP) workloads. It pushes data filtering earlier in the query plan, reducing the cost of aggregation on large tables and improving join performance. AnalyticDB for PostgreSQL applies the magic set pushdown rule to the optimizers to improve query performance in OLAP scenarios.
Short-circuit tuning for condition filtering
The execution engine applies short-circuit filters to improve the execution efficiency of scalar Boolean expressions. In an expression like:
condition_1 AND condition_2 AND ... condition_kIf condition_1 evaluates to false, the entire expression is false — the engine skips all remaining conditions.
The optimizer uses a filter operator to reorder condition branches before execution and modify an algorithm, placing the branch most likely to evaluate to false first. This maximizes short-circuit opportunities and reduces total evaluation costs.
Adaptive selection
The HTAP adaptive selection algorithm automatically selects between the TP and AP optimizers at query runtime. The system picks the optimizer that delivers the best performance for each query — whether it is an OLAP or online transaction processing (OLTP) workload.
No manual intervention is needed. Adaptive selection handles mixed workloads where both transactional and analytical queries run against the same database.