AnalyticDB:Optimize query performance

Collect table statistics

The query optimizer generates execution plans based on table statistics. Stale or missing statistics cause the optimizer to make poor estimates, resulting in inefficient plans.

Run ANALYZE after a large data load or after more than 20% of a table's rows are updated.

-- Collect statistics on all tables
ANALYZE;

-- Collect statistics on all columns of table t
ANALYZE t;

-- Collect statistics on a specific column
ANALYZE t(a);

For most workloads, running ANALYZE t on the modified table is sufficient. Use column-level ANALYZE only when you need tighter control—for example, on columns used as join keys, filter conditions, or index columns.

Choose a query optimizer

AnalyticDB for PostgreSQL includes two query optimizers. Each is optimized for different workloads.

Switch optimizers at the session level:

-- Enable the Legacy query optimizer
SET optimizer = off;

-- Enable the ORCA query optimizer
SET optimizer = on;

-- Check the current optimizer
SHOW optimizer;
-- on = ORCA query optimizer
-- off = Legacy optimizer

To change the optimizer at the instance level, Submit a ticket.

Use indexes to accelerate queries

Indexes speed up queries that scan a small fraction of a table based on a filter condition. AnalyticDB for PostgreSQL supports three index types.

Example: adding a B-tree index

Without an index, the optimizer performs a full table scan:

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Create a B-tree index on the b column:

postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

With the index, the optimizer uses an index scan instead:

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

View query plans

A query plan is a tree of operators that describes how the database executes a query. Reading the plan helps you identify why a query is slow.

How to read a query plan

• The plan is a tree. Execution starts at the leaf nodes (bottom) and flows up to the root.

• cost=<startup>..<total> is the optimizer's estimated cost, not actual milliseconds. A high cost estimate means the optimizer expects the operation to be expensive—use it for comparison, not as an absolute measure.

• rows= is the estimated number of rows. A large gap between estimated and actual rows often points to stale statistics—run ANALYZE to fix it.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN displays the plan without running the query.

• EXPLAIN ANALYZE runs the query and adds actual execution times and row counts to the plan.

-- Display plan only (no execution)
postgres=# EXPLAIN SELECT a, b FROM t;
                                      QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

-- Run query and show actual timings
postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

Identify the bottleneck from EXPLAIN ANALYZE output

Use the actual timing and row data to classify the bottleneck:

Supported operator types

Example: reading a join query plan

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Reading this plan from the bottom up:

1. Table Scan — scans t1 and t2.

2. Redistribute Motion — shuffles rows from both tables across compute nodes based on the hash value of b, so matching rows land on the same node.

3. Hash — builds a hash table on t2 for the join.

4. Hash Join — joins t1 and t2 on the hash key.

5. Gather Motion — sends results from compute nodes to the coordinator node, which returns them to the client.

Remove distribute operators

When AnalyticDB for PostgreSQL joins or aggregates data across compute nodes, it inserts Redistribute Motion or Broadcast Motion operators to shuffle data. These operators consume significant network bandwidth. Aligning distribution keys on join columns eliminates the need to redistribute data.

How it works

If two tables are joined on column a and both are distributed by a, each compute node already holds matching rows—no data movement is needed.

Example

SELECT * FROM t1, t2 WHERE t1.a = t2.a;

t1 is distributed by column a. If t2 is distributed by column b (a mismatch), AnalyticDB for PostgreSQL must redistribute t2:

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)

If t2 is also distributed by a, the join runs without redistribution:

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

To fix a mismatch, change the distribution key of t2:

ALTER TABLE t2 SET DISTRIBUTED BY (a);

Use matching data types on join columns

Joining columns of different data types triggers data type conversion, which forces data redistribution. Use the same data type on both sides of a join key.

Explicit type conversion

An explicit cast in the SQL statement changes the hash function applied to the column, causing the optimizer to redistribute both tables:

-- No type conversion: no redistribution
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

-- Explicit cast to numeric: triggers redistribution of both tables
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Implicit type conversion

When two join columns use incompatible types, the database converts one type automatically. For example, timestamp without time zone and timestamp with time zone use different hash functions. The optimizer falls back to Broadcast Motion instead of a hash-based join:

postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                               QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

To eliminate implicit conversions, align column types at table definition time. For example, define both t1.a and t2.a as timestamp without time zone.

Locate data skew

Data skew occurs when rows are unevenly distributed across compute nodes. A skewed table causes one compute node to do most of the work while others sit idle, which slows down the entire query.

Check the row distribution across compute nodes:

postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

This output shows severe skew: node 0 holds 99% of the rows. Fix it by reassigning the distribution key:

-- Option 1: change the distribution key in place
ALTER TABLE t1 SET DISTRIBUTED BY (b);

-- Option 2: recreate the table with a better distribution key
-- Create the new table, bulk-load data, then swap it in place of the original

Choose a distribution key with high cardinality and no dominant values to ensure an even spread.

View running SQL statements

When many queries run concurrently, the instance may report slow responses or insufficient resources. Use the pg_stat_activity view to see what is currently running.

postgres=# SELECT * FROM pg_stat_activity;

Key fields:

Filter to active queries only:

SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

Find the five longest-running queries:

SELECT current_timestamp - query_start AS runtime,
       datname,
       usename,
       current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

Check lock status

If a query holds a lock for an extended period, other queries on the same object wait indefinitely. Run the following query to see which tables are locked and which sessions hold the locks:

SELECT pgl.locktype   AS locktype,
       pgl.database   AS database,
       pgc.relname    AS relname,
       pgl.relation   AS relation,
       pgl.transaction AS transaction,
       pgl.pid        AS pid,
       pgl.mode       AS mode,
       pgl.granted    AS granted,
       pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

To unblock a waiting query, cancel or terminate the session holding the lock:

-- Cancel the query (does not work if the session is IDLE)
SELECT pg_cancel_backend(pid);

-- Terminate the session and roll back its uncommitted transactions
SELECT pg_terminate_backend(pid);

pg_cancel_backend has no effect when pg_stat_activity.current_query shows IDLE. Use pg_terminate_backend in that case.

Enable nested loop joins

Nested loop joins are disabled by default. For queries that return a small number of rows—due to highly selective filter conditions and a LIMIT clause—enabling nested loop joins can significantly reduce query time.

When to enable

The optimization is effective when:

• A filter condition on one table returns very few rows.

• A LIMIT clause further restricts the result set.

• Indexes are available on the join columns of both tables.

Example

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' AND t1.c2 < '230769549'
LIMIT 100;

Check and enable nested loop joins for the session:

SHOW enable_nestloop;
 enable_nestloop
-----------------
 off

SET enable_nestloop = on;

SHOW enable_nestloop;
 enable_nestloop
-----------------
 on

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' AND t1.c2 < '23432442'
LIMIT 100;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.26..16.31 rows=1 width=18608)
   ->  Nested Loop  (cost=0.26..16.31 rows=1 width=18608)
         ->  Index Scan using t1 on c2  (cost=0.12..8.14 rows=1 width=12026)
               Filter: ((c2 >= '230769548'::bpchar) AND (c2 < '230769549'::bpchar))
         ->  Index Scan using t2 on c1  (cost=0.14..8.15 rows=1 width=6582)
               Index Cond: ((c1)::text = (T1.c1)::text)

The plan uses index scans on both tables and a nested loop join, avoiding a full table scan and data redistribution.

enable_nestloop is a session-level setting. Reset it after the query if other queries in the same session benefit from hash joins.