Optimize Query Performance
These topics follow the life of a query: first how a query is processed, then how to influence the optimizer’s decisions, and finally how to speed up execution or trade extra data structures for speed.
Understand query processing
How a query moves from parsing through planning to dispatch and execution.
How dynamic partition elimination and adaptive memory allocation cut down the data scanned.
Influence the optimizer
Choose between the Postgres-based optimizer and GPORCA, and tune GPORCA itself.
Run ANALYZE so cost estimates match the actual data distribution.
Specify join order, join method, and scan method directly.
Speed up execution
Increase the number of compute nodes during execution so one query uses more of the cluster.
Move aggregation ahead of the join to reduce the rows that reach it.
Use RuntimeFilter to filter early while the hash table is built, easing memory and disk pressure.
Trade structures for speed
Use an index instead of a full scan for point lookups and narrow ranges on AO tables.
Enforce uniqueness on AO tables at write time.
Match existing materialized views during planning, with no SQL rewrite.
Refresh only the affected portion when base tables change, instead of recomputing everything.