As data volumes and concurrent access grow, database performance becomes critical to system stability. Drawing from official technical manuals, this article distills core performance optimization techniques for GBASE's GBase 8c, the China‑domestically developed distributed database, covering statistics updates, execution plan intervention, SQL rewriting, and key parameter configuration. GBase 8c is a cost‑based distributed database. Its optimizer acts like a precise navigation system, choosing what it considers the optimal execution path (execution plan) for every SQL statement. This decision relies heavily on "map data"—statistics. The optimizer estimates row counts and costs based on statistics gathered by the ANALYZE command (stored in pg_class and pg_statistic system tables). Outdated statistics are like using an old map—they can easily mislead the optimizer into generating poor execution plans. Core principle: Before attempting any complex tuning, first ensure your statistics are accurate and up to date. Statistics are the source data the planner uses to generate plans: -- Update statistics for a single table ANALYZE tablename; -- Update statistics for all tables in the current database ANALYZE; -- Collect multi‑column statistics for correlated columns ANALYZE tablename((column_1, column_2)); Automation: GBase 8c provides the autovacuum daemon to automatically reclaim space and update statistics. Use the autovacuum and autovacuum_mode parameters to control its behavior (e.g., set to mix mode for both cleanup and analysis). Business signals: Interface timeouts, slow application responses, or errors are the most direct indicators. Proactive detection: Conduct database inspections or query slow logs. Enable slow logging by configuring the track_stmt_stat_level parameter (e.g., 'OFF,L0') and ensure enable_stmt_track is on. Query slow logs: SELECT * FROM dbe_perf.get_global_slow_sql_by_timestamp('2024-05-07 04:00:00', '2024-05-07 04:10:00'); When the optimizer chooses an undesirable plan, GBase 8c provides powerful Plan Hint functionality to guide the optimizer directly. Add Hints using /*+ ... */ comment syntax before the SQL statement. Separate multiple Hints with spaces: SELECT /*+ ... */ * FROM table_name; Join order: Leading((t1 t2 t3)) Join method: NestLoop(t1 t2), HashJoin(t1 t2), MergeJoin(t1 t2) Scan method: IndexScan(t1 index_name), SeqScan(t1), IndexOnlyScan(t1 index_name) Estimated row count: Rows(t1 #10) (suggests table t1 has about 10 rows) By default, a two‑table join may use HashJoin. If you determine NestLoop is better, force it: -- Default may be HashJoin EXPLAIN SELECT * FROM stu s JOIN stu_info i ON s.id = i.stu_id; -- Force NestLoop with a Hint SELECT /*+ NestLoop(s i) */ * FROM stu s JOIN stu_info i ON s.id = i.stu_id; Caution: Hints are a double‑edged sword. They bypass the optimizer's cost calculations. Use them as a last resort and continually verify their effectiveness. Adjusting database parameters can nudge execution plans toward better strategies: work_mem: Increasing this value helps operations like sorts and hash joins complete in memory, reducing disk I/O. shared_buffers: Used for caching data. For read‑heavy applications, increasing this value can significantly improve performance. Optimizer switches: Parameters like enable_hashjoin and enable_indexscan can disable specific query strategies, forcing the optimizer to choose alternatives (similar to Hints, but applied at the session or global level). Example configuration: gs_guc set -Z coordinator -D /path/to/data_dir -c "work_mem = 16MB" Use UNION ALL instead of UNION: If business logic guarantees no duplicates, UNION ALL avoids expensive deduplication. Add non‑null filters for JOIN columns: If NULL values are common on JOIN columns, adding IS NOT NULL filters data early and improves JOIN efficiency. Use TRUNCATE instead of DELETE to clear tables: TRUNCATE is faster and releases disk space immediately. Explicitly specify column names in INSERT statements: Improves readability and stability. Choose the right storage model: Select row‑store or column‑store tables based on the workload (OLTP vs OLAP). Performance tuning for GBase 8c is a systematic process, with statistics, execution plan intervention, and SQL rewriting all interlinked. Integrating these methods into daily operations will help you effectively tackle the growing challenge of slow queries in your gbase database environment.