TIL: Stale Statistics Can Make the Optimizer Pick the Wrong Plan

Today I learned a counterintuitive database performance lesson:

Your SQL can stay exactly the same while its execution plan changes dramatically.

The reason is often stale statistics.

Why Statistics Matter

A database optimizer doesn't simply read your SQL and choose an index.

It estimates the cost of different execution paths using statistics such as:

• Row counts
• Data distribution
• Selectivity
• Index information

When statistics become outdated after significant data changes, the optimizer may make the wrong assumption about the workload.

For example, it might estimate that a table is relatively small and choose a full table scan—even though the table now contains millions of rows and an index would be much more efficient.

The SQL didn't change.

The schema didn't change.

But the information available to the optimizer did.

That's enough to produce a very different execution plan.

A Simple Example

Imagine this query:

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

When the table is small, a sequential scan may be perfectly reasonable.

After millions of rows are added, however, the same query may benefit from an index on order_date.

If the optimizer still relies on outdated statistics, it may not recognize the change.

The result?

Same SQL. Different plan. Much slower query.

How to Refresh Statistics

The exact syntax depends on the database system.

For MySQL:

ANALYZE TABLE orders;

For PostgreSQL:

ANALYZE orders;

For GBase Database(GBase 8s), you can refresh statistics with:

UPDATE STATISTICS HIGH FOR TABLE orders (order_date);

The important point isn't the command itself.

It's the workflow:

Data changes → statistics become stale → optimizer makes worse estimates → execution plan changes → query slows down.

When Should You Refresh Statistics?

Statistics deserve attention when:

• A table has experienced heavy INSERT, UPDATE, or DELETE activity
• Data distribution has changed significantly
• A new index has been created
• Query performance suddenly changes without an obvious SQL change
• The execution plan looks inconsistent with the current data

You don't need to blindly refresh statistics after every statement.

Instead, treat statistics as part of routine database maintenance and investigate them when workload or data distribution changes significantly.

The GBase Database Takeaway

For GBase Database, statistics are an important part of the query-optimization workflow.

When a query suddenly becomes slow, don't immediately rewrite the SQL or add another index.

Start with three questions:

1. Are the statistics current?
2. What execution plan did the optimizer choose?
3. Does that plan match the current data and query pattern?

Only after answering those questions should you move on to index or SQL tuning.

That's why refreshing statistics is often one of the highest-ROI steps in database performance troubleshooting.

Sometimes the fastest way to fix slow SQL isn't to change the SQL at all.

Give the optimizer better information first.