DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

When Execution Plans Go Wrong in GBase 8a: A Tuning Guide from Statistics to Hints

#gbase #database #数据库 #performance

In day-to-day operations of a gbase database, a query that ran in seconds yesterday might suddenly take minutes today — even with indexes in place. The root cause is often stale statistics that mislead the cost‑based optimizer. This guide covers the essential tools: understanding cardinality, using ANALYZE TABLE, reading EXPLAIN output, and applying Hints when necessary.

The Core Metric: Cardinality

The optimizer relies on statistics like row counts and cardinality (the number of distinct values in a column) to estimate how effective an index will be. High cardinality encourages index use; low cardinality may lead to a full table scan. If statistics are out of date, the optimizer makes poor decisions.

-- Check column statistics
SHOW INDEX FROM orders;
-- Alternatively
SELECT TABLE_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';
Enter fullscreen mode Exit fullscreen mode

Refreshing Statistics with ANALYZE TABLE

ANALYZE TABLE updates statistics immediately for new query plans. Existing running queries are not affected.

-- Single table
ANALYZE TABLE orders;
-- Multiple tables
ANALYZE TABLE orders, order_items, customers;
Enter fullscreen mode Exit fullscreen mode

Crucially, after adding a new partition, you must run ANALYZE TABLE. Without it, the optimizer underestimates the partition's size, potentially disabling partition pruning and selecting wrong parallelism.

ALTER TABLE sales ADD PARTITION (PARTITION p202406 VALUES LESS THAN ('2024-07-01'));
ANALYZE TABLE sales;
Enter fullscreen mode Exit fullscreen mode

Diagnosing with EXPLAIN 

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
Enter fullscreen mode Exit fullscreen mode

If type shows ALL, it's a full table scan. Common causes: missing index, outdated statistics (cardinality is zero or too low), implicit type conversion, or using a function on the indexed column.

Solution workflow:

  1. Check if the index exists (SHOW INDEX).
  2. Run ANALYZE TABLE.
  3. Verify with EXPLAIN again.
  4. If still ALL, inspect the SQL for type mismatches or functions on the index column.

Hint: Emergency Override

When statistics are correct but the optimizer still picks a bad plan, Hints provide a temporary override. Use them sparingly and document why.

  • JOIN order: 
  SELECT STRAIGHT_JOIN a.*, b.name
  FROM large_table a JOIN small_table b ON a.id = b.id;
Enter fullscreen mode Exit fullscreen mode
  • Index hints: 
  SELECT * FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 12345;
  SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 12345;
  SELECT * FROM orders IGNORE INDEX (idx_create_time) WHERE customer_id = 12345;
Enter fullscreen mode Exit fullscreen mode
  • Parallelism hint (GBase 8a‑specific): 
  SELECT /*+ PARALLEL(4) */ COUNT(*) FROM large_table;
Enter fullscreen mode Exit fullscreen mode

Always verify with EXPLAIN that the hint is being respected.

Complete Tuning Flow

  1. EXPLAIN the slow query.
  2. If type = ALL: check indexes → if missing, create; if present, run ANALYZE TABLE → re‑evaluate.
  3. If JOIN order is wrong: update statistics → if still bad, apply STRAIGHT_JOIN hint.
  4. If statistics are fine but plan remains poor: use targeted hints, record the reason, and schedule a follow‑up review.

Operational Best Practices

  • Run ANALYZE TABLE immediately after bulk data loads.
  • Run ANALYZE TABLE immediately after adding partitions.
  • Schedule a weekly ANALYZE for core business tables.
  • When troubleshooting slow queries, always start with ANALYZE, then EXPLAIN.
  • After version upgrades, perform a full ANALYZE and re‑validate critical execution plans.

Keeping statistics fresh is the single most reliable way to keep your gbase database performing predictably. Make "ANALYZE after change" a habit, and you will avoid the vast majority of unexpected plan regressions.

Top comments (0)