Query Mysteriously Slow? Refresh Statistics Before You Blame the SQL

Your SQL hasn't changed. The schema hasn't changed. The data volume hasn't exploded.

Yet yesterday's query took 3 ms, and today it takes 30 seconds.

Before rewriting the SQL, adding another index, or blaming the database engine, check one thing first: database statistics.

In many databases, including GBase Database, the query optimizer depends heavily on statistics to estimate row counts, data distribution, and execution costs. When those statistics become stale, the optimizer can make a very different decision from the one you expected.

The SQL didn't change.

The optimizer's view of the data did.

Why Stale Statistics Make Queries Slow

A query optimizer does not normally examine every row before choosing an execution plan. Instead, it uses database statistics to estimate questions such as:

• How many rows are in this table?
• How selective is this condition?
• How many rows will an index return?
• Is an index scan cheaper than a sequential scan?
• Which table should be joined first?
• What join strategy should be used?

Consider a simple query:

SELECT *
FROM orders
WHERE order_date > '2026-01-01';

If the optimizer believes that only 100 rows match the condition, an index path may look attractive.

But suppose a large data import happened yesterday and the actual number is now 10 million rows.

If the statistics still describe the old distribution, the optimizer may underestimate the result set and select an inefficient execution plan.

This is one of the most common causes of the frustrating situation:

"The SQL is unchanged, but performance suddenly collapsed."

GBase Database and Statistics

For GBase Database, statistics maintenance should be part of normal database operations rather than something you only perform after a performance incident.

For example, in GBase Database(GBase 8s), administrators can use the appropriate UPDATE STATISTICS operation to refresh optimizer statistics.

A typical example is:

UPDATE STATISTICS HIGH FOR TABLE orders;

The exact statistics level and maintenance strategy should depend on the table size, workload, and data distribution.

The important point is not the command itself.

The important point is that the optimizer needs current information about your data.

Step 1: Refresh Statistics

When you encounter an unexplained SQL performance regression, statistics refresh is one of the fastest things to test.

For example:

UPDATE STATISTICS HIGH FOR TABLE orders;

Other database systems use different commands. For example, depending on the database and version, you may encounter commands such as:

ANALYZE TABLE orders;

or:

ANALYZE orders;

Do not assume that statistics commands are interchangeable across database systems.

When working with GBase Database, follow the statistics maintenance syntax and recommendations for the specific GBase Database product and version you are running.

Step 2: Compare the Execution Plan

Refreshing statistics is only half of the investigation.

The next step is to check the execution plan again.

EXPLAIN
SELECT *
FROM orders
WHERE order_date > '2026-01-01';

Look for changes such as:

• Sequential scan → index scan
• Unexpected join order → more efficient join order
• Nested loop → hash join or another appropriate strategy
• Estimated rows far from actual rows
• Unexpectedly high estimated cost

The key question is:

Did the optimizer's estimate become more realistic after statistics were refreshed?

This is much more useful than simply asking whether an index exists.

When Should You Refresh Statistics?

Statistics become increasingly valuable as the underlying data changes.

Pay particular attention after:

1. Heavy INSERT Operations

A large batch import can change the table's size and data distribution significantly.

If statistics still describe the old data, query optimization can suffer.

2. Large DELETE Operations

Deleting a significant percentage of a table can make existing statistics less representative of the current dataset.

3. Bulk Data Loading

ETL and batch-processing jobs can introduce millions of new rows in a short period.

This is a classic trigger for statistics maintenance.

4. Major Data Distribution Changes

Suppose an orders table normally contains data evenly distributed across regions.

Then a new business campaign causes 70% of new orders to come from one region.

The column's distribution has changed even if the table structure has not.

The optimizer needs statistics that reflect the new reality.

5. New Indexes or Schema Changes

After creating a new index, make sure the optimizer has the information it needs to evaluate that access path.

Creating an index does not automatically guarantee that the optimizer will choose it.

Don't Confuse a Bad Plan With a Missing Index

A common database tuning mistake is:

"The query is slow. Let's create an index."

Sometimes that works.

Sometimes the index already exists.

The real problem may be that the optimizer incorrectly estimates how many rows the query will return.

That distinction matters.

A useful troubleshooting sequence is:

Slow query
    ↓
Check execution plan
    ↓
Check estimated vs. actual rows
    ↓
Check statistics freshness
    ↓
Refresh statistics if necessary
    ↓
Run EXPLAIN again
    ↓
Only then consider SQL/index changes

This approach is especially useful when troubleshooting GBase Database performance because it prevents unnecessary schema changes before understanding what the optimizer is actually doing.

A Practical Statistics Maintenance Strategy

Instead of waiting for users to report a slow query, make statistics maintenance part of your operational routine.

A practical approach is:

Daily: Monitor high-value or high-frequency SQL.

After bulk data changes: Refresh statistics on affected tables.

After major ETL jobs: Check whether data distribution has changed significantly.

After index creation: Verify the execution plan.

During performance incidents: Compare optimizer estimates with actual execution behavior.

For large production tables, avoid blindly refreshing every table at the same frequency. Statistics maintenance should match how quickly the underlying data changes.

The DBA's Rule of Thumb

When a query suddenly becomes slow without an obvious SQL or schema change, don't immediately rewrite the query.

Ask three questions first:

1. Did the data change significantly?
2. Are the optimizer statistics still representative?
3. Did the execution plan change?

If the answer to the first question is yes, stale statistics should be high on your investigation list.

For GBase Database, database statistics are not just administrative housekeeping. They are part of the information the optimizer uses to make SQL execution decisions.

Final Takeaway

A query going from 3 ms to 30 seconds feels like a major database failure.

Sometimes, however, the database hasn't failed at all.

The optimizer simply made a bad decision based on outdated information.

Before adding indexes, rewriting SQL, or changing your database architecture, refresh the statistics and inspect the execution plan.

For GBase Database performance tuning, the simplest troubleshooting habit is often one of the most valuable:

Keep statistics representative of the data, and always verify the execution plan when performance changes unexpectedly.

Your SQL may be innocent.

The optimizer may just be working with yesterday's map.