Answers · Where the data lives
Will refreshing statistics fix a bad query plan?
In shortOnly if the summary was taken before the data changed; otherwise refreshing re-measures the same assumptions and the plan stays wrong.
Only if the summary was taken before the data changed. A refresh re-measures row counts, frequent values, histograms and distinct counts; it does not change the planner's assumption that two columns are independent, nor does it make a function over a column comparable to the column's histogram.
What a refresh changes
Take a table with 1,000,000 rows. The summary says 10,000 rows because it was gathered before a load. A filter matches 1 per cent of rows. The stale estimate is 10,000 times 0.01, which is 100. The actual count is 1,000,000 times 0.01, which is 10,000. The plan is out by a factor of 100, and the cause is exactly the one a refresh repairs. This is common after a load, after growth by an order of magnitude, or after a new value appears that the frequent-values list has never seen.
The refresh rewrites the row count to 1,000,000. The selectivity is still 0.01, so the new estimate is 1,000,000 times 0.01, which is 10,000. That matches the measured count. If the summary was gathered after the data changed, the same refresh produces 1,000,000 rows again and the estimate stays 10,000. It cannot repair a different cause by being run again. The remedy is cheap and correct here, but only here.
The natural guess fails because a refresh changes the numbers the planner has, not the rule it applies to them. Check when the summary was gathered before choosing this remedy. If it was gathered an hour ago and a filtered scan is still out by a factor of 100, staleness is not the cause; another refresh will produce the same number.
The estimate that survives a refresh
Two filters on related columns show the limit. A table has 1,000,000 rows. Country matches 1 per cent and currency matches 1 per cent. The planner multiplies the shares because it assumes independence: 0.01 times 0.01 is 0.0001, and 1,000,000 times 0.0001 is 100. If every country row uses that currency, the true count is 1 per cent of 1,000,000, which is 10,000.
A refresh re-measures country at 1 per cent and currency at 1 per cent. It does not measure the pair, so the independence assumption remains and the estimate stays 100. Declaring the two columns as a related group gives the planner the joint frequency: 0.01 times 1,000,000 is 10,000. That is the remedy for this cause, and it is re-measured on every statistics run.
A function on the filtered column fails for a different reason. The summary describes created_at, not the month extracted from it. With 1,000,000 rows and a default share of 0.005, the planner estimates 5,000 rows. If January actually holds 30,000 rows, the true share is 0.03 and the estimate is out by a factor of six. Refreshing created_at leaves the function in place, so the planner still has no histogram for the function result and still estimates 5,000. Rewriting the predicate as a range on created_at lets the histogram answer: 1,000,000 times 0.03 is 30,000.
| Cause | Estimate before | Estimate after refresh | Remedy that addresses it |
|---|---|---|---|
| Summary older than the data, 1 per cent filter | 100 | 10,000 | Refresh the statistics |
| Two related filters, each 1 per cent | 100 | 100 | Declare the related columns |
| Function on the filtered column, true share 0.03 | 5,000 | 5,000 | Rewrite as a range on the column |
When to overrule instead
Forcing a method or pinning a plan is the last remedy because it stops the planner re-deciding. A plan that changes between identical runs with no change in the data is the one clear case for pinning: the search is unstable, and no extra information removes the variance. The other case is an estimate that remains wrong after every remedy to its left has been tried. Leave a note with what was observed, when, and the row counts at the time. A forced plan is not wrong today; it becomes wrong quietly as row counts move.
The refresh stops being eligible once the summary is newer than the change you suspect. It also stops being sufficient when the cause is correlation, an obstructing function or parameter, or an unstable search, because in those cases the information or the decision is the problem rather than its age. Match the remedy to the located cause: refresh for stale numbers, declare or refine for missing information, rewrite for an obstruction, split or pin only when the estimate cannot be restored. A forced plan with no note is indistinguishable from a guess.
Worth keeping
- A refresh repairs only an estimate based on a summary taken before the data changed.
- A wrong estimate from related columns survives a refresh because the independence assumption is unchanged.
- A forced plan should be the last resort and should record the observations that justified it.
Where this goes next
This question is one step of What the Planner Decides, a course that works through its subject from the beginning. The page that teaches this part of it is Making It Choose Better, and it reads the whole way through whether or not you have an account.
What an account changes is the practice: problems standing under every idea, marked with a reason rather than a tick, in a copy of the course that is yours.
Read that pageThe whole courseOther questions people ask about this
- How does the database estimate how many rows a WHERE clause will return?It does not scan the table: it uses stored summary statistics to turn each filter into a fraction of rows and multiplies the fractions.
- If SQL is declarative, who decides how the join actually gets run?The database's query planner chooses the join method and order; your SQL only says which rows the answer must contain.
- Why does the planner underestimate rows when I filter on two columns that are related?It multiplies the two filter shares as if the columns were unrelated, so when one column already implies the other the estimate comes out far too low.
- Why does the order I join tables in matter if joins are commutative?The final result is the same, but the rows carried between joins are not, and that is what the work is paid on.
- Why is the date off by one day after I save it and read it back?You saved a calendar date as if it were a precise moment, so showing it in another time zone can move it to the day before or after.
- Is UTF-8 the same thing as Unicode, or what's the difference?No: Unicode assigns a number to each character, while UTF-8 is a way of writing those numbers as bytes, using one to four bytes per character.