How to Choose an Index and Estimate Selectivity in PostgreSQL

← Back to the section

"The index is there, but the query is still slow" — one of the most common PostgreSQL complaints. Almost always the cause is selectivity: PostgreSQL itself decides whether using an index pays off or whether it's simpler to read the whole table. Let's see how it decides and what to do when it decides wrong.

Why PostgreSQL sometimes doesn't use an index

Imagine a table of a million orders. Each order has a status field with five values: NEW, PAID, SHIPPED, DELIVERED, CANCELLED. Most orders are DELIVERED, roughly 62%.

You added an index on status and run WHERE status = 'DELIVERED'. PostgreSQL looks at the index and sees 620,000 rows out of 1,000,000. To fetch each of them it jumps to a random spot on disk — slower than reading the whole table sequentially. So PostgreSQL chooses a seq scan, and it's right.

The key term here is selectivity.

What selectivity is

In the documentation and in everyday developer talk the word "selectivity" means two different things. For the planner it is the fraction of rows that pass the condition: the smaller that fraction, the more an index pays off. Developers usually mean the variety of values in a column. The two are directly related: the more distinct values a column holds, the fewer rows an equality condition returns.

Below, selectivity of a column means the second one:

variety = number_of_distinct_values / total_rows

An index on is_deleted or status is practically useless: WHERE is_deleted = false returns 98% of the table, and PostgreSQL sensibly prefers a seq scan.

How PostgreSQL makes the decision

The planner estimates what percentage of rows the condition returns and compares the cost of two paths:

Two more parameters affect the decision: how much data PostgreSQL considers cached (effective_cache_size) and how much more expensive a random read is than a sequential one (random_page_cost).

Tuning random_page_cost on SSD

By default random_page_cost = 4.0 — calibrated for hard drives, where a random read is four times slower than a sequential one. On SSD the difference almost disappears.

Leave it alone and PostgreSQL keeps choosing a seq scan where an index is faster.

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

1.1 is the standard recommendation for SSD.

How to view column statistics

PostgreSQL stores statistics for each column in the system view pg_stats:

live example

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

What the fields mean:

• n_distinct — an estimate of distinct values. A positive number is an absolute count; a negative one (-0.05) is a fraction of the rows (5% distinct).
• most_common_vals — an array of the most frequent values.
• most_common_freqs — frequencies from 0 to 1 for each of them.
• null_frac — the fraction of NULL values.

Example result for the status column:

attname           | status
n_distinct        | 5
most_common_vals  | {DELIVERED,SHIPPED,NEW,CANCELLED,PAID}
most_common_freqs | {0.62, 0.18, 0.10, 0.06, 0.04}

This is exactly where the 62% of rows for DELIVERED and the 4% for PAID come from.

What pg_stats holds is an estimate from the last ANALYZE; the real fractions are one ordinary query away:

live example

SELECT status,
       count(*) AS rows_with_status,
       round(100.0 * count(*) / (SELECT count(*) FROM orders), 1) AS pct
FROM orders
GROUP BY status
ORDER BY rows_with_status DESC;

A composite index where a single-column one is useless

The status column with five values is of little use on its own. Add a second column with high selectivity and the picture changes.

A composite index (status, created_at):

• WHERE status = 'DELIVERED' — 62% of rows → seq scan.
• WHERE status = 'DELIVERED' AND created_at > now() - interval '1 day' — roughly 62% × 0.5% = 0.3% → the index is a perfect fit.

The selectivities multiply. That's why a composite index works where a single-column one is useless — but both conditions must be in the query.

The planner's arithmetic is just that: multiply the fractions, compare with the thresholds above.

live example

public class Selectivity {

    static final long ROWS = 1_000_000;

    public static void main(String[] args) {
        verdict("status = 'DELIVERED'", 0.62);
        verdict("status = 'PAID'", 0.04);
        verdict("status = 'DELIVERED' AND created_at > now() - 1 day", 0.62 * 0.005);

        double independent = 0.05 * 0.05;
        double real = 0.05;
        System.out.println();
        System.out.println("country_code = 'RU' AND currency = 'RUB'");
        System.out.println("  planner expects " + rows(independent) + " rows");
        System.out.println("  reality returns " + rows(real) + " — off by a factor of "
                + Math.round(real / independent));
    }

    static void verdict(String where, double fraction) {
        String choice = fraction < 0.05 ? "index"
                : fraction > 0.20 ? "seq scan" : "depends on the cache";
        System.out.printf("%-52s %7d rows (%5.2f%%) -> %s%n",
                where, rows(fraction), fraction * 100, choice);
    }

    static long rows(double fraction) {
        return Math.round(ROWS * fraction);
    }
}

The first three lines are the threshold table in numbers, the last two show where multiplying lies.

How to check what actually happens

EXPLAIN ANALYZE shows both the plan and the actual execution:

live example

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'PAID';

Seq Scan on orders  (cost=0.00..18334.00 rows=400000 width=...) (actual time=0.012..89.123 rows=42137 loops=1)
  Filter: (status = 'PAID')
  Rows Removed by Filter: 957863

Compare two numbers here:

• rows=400000 — the planner's estimate.
• actual rows=42137 — the real number of rows.

A tenfold discrepancy means the statistics are stale and the plan was chosen on wrong data.

When statistics go stale and what to do

Run ANALYZE manually

Autovacuum collects statistics automatically when about 10% of a table's rows have changed; during a bulk load it can't keep up, so after a large load run it by hand:

ANALYZE orders;                            -- one table
ANALYZE orders (status, created_at);       -- only the needed columns
ANALYZE;                                   -- every table in the database you may read

Increase the depth of statistics

By default PostgreSQL keeps statistics for the 100 most frequent values (statistics_target = 100). While a column holds fewer than a hundred distinct values, all of them land in that list and the estimates are exact. The trouble starts when there are more: city, say, has tens of thousands. Only the hundred most frequent ones get in, and the rest are estimated by a single averaged figure — so for a rare city the planner expects far more rows than really come back.

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

After this PostgreSQL keeps up to 1000 most frequent values for the column.

Correlated columns

Two columns are often related: country_code and currency, say — Russia almost always means the ruble. By default PostgreSQL treats columns as independent and multiplies the fractions: 5% × 5% = 0.25%. But if the ruble only shows up on Russian orders, the condition returns the full 5% — exactly what the example above computed. The planner expects two and a half thousand rows and gets fifty thousand — a plan built for a set twenty times smaller.

For such cases there is extended statistics:

CREATE STATISTICS stats_orders_geo (dependencies, ndistinct)
    ON country_code, currency FROM orders;
ANALYZE orders;

Now the planner knows about the dependency and stops multiplying blindly: for WHERE country_code = 'RU' AND currency = 'RUB' it takes the estimate from the leading condition.

Common mistakes

An index on a boolean column. WHERE is_deleted = false is almost always 98%+ of the rows, so an index won't help. For partial coverage a partial index is better: CREATE INDEX ON orders (created_at) WHERE deleted_at IS NULL — it covers only the current rows.

Ignoring a discrepancy between rows and actual rows. A tenfold discrepancy in EXPLAIN ANALYZE is a signal that the statistics are stale. The fix is ANALYZE — after a bulk load you run it by hand: autovacuum can't keep up with a fast insert.

In short

• Selectivity of a column is distinct values over total rows; the planner counts the fraction of rows a condition returns.
• Threshold rule: less than 5% of rows → index, more than 20% → seq scan; an index on is_deleted or on status with five values is useless.
• A composite index (status, created_at) works where (status) is useless: the fractions multiply.
• pg_stats shows what the planner knows (n_distinct, most_common_freqs); a tenfold gap between rows and actual rows in EXPLAIN ANALYZE calls for ANALYZE.
• On SSD set random_page_cost = 1.1: the default 4.0 is for a hard drive and undervalues indexes.
• Columns with thousands of values — SET STATISTICS 1000; related columns — CREATE STATISTICS ... (dependencies), otherwise multiplying the fractions lies.

What to read next

• Composite Indexes in PostgreSQL — column order and the leftmost prefix.
• EXPLAIN ANALYZE — how to read a query plan in detail.
• PostgreSQL index types — when B-tree, and when GIN or BRIN.
• VACUUM and autovacuum — when and how statistics are updated.