Automatic Query Optimizations

This topic covers several query optimizations that take effect automatically: dynamic partition elimination prunes irrelevant partitions at execution time to reduce the amount of data scanned, memory optimization allocates and releases memory according to the characteristics of each operator, and numeric aggregate optimization speeds up SUM and AVG on numeric columns of certain precisions. None of them require rewriting your SQL.

Tip

SynxDB uses the GPORCA optimizer by default, which extends the native Postgres planner with more advanced optimization capabilities.

Dynamic partition elimination

SynxDB supports dynamic partition elimination (DPE), a feature that prunes partitions at query execution time based on runtime values. This reduces the data scanned and improves query efficiency.

DPE is supported for the following join types:

  • Hash Inner Join

  • Hash Right Join

  • Hash Semi Join (produced by EXISTS and IN subqueries)

Hash Left Join is not supported. A left join must preserve every row of the outer table, so pruning any partition would drop results.

DPE is enabled when the following conditions are met:

  • The partitioned table is on the outer side of the join.

  • The join condition is an equality predicate on the partition key.

  • No motion operator sits above the scan of the partitioned table. If the distribution key of the partitioned table differs from the join key, the plan inserts a Redistribute Motion above the scan, and the partition selector’s result arrives too late to affect it, so DPE does not take effect. Either align the distribution key of the partitioned table with the join key, or let the dimension table join through a broadcast or a replicated table.

  • Statistics are collected on the partitioned tables. For example:

    ANALYZE <root partition>;

The gp_dynamic_partition_pruning parameter controls whether DPE is enabled. It is ON by default but only applies to the Postgres optimizer. You can verify if DPE is in effect by checking the EXPLAIN plan for the presence of a Partition Selector node.

Memory optimization

SynxDB dynamically allocates memory based on the characteristics of each operator within a query. It proactively releases and reallocates memory resources during different stages of query execution, leading to more efficient resource utilization and query performance. In addition, SynxDB optimizes memory management for the Hash Aggregate operator when processing large-scale data, improving memory utilization and significantly reducing the risk of performance degradation or disk spills caused by insufficient memory. This optimization is transparent to users.

Numeric aggregation optimization

SynxDB automatically optimizes SUM and AVG aggregate functions on numeric(P, 2) columns where P < 17 by internally converting them to efficient 64-bit integer operations. This optimization is fully transparent — no configuration is required and query results remain identical.

Tip

For columns that store values with exactly two decimal places, such as monetary amounts, prices, or fees, it is recommended to use the numeric(P, 2) type (with P < 17) to benefit from this optimization.