This article focuses on KES database performance optimization and systematically breaks down a practical workflow for slow query diagnosis, SQL rewrites, index design, and parameter tuning. It addresses common pain points such as systems that gradually slow down, frequent full table scans, and instability under high concurrency. Keywords: KES, SQL optimization, index tuning.
Technical specifications provide a quick snapshot.
| Parameter | Details |
|---|---|
| Database | KES / KingbaseES |
| Type | Enterprise-grade relational database |
| Compatible ecosystem | PostgreSQL-style system views and execution plans |
| Core scenarios | Slow query governance, high-concurrency optimization, large-table maintenance |
| Article popularity | Approximately 8.9k views |
| Article tags | Database, Performance Optimization, SQL Optimization |
| Core dependencies | EXPLAIN ANALYZE, sys_stat_activity, sys_stat_statements |
AI Visual Insight: The image serves as a cover-style illustration for KES database performance tuning, highlighting the complete optimization chain from diagnosis and SQL tuning to index design and parameter configuration. It works well as a thematic entry point for collaborative troubleshooting between database administrators and developers.
KES performance optimization must follow a diagnose-first, tune-second closed loop.
KES can handle high concurrency and large data volumes in government, finance, energy, and similar environments. However, most performance issues do not stem from limitations in the database kernel itself. They usually originate from SQL design, indexing strategy, parameter settings, and table structure choices.
The key to effective optimization is not simply “tuning parameters.” It is building a reliable workflow: identify the bottleneck first, verify the execution plan next, and apply low-risk changes last. That is how you move from “it runs” to “it runs fast and stays stable.”
Performance diagnosis should start with slow queries and system views.
The first step is to enable the slow query log and capture SQL statements that exceed a threshold. Without real slow SQL samples, most follow-up optimization becomes guesswork.
# Record SQL statements that run longer than 500 ms
log_min_duration_statement = 500
# Enable statement logging to trace request patterns
log_statement = all
# Turn on slow query logging
slow_query_log = onThis configuration establishes a basic observability layer for slow queries and makes it easier to pinpoint hot statements precisely.
Execution plans can directly expose full table scans and ineffective index usage.
Use EXPLAIN ANALYZE to inspect the actual access path chosen by the optimizer. If the result contains Seq Scan, the database is usually scanning the entire table. If it shows Index Scan, the index path is being used as expected.
EXPLAIN ANALYZE
SELECT *
FROM t_user
WHERE username = '张三'; -- Chinese filter value example for observing index hitsThis SQL statement verifies whether a single-condition query correctly uses an index and is the shortest path to identifying the right optimization direction.
System views can help confirm connection pressure, hot SQL, and scan patterns.
-- Check the total number of current connections
SELECT count(*) FROM sys_stat_activity;
-- Find the SQL statements with the highest cumulative duration
SELECT query, duration
FROM sys_stat_statements
ORDER BY duration DESC;
-- Compare sequential scans with index scans
SELECT relname, seq_scan, idx_scan
FROM sys_stat_user_tables;This set of queries helps you quickly determine whether the system is bottlenecked by connection count, slow SQL, or excessive full table scans.
SQL rewrites usually deliver the highest return on optimization effort.
Most production instability does not require touching database parameters first. Fixing SQL often comes first. Reducing unnecessary column reads, avoiding functions that invalidate indexes, and flattening nested subqueries can directly reduce I/O and CPU pressure.
Reducing unnecessary reads and avoiding index invalidation can immediately improve response time.
SELECT * increases network transfer, cache usage, and disk read costs. Selecting only the required columns is one of the easiest and most consistently beneficial improvements.
-- Anti-pattern: read every column from the table
SELECT * FROM t_order;
-- Better pattern: return only the columns required by the business logic
SELECT oid, user_id, total_price
FROM t_order;The goal of this rewrite is to reduce unnecessary column reads and lower I/O, memory, and network overhead.
Take it one step further and avoid wrapping indexed columns in functions, because the optimizer often cannot use existing indexes in that case.
-- Anti-pattern: function processing invalidates index usage
SELECT * FROM t_user
WHERE SUBSTR(phone, 1, 3) = '138';
-- Better pattern: prefix matching can use the index
SELECT * FROM t_user
WHERE phone LIKE '138%';This example shows that the same business intent can trigger completely different access paths depending on how the SQL is written.
Deep pagination and nested subqueries should be rewritten first.
Deep pagination with OFFSET forces the database to skip a large number of rows. The deeper the page, the slower the query. Rewriting it as anchor-based pagination using a primary key or cursor is much more stable.
-- Anti-pattern: deep pagination scans and discards a large number of preceding rows
SELECT * FROM t_order
ORDER BY oid
LIMIT 20 OFFSET 10000;
-- Better pattern: continue paging from a primary key anchor
SELECT * FROM t_order
WHERE oid > 10000 -- Core logic: narrow the scan set by using a primary key range
ORDER BY oid
LIMIT 20;The value of this rewrite is that it converts “skip-based scanning” into “range-based scanning.”
For subqueries, rewrite them as JOINs whenever possible. That gives the optimizer a better chance to generate a stable execution plan.
Index design determines whether the query path remains controllable.
More indexes are not always better. More precise indexes are. Prioritize indexes on frequently filtered columns, JOIN columns, sort columns, and unique columns. At the same time, avoid over-indexing low-selectivity columns and frequently updated columns.
Composite indexes must follow the leftmost prefix rule.
The matching order of a composite index directly affects its hit rate. If an index is defined as (username, status), a query on status alone usually cannot use that index effectively.
CREATE INDEX idx_user_name_status
ON t_user(username, status);
-- Can use the index: matches the leftmost column
SELECT * FROM t_user WHERE username = '张三';
-- Can use the index: matches continuously from the leftmost column
SELECT * FROM t_user WHERE username = '张三' AND status = 1;This example highlights the importance of column order in composite index design. A poor order can dramatically reduce the value of the index.
You should also clean up ineffective indexes regularly to avoid write amplification.
-- Check whether an index is actually being scanned and used
SELECT indexrelname, idx_scan
FROM sys_stat_user_indexes
WHERE relname = 't_user';This query helps identify indexes that have gone unused for a long time and provides evidence for index cleanup.
Parameter tuning should be based on the resource model and workload characteristics.
Default parameters are usually conservative. They fit entry-level environments, not production workloads. In KES, parameter tuning typically focuses on memory, connections, and WAL/checkpoint-related settings, but you must confirm machine resource limits before making changes.
# Shared buffer pool, often set to about one quarter of total memory
shared_buffers = 8GB
# Memory available for sort and hash operations per query
work_mem = 64MB
# Memory used for maintenance operations
maintenance_work_mem = 256MB
# Estimated OS cache capacity available to the database
effective_cache_size = 16GBThese parameters can improve cache hit rates and sorting efficiency, but they must be evaluated carefully against actual memory capacity.
High-concurrency workloads also require coordinated connection, transaction, and batch-write governance.
More connections are not always better. Increasing max_connections blindly can amplify context switching and memory consumption. Transactions should also remain as short as possible to avoid holding locks for too long.
-- Batch inserts are better than committing rows one by one in a loop
INSERT INTO t_user (uid, username)
VALUES
(1001, 'a'),
(1002, 'b'),
(1003, 'c'); -- Core logic: merge multiple round trips into a single commitThis SQL example shows one of the most common low-cost optimizations for high-concurrency writes: batch commits.
Real-world cases prove that optimization should center on coordinated SQL and index improvements.
In one government platform, a work-item list query originally took 8 to 12 seconds. The root causes included nested subqueries, LIKE '%张三%' patterns that invalidated index usage, a missing composite index on the order table, and SELECT * expanding the read scope unnecessarily.
The optimization plan should correct both the access path and the returned columns.
-- Create a composite index for filtering and sorting
CREATE INDEX idx_gov_order_user_time
ON t_gov_order(user_id, create_time);
-- Create an index for user name prefix lookups
CREATE INDEX idx_gov_user_name
ON t_gov_user(user_name);These indexes move both user filtering and time-based sorting into an optimizable access path.
SELECT o.order_id, o.order_title, o.create_time
FROM t_gov_order o
JOIN t_gov_user u ON o.user_id = u.user_id -- Core logic: replace an inefficient subquery with a JOIN
WHERE u.user_name LIKE '张三%'
ORDER BY o.create_time DESC
LIMIT 20;This optimized SQL resolves the subquery issue, fuzzy matching issue, and unnecessary column reads at the same time.
The final result reduced query latency from the 8-second range to roughly 0.02 seconds, without downtime and with almost no visible impact on the business side.
A stable performance optimization system must include validation and regression checks.
A successful tuning change once does not guarantee long-term stability. After any index, SQL, or parameter change, you should re-check execution plans, slow query trends, CPU and memory usage, and lock waits to avoid a situation where “it gets faster today but regresses next week.”
Common mistakes include too many indexes, incorrect composite index order, leaving deep pagination untreated, long-running transactions, overly aggressive parameter settings, modifying table structures without backups, and skipping validation after optimization. These issues are often more dangerous than a single slow SQL statement.
FAQ provides structured answers to common questions.
1. Should KES performance optimization start with SQL changes or parameter tuning?
Start with SQL and indexes. Most performance issues come from full table scans, inefficient pagination, subqueries, and ineffective index usage. Parameter tuning should only happen after the query path is already reasonable.
2. How can I quickly tell whether a SQL statement needs an index?
Start with EXPLAIN ANALYZE, then check whether the column appears frequently in WHERE, JOIN, ORDER BY, or GROUP BY. If the execution plan consistently shows Seq Scan and the query runs often, it is worth evaluating an index.
3. Why is my query still slow after I created an index?
Common causes include using functions on indexed columns, placing % at the beginning of a fuzzy match, incorrect composite index column order, outdated statistics, and returning too many columns, which increases table lookup costs. The existence of an index does not guarantee that the optimizer will use it.
Core summary: This article reconstructs a practical and directly applicable KES database performance optimization methodology. It covers slow query diagnosis, execution plan analysis, SQL rewrites, index design, parameter tuning, high-concurrency governance, and a government-platform case review to help developers build a complete closed loop from diagnosis to optimization and validation.