# Example 649 — The index was rebuilt and the statistics were not `the_index_was_rebuilt_and_the_statistics_were_not.eml` - The index is rebuilt, unbloated and correct. How many queries use it is computed below. ## EML ```eml # Self-authored for the EML case corpus (no external origin). The index is # rebuilt, unbloated and correct. How many queries use it is computed below. # # The rebuild did what it promises. The tree was forty percent dead space from # six months of updates; it is now packed, its depth is one level shallower, and # every entry in it points at a live row. Nothing about the index is stale and # nothing about the rebuild was wasted. # # Whether a query USES an index is not decided by the index. It is decided by # the planner, from table statistics, and a rebuild does not collect statistics # — it rewrites the structure the statistics describe without touching the # description. # # The last statistics were collected before a bulk load. They say the table has # forty-one thousand rows. It has twelve million four hundred thousand, and at # forty-one thousand a sequential scan is the correct plan. 12400000 => rows_actual 41000 => rows_in_the_statistics 84000 => queries_per_hour 40 => dead_space_percent_before 0 => index_scans_chosen int(rows_actual / rows_in_the_statistics) => estimate_wrong_by rows_actual - rows_in_the_statistics => rows_the_planner_cannot_see "rows in the table : " + str(rows_actual) ^0 "rows in the statistics : " + str(rows_in_the_statistics) ^0 "rows the planner cannot see: " + str(rows_the_planner_cannot_see) ^0 "the estimate is out by : " + str(estimate_wrong_by) + " times" ^0 "" ^0 # ---- what the rebuild verified ---- "the rebuild's report" ^0 " dead space before, percent : " + str(dead_space_percent_before) ^0 " dead space after, percent : 0" ^0 " tree depth : one level shallower" ^0 " entries pointing at dead rows : 0" ^0 " corruption found : none" ^0 " verdict : REBUILT" ^0 "" ^0 " all true, and the bloat was real; deferring this is how" ^0 " an index stops fitting in memory" ^0 "" ^0 # ---- what chooses the plan ---- "planning one query" ^0 " index condition matches : yes" ^0 " index is usable : yes" ^0 " estimated rows to return : " + str(rows_in_the_statistics) + ", from the statistics" ^0 " at that estimate the cheaper plan is : a sequential scan" ^0 " index scans chosen this hour : " + str(index_scans_chosen) ^0 "" ^0 " the planner is not wrong; it is answering correctly from" ^0 " a number nobody refreshed" ^0 "" ^0 # ---- what it cost ---- # The rebuild is measured on the index and the index is not on the path. 940 => mean_ms_before_the_rebuild 940 => mean_ms_after_the_rebuild 3 => mean_ms_after_statistics_were_collected "mean query time, ms" ^0 " before the rebuild : " + str(mean_ms_before_the_rebuild) ^0 " after the rebuild : " + str(mean_ms_after_the_rebuild) ^0 " after collecting statistics : " + str(mean_ms_after_statistics_were_collected) ^0 "" ^0 mean_ms_before_the_rebuild - mean_ms_after_the_rebuild => improvement_from_the_rebuild int(mean_ms_before_the_rebuild / mean_ms_after_statistics_were_collected) => improvement_from_one_analyze " the rebuild moved it by, ms : " + str(improvement_from_the_rebuild) ^0 " one statistics collection moved it by : " + str(improvement_from_one_analyze) + " times" ^0 "" ^0 queries_per_hour * mean_ms_before_the_rebuild => ms_spent_per_hour int(ms_spent_per_hour / 1000) => seconds_spent_per_hour "queries per hour : " + str(queries_per_hour) ^0 "seconds of query time / hour: " + str(seconds_spent_per_hour) ^0 "" ^0 # ---- why the maintenance window looked successful ---- # The window's success criterion was the thing the window changed. Nobody # checked the plan, because the plan is not an index property. "the maintenance window" ^0 " planned : rebuild the index" ^0 " verified : the index is rebuilt" ^0 " measured : dead space, tree depth, entry validity" ^0 " not measured : whether any query reaches it" ^0 "" ^0 # ---- null control ---- # The same window, with statistics collected after the rebuild. rows_actual => nc_rows_in_the_statistics queries_per_hour => nc_index_scans_chosen "null control - the same rebuild, then collect statistics" ^0 " rows in the statistics : " + str(nc_rows_in_the_statistics) ^0 " index scans chosen /hour : " + str(nc_index_scans_chosen) ^0 " mean query time, ms : " + str(mean_ms_after_statistics_were_collected) ^0 " the index did not improve; the planner stopped being" ^0 " told the table was small" ^0 "" ^0 # ---- the rule ---- "what a rebuilt index guarantees" ^0 " this structure is compact and correct : exactly" ^0 " queries will use it : not addressed;" ^0 " use is a planner decision taken from statistics, and" ^0 " the rebuild rewrote what the statistics describe" ^0 " without rewriting the statistics" ^0 "" ^0 "maintenance that changes an object and a decision that reads" ^0 "a description of it are two different clocks; the object can" ^0 "be perfect and unreachable" ^0 "" ^0 "The index is rebuilt and the report is right: " + str(dead_space_percent_before) + " percent dead space gone," ^0 "a level shallower, 0 entries pointing at dead rows. The statistics still say" ^0 str(rows_in_the_statistics) + " rows against " + str(rows_actual) + " - an estimate out by " + str(estimate_wrong_by) + " times, hiding" ^0 str(rows_the_planner_cannot_see) + " rows - so " + str(index_scans_chosen) + " of " + str(queries_per_hour) + " queries an hour reach it, the rebuild" ^0 "moved the mean by " + str(improvement_from_the_rebuild) + " ms, and one statistics collection moves it by " + str(improvement_from_one_analyze) + " times." ^0 ``` ## Python (deterministic transpilation) ```python rows_actual = 12400000 rows_in_the_statistics = 41000 queries_per_hour = 84000 dead_space_percent_before = 40 index_scans_chosen = 0 estimate_wrong_by = int(rows_actual / rows_in_the_statistics) rows_the_planner_cannot_see = rows_actual - rows_in_the_statistics print("rows in the table : " + str(rows_actual)) print("rows in the statistics : " + str(rows_in_the_statistics)) print("rows the planner cannot see: " + str(rows_the_planner_cannot_see)) print("the estimate is out by : " + str(estimate_wrong_by) + " times") print("") print("the rebuild's report") print(" dead space before, percent : " + str(dead_space_percent_before)) print(" dead space after, percent : 0") print(" tree depth : one level shallower") print(" entries pointing at dead rows : 0") print(" corruption found : none") print(" verdict : REBUILT") print("") print(" all true, and the bloat was real; deferring this is how") print(" an index stops fitting in memory") print("") print("planning one query") print(" index condition matches : yes") print(" index is usable : yes") print(" estimated rows to return : " + str(rows_in_the_statistics) + ", from the statistics") print(" at that estimate the cheaper plan is : a sequential scan") print(" index scans chosen this hour : " + str(index_scans_chosen)) print("") print(" the planner is not wrong; it is answering correctly from") print(" a number nobody refreshed") print("") mean_ms_before_the_rebuild = 940 mean_ms_after_the_rebuild = 940 mean_ms_after_statistics_were_collected = 3 print("mean query time, ms") print(" before the rebuild : " + str(mean_ms_before_the_rebuild)) print(" after the rebuild : " + str(mean_ms_after_the_rebuild)) print(" after collecting statistics : " + str(mean_ms_after_statistics_were_collected)) print("") improvement_from_the_rebuild = mean_ms_before_the_rebuild - mean_ms_after_the_rebuild improvement_from_one_analyze = int(mean_ms_before_the_rebuild / mean_ms_after_statistics_were_collected) print(" the rebuild moved it by, ms : " + str(improvement_from_the_rebuild)) print(" one statistics collection moved it by : " + str(improvement_from_one_analyze) + " times") print("") ms_spent_per_hour = queries_per_hour * mean_ms_before_the_rebuild seconds_spent_per_hour = int(ms_spent_per_hour / 1000) print("queries per hour : " + str(queries_per_hour)) print("seconds of query time / hour: " + str(seconds_spent_per_hour)) print("") print("the maintenance window") print(" planned : rebuild the index") print(" verified : the index is rebuilt") print(" measured : dead space, tree depth, entry validity") print(" not measured : whether any query reaches it") print("") nc_rows_in_the_statistics = rows_actual nc_index_scans_chosen = queries_per_hour print("null control - the same rebuild, then collect statistics") print(" rows in the statistics : " + str(nc_rows_in_the_statistics)) print(" index scans chosen /hour : " + str(nc_index_scans_chosen)) print(" mean query time, ms : " + str(mean_ms_after_statistics_were_collected)) print(" the index did not improve; the planner stopped being") print(" told the table was small") print("") print("what a rebuilt index guarantees") print(" this structure is compact and correct : exactly") print(" queries will use it : not addressed;") print(" use is a planner decision taken from statistics, and") print(" the rebuild rewrote what the statistics describe") print(" without rewriting the statistics") print("") print("maintenance that changes an object and a decision that reads") print("a description of it are two different clocks; the object can") print("be perfect and unreachable") print("") print("The index is rebuilt and the report is right: " + str(dead_space_percent_before) + " percent dead space gone,") print("a level shallower, 0 entries pointing at dead rows. The statistics still say") print(str(rows_in_the_statistics) + " rows against " + str(rows_actual) + " - an estimate out by " + str(estimate_wrong_by) + " times, hiding") print(str(rows_the_planner_cannot_see) + " rows - so " + str(index_scans_chosen) + " of " + str(queries_per_hour) + " queries an hour reach it, the rebuild") print("moved the mean by " + str(improvement_from_the_rebuild) + " ms, and one statistics collection moves it by " + str(improvement_from_one_analyze) + " times.") ``` ## stdout (executed) ```text rows in the table : 12400000 rows in the statistics : 41000 rows the planner cannot see: 12359000 the estimate is out by : 302 times the rebuild's report dead space before, percent : 40 dead space after, percent : 0 tree depth : one level shallower entries pointing at dead rows : 0 corruption found : none verdict : REBUILT all true, and the bloat was real; deferring this is how an index stops fitting in memory planning one query index condition matches : yes index is usable : yes estimated rows to return : 41000, from the statistics at that estimate the cheaper plan is : a sequential scan index scans chosen this hour : 0 the planner is not wrong; it is answering correctly from a number nobody refreshed mean query time, ms before the rebuild : 940 after the rebuild : 940 after collecting statistics : 3 the rebuild moved it by, ms : 0 one statistics collection moved it by : 313 times queries per hour : 84000 seconds of query time / hour: 78960 the maintenance window planned : rebuild the index verified : the index is rebuilt measured : dead space, tree depth, entry validity not measured : whether any query reaches it null control - the same rebuild, then collect statistics rows in the statistics : 12400000 index scans chosen /hour : 84000 mean query time, ms : 3 the index did not improve; the planner stopped being told the table was small what a rebuilt index guarantees this structure is compact and correct : exactly queries will use it : not addressed; use is a planner decision taken from statistics, and the rebuild rewrote what the statistics describe without rewriting the statistics maintenance that changes an object and a decision that reads a description of it are two different clocks; the object can be perfect and unreachable The index is rebuilt and the report is right: 40 percent dead space gone, a level shallower, 0 entries pointing at dead rows. The statistics still say 41000 rows against 12400000 - an estimate out by 302 times, hiding 12359000 rows - so 0 of 84000 queries an hour reach it, the rebuild moved the mean by 0 ms, and one statistics collection moves it by 313 times. ``` ## Round-trip `ok: true` — round-trip fixpoint reached (python1 == python2) ## Trace event types eml:run:start · eml:assign · eml:output · eml:run:done