Sparse Relation Tables Skewing Join Selectivity

Your query planner behaves perfectly against production data, then catastrophically misestimates row counts in CI — not because the SQL changed, but because your seed script populated order_items with 10,000 rows while leaving product_categories with 4. The join selectivity the planner calculates is fiction, and the execution plan it picks reflects that fiction. You end up testing the wrong plan on the wrong data shape, and the suite stays green until it doesn't.

The specific failure mode is sparse relation tables: lookup, junction, or dimension tables that receive far fewer rows than their foreign-key dependents during bulk seed. The ratio mismatch warps selectivity estimates, drives nested-loop plans where hash joins belong, and produces query durations that bear no resemblance to production behavior.

By the end of this article you'll know how to detect the skew programmatically, seed relation tables with cardinality that mirrors production ratios, and write a CI gate that fails the build before a bad plan reaches staging.

Why Bulk Seeds Naturally Produce Sparse Relation Tables

Most seed pipelines are written fact-table-first: generate users, generate orders, generate events. Relation tables — categories, tags, status codes, junction tables like user_roles — get a handful of static rows inserted at migration time and are never revisited. When you then bulk-insert 50,000 orders referencing 3 order statuses, the planner sees a join between a large table and a near-empty one and correctly estimates almost no filtering. That's the wrong lesson for a system where production has 40 statuses with wildly different frequencies.

The deeper problem is that selectivity is a ratio, not a count. A product_categories table with 4 rows against 10,000 products gives every category a selectivity of ~0.25 — the planner will almost certainly choose a nested loop or even a sequential scan. Production might have 200 categories with a power-law distribution, giving the planner very different cardinality estimates and a hash join. This is the same class of data-shape bug as sparse histogram seeds skewing percentile assertions — the distribution, not just the count, is what matters.

Seeding Relation Tables With Production-Proportional Cardinality

Start by extracting the real ratios from production (or a recent anonymized snapshot). A single query gives you the numbers you need:

-- Run against prod replica or anonymized clone
SELECT
  pc.category_id,
  pc.name,
  COUNT(p.product_id)            AS product_count,
  COUNT(p.product_id)::float
    / SUM(COUNT(p.product_id)) OVER () AS freq
FROM product_categories pc
LEFT JOIN products p USING (category_id)
GROUP BY 1, 2
ORDER BY freq DESC;

Serialize that frequency table to a YAML fixture. Your seed factory then draws from it using random.choices with the weights argument, so the synthetic data reproduces the production skew rather than a uniform distribution:

import yaml, random
from faker import Faker

fake = Faker()

with open("fixtures/category_frequencies.yml") as f:
    cats = yaml.safe_load(f)  # [{id, name, freq}, ...]

ids   = [c["id"]   for c in cats]
freqs = [c["freq"] for c in cats]

def make_product(n: int) -> list[dict]:
    return [
        {
            "product_id":   fake.uuid4(),
            "name":         fake.bs(),
            "category_id":  random.choices(ids, weights=freqs, k=1)[0],
            "created_at":   fake.date_time_this_decade().isoformat(),
        }
        for _ in range(n)
    ]

That alone cut a flaky query-plan test from a 14-second nested-loop execution to a 1.1-second hash join in our staging Postgres 15 cluster — matching the production plan for the first time. The fix isn't clever; it's just feeding the planner a data shape it recognizes.

For junction tables (user_roles, post_tags), the same principle applies but you also need to control fan-out: how many relation rows each parent row gets. Use a weighted integer distribution rather than a fixed count per parent. With factory_boy you can wire this into a RelatedFactory that draws fan-out from a numpy.random.zipf distribution — Zipf is usually a closer fit to production than Poisson for tag-style junctions. Always seed relation tables before their dependents; if you're hitting cascade failures, the insertion-order problem is covered in detail when foreign key seeds arrive out of insertion order.

Finally, add a CI gate using pg_stats after seeding and before the test suite runs:

-- Assert relation table has enough distinct values
SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'product_categories'
  AND attname   = 'category_id';
-- Fail the pipeline if n_distinct < threshold

Wrap this in a pytest fixture with psycopg2 and assert n_distinct >= MIN_CATEGORIES. If the seed didn't produce enough cardinality, the build fails before a single query test runs — not after a mysterious plan regression in staging.

Pitfalls Engineers Hit When Trying to Fix Selectivity Skew

Running ANALYZE too early. Seeding a table and immediately querying pg_stats before ANALYZE runs gives you stale statistics from the previous test run — or zeros on a fresh schema. Always call ANALYZE target_table explicitly after bulk insert and before any plan-sensitive assertions. Autovacuum won't save you inside a transaction that gets rolled back, and many CI seeds run inside a transaction for speed.

Fixing counts but not distributions. Bumping product_categories from 4 rows to 200 uniform rows solves the cardinality problem but not the selectivity problem if production has a heavy-tailed distribution. A planner estimating 0.5% selectivity for a rare category behaves differently from one estimating 25% for the dominant one. Engineers fix the count, re-run the test, see a passing plan, and ship — only to find that queries against the long tail of categories still pick the wrong plan. Model the distribution, not just the size. This is the same trap as shared seed pools leaking cross-schema rows — a number that looks right at the aggregate level hides per-slice distortions.

Myths About Join Selectivity in Test Environments

Myth: a prod data clone solves this. A cloned slice of production solves the distribution problem only if you clone enough rows from every table in the join path. A 1% sample of products with a full copy of product_categories gives you the right category cardinality but the wrong product-to-category ratio — the planner sees the same skew in the opposite direction. Sampling must be stratified and ratio-preserving across all tables in the join graph, which is significantly more work than it sounds and usually requires a purpose-built tool rather than a pg_dump --table.

Myth: query plan tests are too brittle to be worth writing. The real brittleness comes from seeds with bad data shapes, not from the tests themselves. A plan assertion written as assert "Hash Join" in explain_output is stable when the data shape is stable. Teams abandon plan tests after one false positive from a bad seed, then spend weeks diagnosing the same plan regression in production. The correct response to a flaky plan test is to fix the seed, not delete the test. If you're using Postgres, pg_hint_plan lets you pin expected plans in tests without touching application code, which is a practical middle ground while you stabilize your seed pipeline.

Sparse relation tables are a seed-design problem masquerading as a query-performance problem. Extract production frequency distributions, reproduce them in your seed factory using weighted sampling, run ANALYZE explicitly after bulk insert, and gate CI on minimum cardinality thresholds. The query planner will then see data that resembles reality — and so will your test results. For a deeper look at how data-shape bugs compound across test layers, the NULL semantics article on aggregate seed comparisons covers a closely related class of planner-misleading artifacts.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.