0Pricing
PostgreSQL Performance & Query Optimization · درس

تصحيحات MCV وN-Distinct

استخدم إحصاءات ndistinct والقيم الأكثر شيوعًا لإصلاح تقديرات الربط والتجميع.

تصحيحات MCV وN-Distinct درس مجاني في PostgreSQL Performance & Query Optimization على CoddyKit. هذا هو الدرس 3 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في PostgreSQL Performance & Query Optimization، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة PostgreSQL Performance & Query Optimization 4 دروس في المجموع.

بعض أجزاء هذا الدرس لم تُترجم بعد وتظهر باللغة الإنجليزية.

Why Estimates Drift

The PostgreSQL planner chooses join orders, join methods, and grouping strategies from row-count estimates. When those estimates are wrong, you get nested loops over millions of rows or a hash table sized for the wrong cardinality.

Two column-level statistics drive most of these estimates:

  • n_distinct — how many distinct values the planner believes a column holds. It feeds grouping and join cardinality.
  • most_common_vals (MCV) — the list of frequent values and their frequencies, used for selectivity of equality predicates.

This lesson shows how to read, diagnose, and correct both when the default sampling gets them wrong.

Reading pg_stats

Everything the planner knows about a column lives in the pg_stats view, a human-readable wrapper over pg_statistic. Start every diagnosis here.

Key columns: n_distinct, most_common_vals, most_common_freqs, and null_frac.

SELECT attname,
       n_distinct,
       null_frac,
       most_common_vals,
       most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'orders'
  AND attname IN ('customer_id', 'status');

How n_distinct Is Encoded

The n_distinct value is overloaded with two meanings:

  • A positive number is an absolute count of distinct values (e.g. 4200).
  • A negative number between -1 and 0 is a ratio of distinct values to total rows. -1 means every row is unique; -0.5 means distinct count is half the row count.

Negative form is chosen by ANALYZE when the distinct count appears to grow with the table, so it scales as the table grows. This distinction matters when you override it manually.

The Sampling Problem

ANALYZE estimates n_distinct from a random sample (default ~300 × default_statistics_target rows), not a full scan. Estimating the number of distinct values from a sample is notoriously hard.

The classic failure: a high-cardinality column where distinct values are spread thinly. The sample sees few repeats, so the estimator under-counts badly. A column with 5 million real distinct values might be recorded as 50,000.

The planner then thinks a GROUP BY produces 50,000 groups, picks a hash aggregate sized for that, and spills to disk when reality hits 5 million.

Spotting a Bad n_distinct

Compare what the planner believes against ground truth. Run an exact distinct count and hold it next to pg_stats:

If n_distinct is stored as a small positive number but the real count is orders of magnitude larger, you have an underestimate. Remember to convert the negative ratio form: real estimate = -n_distinct × reltuples.

-- ground truth
SELECT count(DISTINCT customer_id) AS real_distinct
FROM orders;

-- what the planner thinks
SELECT n_distinct
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

Overriding n_distinct

When you know the true cardinality better than the sampler ever will, pin it with ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...).

Use the negative ratio form for columns that scale with table size — it survives growth. Use a positive integer only for a stable, bounded domain.

The override is stored in pg_attribute and applied on the next ANALYZE, so always re-analyze afterward.

-- distinct count grows ~linearly with rows: use the ratio form
ALTER TABLE orders
  ALTER COLUMN customer_id SET (n_distinct = -0.8);

ANALYZE orders;

n_distinct_inherited for Partitions

Partitioned tables have a second knob: n_distinct_inherited. The plain n_distinct override applies to the table's own rows; n_distinct_inherited applies to statistics gathered across the whole inheritance/partition tree.

For a partitioned orders table, queries usually scan the parent, so the inherited form is what the planner reads. Set both to be safe when a column is badly estimated.

ALTER TABLE orders
  ALTER COLUMN customer_id SET (n_distinct_inherited = -0.8);

ANALYZE orders;

MCV: Selectivity of Equality

For an equality predicate like status = 'shipped', the planner looks for the value in most_common_vals. If found, it uses the paired frequency from most_common_freqs directly. If not found, it assumes the value is one of the non-MCV values and spreads the remaining selectivity evenly across them.

So MCV accuracy decides whether a skewed predicate gets a sensible row estimate or a flat average that's wildly wrong for a hot value.

SELECT unnest(most_common_vals::text::text[]) AS val,
       unnest(most_common_freqs)            AS freq
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

When the MCV List Is Too Short

The MCV list length is capped by the column's statistics target. If a skewed column has 200 meaningfully frequent values but the target only keeps 100, the planner mis-estimates the values that fell off the list.

The fix is to widen the histogram and MCV list by raising the per-column statistics target, then re-analyze. This is the most common, lowest-risk correction for skewed equality and grouping estimates.

-- keep up to 1000 MCV entries + histogram buckets for this column
ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 1000;

ANALYZE orders;

Verifying the Fix with EXPLAIN

Never trust an override blindly — confirm the estimate moved toward reality. Run EXPLAIN ANALYZE and compare the planner's estimated rows to the actual rows the executor saw.

For grouping, look at the row count emitted by the HashAggregate / GroupAggregate node. A healthy plan has estimated and actual within a small factor of each other.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id;

Correlated Columns Need Extended Stats

Per-column MCV and n_distinct assume columns are independent. When two columns are correlated (e.g. city and country), the product of single-column selectivities under-estimates the combined group count.

That is exactly what multivariate CREATE STATISTICS ... (ndistinct, mcv) repairs — it stores a joint n_distinct and a joint MCV list for the column group, fixing multi-column GROUP BY and AND-predicate estimates.

CREATE STATISTICS orders_geo (ndistinct, mcv)
  ON city, country
  FROM orders;

ANALYZE orders;

Quick Check

You have a high-cardinality column whose distinct count grows linearly as the table grows, and ANALYZE keeps under-estimating it, wrecking GROUP BY plans. Which correction is best?

Recap

You learned to repair the two statistics that drive most cardinality errors:

  • Diagnose in pg_stats: read n_distinct, most_common_vals, most_common_freqs; compare against an exact count(DISTINCT ...).
  • n_distinct is positive for absolute counts, negative for a row-ratio. Override with ALTER COLUMN ... SET (n_distinct = ...), using the ratio form for growing columns and n_distinct_inherited for partitioned parents.
  • MCV drives equality selectivity. Lengthen it with SET STATISTICS when skewed values fall off the list.
  • Always ANALYZE after any change and confirm with EXPLAIN ANALYZE that estimated rows now track actual rows.
  • For correlated columns, reach for multivariate CREATE STATISTICS (ndistinct, mcv).

الأسئلة الشائعة

هل درس «تصحيحات MCV وN-Distinct» مجاني؟

نعم — نص درس «تصحيحات MCV وN-Distinct» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة PostgreSQL Performance & Query Optimization، انتقل إلى CoddyKit PRO. تتضمن دورة PostgreSQL Performance & Query Optimization 4 دروس في المجموع.

ماذا ستتعلم في «تصحيحات MCV وN-Distinct»؟

استخدم إحصاءات ndistinct والقيم الأكثر شيوعًا لإصلاح تقديرات الربط والتجميع. تتمرن على PostgreSQL Performance & Query Optimization مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ PostgreSQL Performance & Query Optimization؟

لا تُشترط خبرة سابقة. PostgreSQL Performance & Query Optimization على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 3 من أصل 4.

كم من الوقت يستغرق درس «تصحيحات MCV وN-Distinct»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس PostgreSQL Performance & Query Optimization هذا؟

نعم. كل درس في PostgreSQL Performance & Query Optimization يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

  1. كيفية تقدير المخطط لأعداد الصفوف
  2. الإحصاءات متعددة المتغيرات للأعمدة المترابطة
  3. تصحيحات MCV وN-Distinct
  4. التحقق من التقديرات مقابل الصفوف الفعلية
← العودة إلى PostgreSQL Performance & Query Optimization