Optimize Performance
When queries slow down, there are three directions to work in: change how the query executes, trade precomputed results for query speed, and allocate resources among concurrent workloads.
If you are not sure where to start, read How Queries Are Processed to see the stages a query goes through, then Automatic Query Optimizations for the optimizations that apply without any change to your SQL.
Change how queries execute
Choose between the Postgres-based optimizer and GPORCA, each suited to different workloads.
Stale statistics skew cost estimates, leading the optimizer to a poor plan.
Specify join order, join method, and scan method when the optimizer’s choice is not right.
Large joins hit memory and disk limits while building the hash table; RuntimeFilter filters earlier.
Move aggregation ahead of the join to reduce the rows that participate.
Increase the number of compute nodes during execution so one query uses more of the cluster.
Trade precomputation and indexes for speed
Match existing materialized views during planning, with no SQL rewrite.
Refresh only the affected portion when base tables change.
Objects similar to materialized views that refresh on a schedule and can also rewrite queries automatically.
Use an index instead of a full scan for point lookups on AO tables.
Enforce uniqueness on AO tables at write time.
Build an index without blocking inserts, updates, and deletes on the table, once the global deadlock detector is enabled.
Use CREATE TABLE AS to build tables in parallel and speed up dependent materialized view refreshes.
Allocate and limit resources
Cap CPU, memory, concurrent transactions, and disk I/O per role so one workload cannot take over the cluster.