A slow query doesn't always mean the SQL is badly written.
In many production incidents, the first question should be much simpler:
What execution plan did the optimizer actually choose?
That's why EXPLAIN is one of the first tools DBAs reach for when troubleshooting database performance. It shows the path the optimizer selected—not the path you assumed it would take.
This troubleshooting approach also applies to GBase Database, where execution plans, statistics, indexes, and SQL optimization form a practical performance-tuning chain.
1. Start With EXPLAIN
Different database systems use slightly different syntax, but the core information is similar.
An execution plan can help you answer questions such as:
- Is an index being used?
- Is the database performing a full table scan?
- How many rows does the optimizer expect to process?
- What is the estimated cost?
- Which operation is consuming the most work?
For example:
-- MySQL
EXPLAIN
SELECT *
FROM orders
WHERE order_date > '2026-01-01';
PostgreSQL provides additional runtime information:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE order_date > '2026-01-01';
If the plan shows a sequential scan or full table scan where you expected an index scan, that's an important clue.
But don't immediately create another index.
The execution plan is the starting point, not the final answer.
2. Follow the Troubleshooting Chain
A reliable slow-query investigation usually follows this order:
Statistics → Execution Plan → Index Design → SQL
Step 1: Refresh Statistics
The optimizer depends on statistics to estimate row counts and data distribution.
After large INSERT, UPDATE, or DELETE operations, those statistics may no longer represent the actual data.
That can cause the optimizer to choose an inefficient plan—even when a suitable index already exists.
For example:
ANALYZE TABLE orders;
The exact command varies by database, but the principle is the same:
Make sure the optimizer is working with current information before changing the SQL.
For GBase Database(GBase 8s), statistics maintenance is an important part of query-performance troubleshooting.
Step 2: Read the Execution Plan
Once statistics are current, inspect the execution plan again.
Look for the access path:
- Full table scan
- Index scan
- Index range scan
- Join method
- Estimated rows
- Estimated cost
The key question isn't simply "Does this query have an index?"
It's:
Did the optimizer choose the right access path for this workload?
An index can exist and still not be used. That doesn't automatically mean the optimizer is wrong. If a query returns a large percentage of a table, a full scan may genuinely be cheaper than repeatedly accessing an index and the underlying rows.
Step 3: Check Index Design
If the plan still looks inefficient, inspect the indexes.
Start with the actual query pattern.
For example:
SELECT *
FROM orders
WHERE customer_id = 1001
AND order_date >= '2026-01-01'
ORDER BY order_date;
Ask:
- Are the filtering columns indexed?
- Is the composite index ordered appropriately?
- Does the index support the query's filtering and sorting pattern?
- Is the query selective enough for an index to provide a benefit?
This is also why more indexes aren't always better.
Every additional index introduces storage, write, and maintenance costs. On high-write tables, excessive indexing can reduce INSERT and UPDATE performance.
Index design should follow workload patterns—not the other way around.
Step 4: Optimize the SQL
Only after statistics, execution plans, and indexes have been checked should you rewrite the SQL.
At this stage, look for issues such as:
- Unnecessary columns in
SELECT - Functions applied to indexed columns
- Inefficient joins
- Missing predicates
- Unnecessary sorting
- Repeated subqueries
SQL optimization is important, but it shouldn't be the automatic first response to every slow query.
3. A Typical Production Case
Imagine a query that normally runs in around 5 seconds.
After a major data load, it suddenly takes 90 seconds.
The initial reaction might be:
"The SQL needs to be rewritten."
But the execution plan tells a different story.
Before the slowdown:
INDEX PATH
Estimated rows: 12,000
After the slowdown:
SEQUENTIAL SCAN
Estimated rows: 2,500,000
The SQL hasn't changed.
The data has changed, and the optimizer's statistics may no longer accurately describe the table.
After refreshing statistics, the optimizer can reassess the available access paths. If the index becomes the better choice again, the query can return to its previous performance without changing the SQL itself.
That's an important lesson:
A slow query is not always a SQL problem. Sometimes it's an optimizer-information problem.
4. What About GBase Database?
The same troubleshooting logic applies to GBase Database.
For GBase Database(GBase 8s), the workflow can be summarized as:
Refresh statistics → inspect execution plan → verify index usage → check index design → optimize SQL
For GBase Database(GBase 8c), the same principle applies: understand what the optimizer sees and which execution path it chooses before making changes.
The exact commands and plan output differ between database products, but the diagnostic mindset remains the same.
This is particularly useful when migrating workloads to GBase Database. Instead of assuming that a query is slow because the SQL needs to change, compare the execution plans and statistics first.
5. Common Mistakes
Mistake 1: Adding an Index Immediately
A missing index is only one possible cause.
First determine why the optimizer chose the current plan.
Mistake 2: Using Hints Too Early
Forcing an index can make one query faster today while creating a maintenance problem tomorrow.
If statistics are stale, fix the statistics first.
Mistake 3: Assuming Every Full Scan Is Bad
A full scan isn't automatically a performance failure.
If a query needs a large percentage of the table, scanning the table may be the most efficient strategy.
Mistake 4: Rewriting SQL Before Checking the Plan
Without seeing the execution plan, SQL rewriting is often guesswork.
The plan gives you evidence.
6. The Practical Rule
When a query suddenly becomes slow, don't start by changing everything.
Use a disciplined chain:
1. Refresh statistics
↓
2. Read EXPLAIN
↓
3. Check the access path
↓
4. Review index design
↓
5. Optimize SQL
↓
6. Consider hardware only if necessary
This approach makes database performance troubleshooting more systematic and less dependent on trial and error.
For GBase Database, the same rule applies:
Statistics first. Execution plan second. Index design third. SQL optimization last.
Once you learn to read the execution plan, a slow query becomes less of a mystery and more of a diagnosis.
