Mastering GaussDB(DWS) TopSQL: Boost Query Performance and Troubleshoot Issues

What is TopSQL?

TopSQL is a powerful built‑in performance analysis tool in GaussDB(DWS) that records running statements in real time (real‑time TopSQL) and completed statements (historical TopSQL). It helps locate abnormal query interruptions, long blocking times, and other production issues that would otherwise require extensive manual investigation.

TopSQL System Tables and Parameters

TopSQL captures resource usage, execution time, memory consumption, and diagnostic warnings at the job and operator level.

Key Parameters (GaussDB(DWS) 8.1.3)

enable_resource_track : on – Global switch for resource monitoring; TopSQL works only when enabled.

enable_resource_record : on – Controls whether real‑time TopSQL is persisted to historical TopSQL; recommended on for easier post‑mortem analysis.

resource_track_cost : 0 – Records only statements whose execution cost exceeds this threshold; default 0.

resource_track_duration : 1 – Interval (seconds) for persisting real‑time TopSQL to historical storage; default 60, recommended 1.

resource_track_level : query/perf – Determines the granularity of TopSQL information stored in system tables.

TopSQL_retention_time : 30 – Retention period (seconds) for TopSQL records; keep default.

enable_track_record_subsql : on-demand – Controls whether sub‑queries are recorded in TopSQL tables.

TopSQL System Tables

Real‑time TopSQL: pgxc_wlm_session_statistics Historical TopSQL:

pgxc_wlm_session_info

Important Fields

username : User name.

block_time : Queue time, useful for CCN scenarios.

Start_time : Statement start time.

duration : Execution duration (key metric).

Estimate_memory : Estimated memory usage.

Max_peak_memory : Actual maximum memory used.

Max_spill_size : Disk spill size.

Unique_sql_id : Normalized ID identifying the same statement with different parameters.

Cpu_skew_percent : CPU skew information.

warning : Self‑diagnostic warning messages.

Typical Application Scenarios

1. Real‑time TopSQL

Problem: An intermittent slow SQL in a cluster is not captured in historical TopSQL due to a large resource_track_duration setting.

Solution: Use the query ID to view the wait view, run EXPLAIN VERBOSE for the execution plan, and consult real‑time TopSQL to obtain the actual plan.

2. Historical TopSQL

Problem: A client experiences frequent out‑of‑memory errors; autopilot inspection shows periodic memory spikes and instance‑level memory skew.

Solution: Query pgxc_wlm_session_info for high‑memory statements, identify the offending SQL via unique_sql_id, and analyze its historical execution.

3. Stored Procedure Sub‑queries

Problem: Numerous stored procedures and anonymous blocks run slowly as data grows.

Solution: Enable enable_track_record_subsql to record sub‑queries, inspect the query_plan field, and re‑execute slow procedures while checking each sub‑statement’s plan via TopSQL.

How to Use TopSQL for Business Statistics

Identify statements with many streaming operators:

SELECT *, (LENGTH(query_plan) - LENGTH(REPLACE(query_plan, 'Streaming', ''))) / LENGTH('Streaming') AS stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC LIMIT 100;

Find high‑memory‑consumption statements:

SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2023-10-30 10:05' AND start_time < '2023-10-30 10:10' ORDER BY max_peak_memory DESC LIMIT 100;

Detect statements needing optimization (warnings present):

SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning IS NOT NULL ORDER BY duration DESC LIMIT 100;

TopSQL Usage Tips

Filter queries by start_time to avoid full‑table scans.

Limit result sets with LIMIT to prevent client OOM.