Propagate stadistinct through GROUP BY/DISTINCT in subqueries and CTEs Previously, examine_simple_variable() would return early when a subquery or CTE used GROUP BY or DISTINCT. It could detect uniqueness for single-column cases, but for multi-column GROUP BY or DISTINCT, selectivity estimation fell back on 1/DEFAULT_NUM_DISTINCT (1/200). This produced wildly inaccurate estimates for filters and joins on such columns, often leading the planner to choose nested loop joins where hash joins would be far better. This was a significant factor in poor TPC-DS benchmark performance.
For DISTINCT or GROUP BY key columns that are simple Vars, we now recurse into the subquery to obtain the base table's stadistinct, which remains valid after grouping (the set of distinct values is preserved). However, MCV frequencies, histograms, and correlation data are not valid because GROUP BY and DISTINCT change the frequency distribution of key columns. So we strip all stats slots from the copied stats tuple, causing callers like var_eq_const() to use the 1/ndistinct estimate instead. If stadistinct is stored as a negative value (a fraction of the base table's row count), we convert it to an absolute count so it is not misinterpreted relative to the subquery's output row count. stanullfrac is adjusted too, since grouping collapses NULLs. For a single grouping key, at most one NULL group survives, so the null fraction is 1/(ndistinct+1). For multiple grouping keys the null fraction depends on the joint distribution of the keys, which we don't have, so we approximate it as zero; NULLs collapse far more aggressively than non-NULLs, so the real fraction is well below the base table's, and erring low keeps estimates on the hash-join-favoring side. Non-key columns (e.g., aggregate outputs) continue to get no stats, same as before. Author: Richard Guo <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Discussion: https://postgr.es/m/CAMbWs49rWYrecgreDhKsfx3VSDW=qo35s+iAmgGu=wparrm...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/906b1e4a19a59612deb144ff6018cb048f7d73f8 Modified Files -------------- src/backend/utils/adt/selfuncs.c | 122 ++++++++++++++++++++++++++++++++----- src/test/regress/expected/with.out | 94 ++++++++++++++++++++++++++++ src/test/regress/sql/with.sql | 35 +++++++++++ 3 files changed, 237 insertions(+), 14 deletions(-)
