Database

Christophe Pettus: Come conta l'altra metà

08 giugno 2026

Sintesi redazionale: Il pianificatore di query di PostgreSQL raccoglie le statistiche in un modo; Oracle, Db2, MySQL, SQLite, DuckDB e Snowflake hanno invece adottato approcci diversi.. Fonte originale: https://postgr.es/p/9lx

PostgreSQL has `ANALYZE`. You run it (or `autovacuum`runs it for you), it draws a sample of `300 × default_statistics_target`rows, and it writes a row per column into `pg_statistic`: a null fraction, an n-distinct estimate, a most-common-values list, an equi-depth histogram, and a physical-vs-logical correlation. The planner reads those numbers, multiplies selectivities together, costs a handful of join strategies, and picks one. Three join algorithms are on the menu: nested loop, merge join, hash join.That is the entire shape of the problem, and every cost-based optimizer ever shipped solves the same one. They differ in three places, and only three: where the numbers come from, how stale the numbers are allowed to get, and which plan shapes are even legal to choose between. The algorithms are the boring part. Everybody hash-joins. The interesting part is the bookkeeping.So: PostgreSQL has `ANALYZE`. What does everyone else have? Six answers, arranged from the system with the most knobs to the system with none.## A note on the menuBefore the statistics, a word about what they’re feeding, because “hash join vs. merge join” quietly assumes the database has both. Three of the six here don’t, and one of them doesn’t have either.A row-store with B-tree indexes and OLTP ancestry tends to carry the full classical set: nested loop for small driving sets with a good index on the inner side, merge join for two already-sorted inputs, hash join for large unsorted equijoins. Oracle and Db2 are this. A system born for analytics, scanning columns in vectorized batches, finds merge join almost useless; sorting both sides to join them is a tax you pay only when the optimizer is cornered. The vectorized hash join wins nearly every analytical equijoin, so that’s what gets built and tuned. DuckDB and Snowflake are this. MySQL is its own case: it shipped for two decades with exactly one join algorithm and only gained a second in 2019. And SQLite is the limit of the argument; it has one join algorithm, nested loop, and has never had any other. There is no hash join and no merge join to weigh it against. The planner’s entire job is to pick the order of the loops.Keep that in mind as the statistics get more elaborate. Half the precision exists to choose between options the engine may not even have, and at least one engine here spends its statistics on a decision with only one possible operator and several possible orderings.## Oracle: every feature anyone ever asked forOracle Database is the maximalist. If a statistics idea has appeared in a paper since 1995, Oracle has shipped a version of it, kept the old version for compatibility, and added a preference to control which one runs.The gatherer is `DBMS_STATS`, a PL/SQL package, not a SQL statement. By default you don’t call it; an AutoTask job runs during the nightly maintenance window and re-gathers anything missing or stale, where “stale” means roughly 10% of rows changed since the last gather. Oracle 19c added **high-frequency automatic statistics collection**, a lightweight task that revisits stale objects every 15 minutes by default, so the window job isn’t the only line of defense. For bulk operations, **online statistics gathering** (12c) piggybacks a stats collection onto `CREATE TABLE AS SELECT`and direct-path inserts; you were scanning every row anyway, so you may as well count them on the way past.The column statistics are where Oracle shows off. Histograms come in four flavors, and the optimizer picks which to build based on the data: **frequency** (one bucket per distinct value, when there are few enough), **top-frequency** (frequency for the popular values, the long tail ignored), **height-balanced** (equi-depth, the legacy type), and **hybrid** (height-balanced buckets that also track the frequency of the value at each bucket boundary, which is the type you actually want and the reason the other two mostly stopped mattering after 12c). N-distinct is computed with a **HyperLogLog** sketch (`APPROXIMATE_NDV_ALGORITHM`, defaulting to `HYPERLOGLOG`since 19c) rather than by sorting and counting, which is the difference between a stats gather that finishes and one that doesn’t. PostgreSQL people will recognize the move; `pg_stat_statements`and friends reach for the same sketch family for the same reason.Then there is the correlation problem, which Oracle attacks from two directions. **Extended statistics** let you declare a column group (`DBMS_STATS.CREATE_EXTENDED_STATS`on `(make, model)`) so the optimizer stops assuming `model = 'Accord'`and `make = 'Honda'`are independent events; this is the direct analog of PostgreSQL’s `CREATE STATISTICS`. Expression statistics do the same for `UPPER(last_name)`and similar derived values.The genuinely different machinery is adaptive. **SQL plan directives** are notes the optimizer leaves itself: “last time I planned a query with this predicate shape, my cardinality estimate was garbage.” The directive outlives the individual statement and triggers dynamic sampling on the next query that matches. **Adaptive plans** go further and defer the decision to runtime. The optimizer ships a plan with a nested-loop join and a hash join both wired up, buffers the first rows out of the driving side through a statistics collector, and if the actual row count crosses a precomputed inflection point, it switches to the hash join mid-execution. The estimate was wrong; the plan corrects itself anyway.If that sounds like it could destabilize a plan you’d spent months getting right, Oracle agrees with you. `OPTIMIZER_ADAPTIVE_STATISTICS`defaults to `FALSE`, because the adaptive-statistics features as originally shipped in 12c caused enough plan churn that Oracle turned the aggressive parts off by default and told everyone to leave them off. The maximalist learned the hard way that more statistics is not the same as better plans. Worth remembering before you reach for the equivalent knobs anywhere else.One footnote, because the marketing obscures it: **real-time statistics**, the 19c feature that gathers stats during ordinary DML instead of waiting for the next gather, is restricted to Engineered Systems. Exadata only. On a normal Oracle install it does not run.## Db2: the optimizer that makes up numbersIBM Db2 (the LUW line, the one a PostgreSQL person would compare against) has the explicit gatherer you’d expect, `RUNSTATS`, invoked per table:1 RUNSTATS ON TABLE db2inst1.sales WITH DISTRIBUTION AND DETAILED INDEXES ALL`WITH DISTRIBUTION`is the part that matters. Without it you get cardinalities and index stats; with it you get **distribution statistics**, which are frequency values for the common entries plus quantiles for the spread. Same two-pronged idea as PostgreSQL’s MCV-list-plus-histogram split, different vocabulary.Automatic collection is two separate mechanisms, and the distinction is the interesting bit. `AUTO_RUNSTATS`is the asynchronous one: a background process notices a table has drifted and schedules a `RUNSTATS`later, the way `autovacuum`schedules an `autoanalyze`. `AUTO_STMT_STATS`is the synchronous one, and it has no PostgreSQL equivalent. With **real-time statistics** enabled (it’s on by default for new databases), the optimizer can collect statistics *at the moment it compiles your query*. You submit SQL against a table whose stats are stale; rather than plan blind, Db2 pauses, samples the table right then, and plans on fresh numbers. There is a time budget so this doesn’t turn every first-run query into a maintenance job, but the mechanism is real and it fires constantly. The monitoring counters say so out loud: a busy database will report tens of thousands of “Synchronous runstats” events.And when even a quick synchronous sample is too expensive, Db2 will **fabricate** statistics. It reads cheap metadata it already has (the number of pages the table occupies, the row width) and manufactures a cardinality estimate from that alone. The “Statistic fabrications” counter sits right next to the runstats counters in the snapshot. This is a database that would rather invent a number than plan with no number, and the design instinct behind it is sound: a fabricated estimate from page count beats the catalog’s belief that the table still has the 1,000 rows it had at creation.Correlation gets the same two answers Oracle gives. **Column group statistics** (`RUNSTATS ON COLUMNS ((c1, c2))`) capture combined cardinality for correlated columns. **Statistical views** are the more unusual tool: you define a view over a join or a filtered subset, register it, run `RUNSTATS`against it, and the optimizer uses the view’s statistics to estimate the cardinality of *other* queries whose shape resembles the view, including cross-table relationships a single-table stat can’t express. It’s a way of teaching the optimizer about a correlation that lives in a join rather than in a table.The join menu is the full classical three: `NLJOIN`, `MSJOIN`(merge scan join), and `HSJOIN`. And Db2 hands you an explicit dial on how hard to look for the best combination of them. `CURRENT QUERY OPTIMIZATION`runs from 0 to 9; the default of 5 uses a greedy join enumeration that’s good enough for most workloads, and 9 unleashes a near-exhaustive search you reserve for genuinely complex queries where the planning time is worth it. PostgreSQL has the analogous tradeoff buried in `geqo_threshold`, the point at which it abandons exhaustive search for a genetic algorithm, but Db2 makes the whole spectrum a session setting you’re expected to reach for.## MySQL: the minimalist, dragged forwardMySQL is the instructive opposite of Oracle, and the comparison flatters PostgreSQL more than anything else in this list.Start with the join menu, because it explains everything else. For most of its history MySQL had exactly one join algorithm: nested loop, with a block-nested-loop variant to cut the inner-table rescans. No merge join, ever. No hash join until **8.0.18** in 2019, which then took over the block-nested-loop role outright by **8.0.20**. So the question this whole article is built around, “hash join vs. merge join,” is one MySQL literally could not ask until recently and still can’t fully ask today; there is no merge join to weigh the hash join against. The optimizer’s job has always been narrower: pick an access method per table, pick a join order, and (now) decide whether a join with no usable index runs as a hash join. `optimizer_search_depth`governs how exhaustively it explores the order, defaulting to 62.The statistics split in two, along the index boundary. **InnoDB persistent statistics** handle indexed access. They’ve been persistent and on by default since 5.6.6 (before that, MySQL re-estimated index cardinality from a handful of random dives on every server restart, which is exactly as unstable as it sounds). The engine samples index pages (`innodb_stats_persistent_sample_pages`, default 20), stores per-index cardinality in `mysql.innodb_table_stats`and `mysql.innodb_index_stats`, and re-gathers automatically when about 10% of the table has changed (`innodb_stats_auto_recalc`). Twenty pages is not many. The estimates are coarse, and on a large table they can be coarse enough to flip a plan.Column **histograms** are the bolted-on second half, added in 8.0 and showing it. You build them by hand:1 ANALYZE TABLE sales UPDATE HISTOGRAM ON region, channel WITH 32 BUCKETS;You get a choice of singleton (one bucket per value) or equi-height buckets, up to 1,024 of them, stored in the data dictionary and exposed through `INFORMATION_SCHEMA.COLUMN_STATISTICS`. Two catches, and they’re both significant. First, histograms are not maintained automatically; you re-run `ANALYZE TABLE`_(testo troncato)_