Query Performance Overview

SynxDB improves query performance through dynamic partition elimination and adaptive memory allocation. These mechanisms help reduce the amount of data scanned, speed up query execution, and enhance overall concurrency.

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 Left Join

  • Hash Right Join

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.

  • 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.