Use pg_stats to Spot Low-Cardinality Index Hotspots Before They Spot You

When migrating from PostgreSQL to YugabyteDB, table load time is only part of the story. Secondary indexes can become the real bottleneck.

One common reason is a low-cardinality leading index column.

Think of columns like:

				
					status
type
state
category
is_active

These columns often have only a few possible values. That does not automatically make them bad index candidates, but it does mean they are worth reviewing carefully.

In YugabyteDB, secondary indexes are distributed data structures too. A base table can use a nicely distributed hash-sharded primary key, while a secondary index has a much less selective key shape.

Important: Hash-sharding the base table does not automatically make every secondary index equally well distributed. Each index has its own key shape and its own tablet distribution.

Create a Demo Table

Let’s create a simple order_events table. The primary key uses event_id HASH, which is a good fit for distributing the base table rows.

				
					CREATE TABLE order_events (
  event_id    uuid NOT NULL,
  customer_id uuid NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  amount      numeric(12,2) NOT NULL,
  payload     jsonb,
  PRIMARY KEY (event_id HASH)
);

Now create an index on the status column:

				
					CREATE INDEX idx_order_events_status
ON order_events (status ASC);

That index looks reasonable at first glance. But status is intentionally low-cardinality in this demo.

Load Sample Data

Now load 250,000 rows.

This data is intentionally skewed:

● processed gets about 70% of the rows.
● pending gets about 20% of the rows.
● failed gets about 8% of the rows.
● cancelled gets about 2% of the rows.

				
					INSERT INTO order_events (
  event_id,
  customer_id,
  status,
  created_at,
  amount,
  payload
)
SELECT
  ('00000000-0000-0000-0000-' || lpad(g::text, 12, '0'))::uuid AS event_id,

  CASE
    -- One very large customer gets 60% of the rows.
    WHEN g % 100 < 60 THEN
      '11111111-1111-1111-1111-111111111111'::uuid

    -- A second larger customer gets 20% of the rows.
    WHEN g % 100 < 80 THEN
      '22222222-2222-2222-2222-222222222222'::uuid

    -- The rest are spread across many smaller customers.
    ELSE
      ('33333333-3333-3333-3333-' || lpad((g % 1000)::text, 12, '0'))::uuid
  END AS customer_id,

  CASE
    -- Low cardinality and skew.
    WHEN g % 100 < 70 THEN 'processed'
    WHEN g % 100 < 90 THEN 'pending'
    WHEN g % 100 < 98 THEN 'failed'
    ELSE 'cancelled'
  END AS status,

  -- Time-ordered values.
  '2026-01-01 00:00:00+00'::timestamptz + (g || ' seconds')::interval AS created_at,

  round((10 + random() * 500)::numeric, 2) AS amount,

  jsonb_build_object(
    'source', 'demo',
    'sequence', g,
    'region',
      CASE
        WHEN g % 3 = 0 THEN 'east'
        WHEN g % 3 = 1 THEN 'central'
        ELSE 'west'
      END
  ) AS payload
FROM generate_series(1, 250000) AS g;

Collect statistics:

				
					ANALYZE order_events;

Tip: The query against pg_stats reads catalog statistics. It does not scan the table. However, those statistics must already exist and be reasonably current. Run ANALYZE after loading representative data.

Confirm the Data Distribution

Before looking at pg_stats, let’s prove that the sample data is skewed:

				
					SELECT
  status,
  count(*) AS row_count,
  round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct_of_table
FROM order_events
GROUP BY status
ORDER BY row_count DESC;

Example output:

				
					. status   | row_count | pct_of_table
-----------+-----------+--------------
 processed |    175000 |        70.00
 pending   |     50000 |        20.00
 failed    |     20000 |         8.00
 cancelled |      5000 |         2.00

This gives us a simple but useful test case.

The index is valid. The column is indexed. But one value represents most of the table.

Check pg_stats

Now check the column statistics for status:

				
					SELECT
  schemaname,
  tablename,
  attname,
  null_frac,
  n_distinct,
  most_common_vals,
  most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename  = 'order_events'
  AND attname    = 'status';

Example output:

				
					.schemaname |  tablename   | attname | null_frac | n_distinct |           most_common_vals           |             most_common_freqs
------------+--------------+---------+-----------+------------+--------------------------------------+-------------------------------------------
 public     | order_events | status  |         0 |          4 | {processed,pending,failed,cancelled} | {0.7066333,0.19473334,0.0798,0.018833334}
(1 row)

The important parts are:

				
					n_distinct = 4

and

				
					processed = about 70.6%

That tells us status is both low-cardinality and skewed.

How to Read n_distinct

n_distinct is the main cardinality signal.

n_distinct value	Meaning	Example
`4`	Estimated fixed number of distinct values.	A status column with 4 possible values.
`-0.25`	Estimated distinct values are about 25% of table rows.	A moderately selective customer or account column.
`-1`	Roughly unique. Distinct values grow with row count.	A UUID or unique identifier column.

For this demo, n_distinct = 4, so the leading index column only has four values across 250,000 rows.

That is the first warning.

The second warning is in most_common_freqs, because one value dominates the table.

Tip: The risky signal is not just n_distinct = 4. The bigger warning is low cardinality plus skew. In this demo, processed represents more than 70% of the table.

Compare the Indexes

Now look at the index definition:

				
					SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'order_events'
ORDER BY indexname;

Example output:

				
					.       indexname        |                                        indexdef
-------------------------+----------------------------------------------------------------------------------------
 idx_order_events_status | CREATE INDEX idx_order_events_status ON public.order_events USING lsm (status ASC)
 order_events_pkey       | CREATE UNIQUE INDEX order_events_pkey ON public.order_events USING lsm (event_id HASH)
(2 rows)

This is a nice contrast:

				
					order_events_pkey       -> event_id HASH
idx_order_events_status -> status ASC

The primary key starts with a unique, hash-sharded value, while the secondary index starts with a low-cardinality range value.

See the Access Pattern

Now run a count for the most common status:

				
					EXPLAIN (ANALYZE, DIST)
SELECT count(*)
FROM order_events
WHERE status = 'processed';

Example output:

				
					Finalize Aggregate  (cost=15468.12..15468.13 rows=1 width=8) (actual time=90.919..90.920 rows=1 loops=1)
  ->  Index Only Scan using idx_order_events_status on order_events
        (cost=34.06..15026.48 rows=176658 width=0)
        (actual time=90.902..90.908 rows=1 loops=1)
        Index Cond: (status = 'processed'::text)
        Heap Fetches: 0
        Storage Index Read Requests: 1
        Storage Index Read Execution Time: 90.825 ms
        Storage Index Read Ops: 1
        Storage Index Rows Scanned: 175000
        Partial Aggregate: true
Planning Time: 0.121 ms
Execution Time: 91.004 ms
Storage Rows Scanned: 175000

The plan uses the index:

				
					Index Only Scan using idx_order_events_status

And because this is a count, YugabyteDB does not need to fetch table rows:

				
					Heap Fetches: 0

But the storage layer still scanned 175,000 index rows:

				
					Storage Index Rows Scanned: 175000

That is the teaching moment.

The index is being used, but the predicate is not very selective.

Tip: In this plan, rows=1 does not mean only one index row was scanned. The storage layer performed a partial aggregate and returned one aggregate result. The real signal is Storage Index Rows Scanned: 175000.

Now compare that to a less common value:

				
					EXPLAIN (ANALYZE, DIST)
SELECT count(*)
FROM order_events
WHERE status = 'cancelled';

Since cancelled is only about 2% of the table, it should scan far fewer index rows than processed.

Same index, same column…. Very different selectivity.

Audit Leading Index Columns

For a real schema, you do not want to check every index manually.

This query lists the leading key column for each valid index and joins it to pg_stats:

				
					WITH index_columns AS (
  SELECT
      n.nspname AS schema_name,
      t.relname AS table_name,
      t.oid     AS table_oid,
      i.relname AS index_name,
      i.oid     AS index_oid,
      ix.indisprimary,
      ix.indisunique,
      ix.indpred IS NOT NULL AS is_partial,
      ix.indexprs IS NOT NULL AS is_expression,
      pg_get_indexdef(i.oid) AS index_def,
      k.pos + 1 AS key_position,
      a.attname AS column_name
  FROM pg_index ix
  JOIN pg_class i
    ON i.oid = ix.indexrelid
  JOIN pg_class t
    ON t.oid = ix.indrelid
  JOIN pg_namespace n
    ON n.oid = t.relnamespace
  CROSS JOIN LATERAL generate_series(0, ix.indnkeyatts - 1) AS k(pos)
  LEFT JOIN pg_attribute a
    ON a.attrelid = t.oid
   AND a.attnum   = ix.indkey[k.pos]
  WHERE ix.indisvalid
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT
    ic.schema_name,
    ic.table_name,
    ic.index_name,
    ic.column_name AS leading_column,
    c.reltuples::bigint AS estimated_rows,
    s.n_distinct,
    CASE
      WHEN s.n_distinct > 0 THEN s.n_distinct
      WHEN s.n_distinct < 0 THEN abs(s.n_distinct) * c.reltuples
      ELSE NULL
    END::bigint AS estimated_distinct_values,
    round(s.null_frac::numeric, 4) AS null_frac,
    s.most_common_vals,
    s.most_common_freqs,
    ic.is_partial,
    ic.is_expression,
    ic.index_def
FROM index_columns ic
JOIN pg_class c
  ON c.oid = ic.table_oid
LEFT JOIN pg_stats s
  ON s.schemaname = ic.schema_name
 AND s.tablename  = ic.table_name
 AND s.attname    = ic.column_name
WHERE ic.key_position = 1
ORDER BY
  estimated_rows DESC,
  estimated_distinct_values NULLS FIRST;

For the demo table, the output looks like this:

				
					.schema_name |  table_name  |       index_name        | leading_column | estimated_rows | n_distinct | estimated_distinct_values | null_frac |             most_common_vals              |             most_common_freqs
-------------+--------------+-------------------------+----------------+----------------+------------+---------------------------+-----------+-------------------------------------------+--------------------------------------------
 public      | order_events | idx_order_events_status | status         |         250000 |          4 |                         4 |    0.0000 | {processed,pending,failed,cancelled}      | {0.7066333,0.19473334,0.0798,0.018833334}
 public      | order_events | order_events_pkey       | event_id       |         250000 |         -1 |                    250000 |    0.0000 |                                           |

The audit gives us the same conclusion:

				
					idx_order_events_status -> n_distinct = 4
order_events_pkey       -> n_distinct = -1

The primary key is effectively unique.

The status index has only four leading-key values.

Important: This audit does not prove an index is causing a tablet hotspot. It identifies indexes that deserve review. Final confirmation should come from workload testing, tablet metrics, query plans, and production-like read/write behavior.

What To Do If You Find One

Finding a low-cardinality index does not automatically mean you should drop it.

It means you should validate it against the workload.

Finding	Why it matters	What to review
Very few distinct values	Many rows share the same leading index key.	Check whether the index is selective enough for real queries.
One dominant value	Queries on that value may scan a large part of the index.	Compare common-value queries against rare-value queries.
High write volume	Index maintenance can concentrate writes on a small key range.	Check tablet-level write distribution.
Status/type/category as the only key	The index may not match the application’s real access pattern.	Consider a composite index or partial index.

For example, if the application usually searches for rare statuses, the index may still be useful.

If most queries search for the common value, the index may provide little benefit.

				
					CREATE INDEX idx_order_events_status_created
ON order_events (status ASC, created_at DESC);

Or use a partial index if only certain values matter:

				
					CREATE INDEX idx_order_events_failed
ON order_events (created_at DESC)
WHERE status = 'failed';

Those are not universal fixes. The right answer depends on the workload.

Tip: Do not fix indexes based on cardinality alone. Start with pg_stats, then validate with the real query pattern, EXPLAIN, tablet metrics, and production-like load.

Final Takeaway

pg_stats gives you a fast way to find low-cardinality and skewed leading index columns.

The key fields to review are:

				
					n_distinct
null_frac
most_common_vals
most_common_freqs

Low cardinality is not automatically bad.

But if a secondary index starts with a column that has only a few values, and one of those values dominates the table, that index deserves a closer look.

The base table may be well distributed.

The index may not be.

Have Fun!