Concepts and theorems · Intermediate

Selectivity: estimate the fraction of rows matching a predicate

Distinguish predicate fractions from index usefulness using skewed values and combined conditions.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. A fraction is not a row count
  2. Work through a skewed distribution
  3. Combined predicates can be correlated
  4. An index decision includes more than selectivity
  5. Ask Logeia about this articleCheck your understanding

A fraction is not a row count

Selectivity can describe the fraction of a population that satisfies a predicate. Multiplying a row-count estimate by a selectivity estimate gives an estimated result cardinality. One percent of a million rows differs greatly from one percent of a thousand rows despite the same fraction. Informal phrases such as “highly selective” often mean a small matching fraction, so provide a number and denominator instead of relying on the adjective. Also distinguish the fraction observed in the actual data from the planner’s estimate obtained from statistics and assumptions about the predicate.

Work through a skewed distribution

Suppose a table has 10,000 orders: 100 pending and 9,900 completed. The actual selectivities of those equality predicates are one percent and ninety-nine percent. Assuming fifty percent for each because there are two distinct labels misses the skew. Most-common-value statistics can help represent such distributions. After bulk updates or the introduction of a new value, stale statistics can describe a population different from the current table. Check the actual population and predicate first, then compare the estimate. A low estimate can be outdated or based on an unsuitable distribution assumption rather than a missing index.

Combined predicates can be correlated

Predicates can be correlated, as with region and postal code. If nearly every row matching one condition also matches the other, multiplying independent fractions can underestimate the conjunction. PostgreSQL extended statistics can describe selected cross-column dependencies or common combinations. They do not automatically model every possible correlation for every operator and expression. Identify which predicates create the error and check which statistics apply. The important distinction is between a convenient independence assumption and a property supported by the data. Adding more individual column statistics is not always enough when the error arises between columns.

An index decision includes more than selectivity

A small matching fraction can make an index attractive, but cost also depends on requested columns, physical layout, caching, table size and order requirements. A sequential scan can be sensible when most rows are needed. Compare estimated rows with actual rows at the relevant plan nodes and locate where the discrepancy grows. EXPLAIN ANALYZE executes the statement, so it requires care with writes and expensive production queries. Do not force a plan from the selectivity number alone. Use the fraction as one input to a cost explanation that accounts for the entire required result.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path