PostgreSQL (and other relational databases) needs to estimate how many rows a query will return because it directly impacts which execution plan it chooses. Pick the wrong plan, and your query could be thousands of times slower.
Here’s how the query planner actually works under the hood to estimate the number of rows…
When you run a range query like “SELECT * FROM table WHERE column < 1000”, PostgreSQL doesn’t just guess.
In fact, for numerical values, it maintains histogram buckets that divide your data into equal frequency ranges. The planner figures out which bucket your value falls into, calculates what fraction of that bucket matches your condition, and estimates the rows.
For equality conditions like “column = ‘CRAAAA’”, the approach is different. PostgreSQL maintains a list of the most common values (MCVs) with their exact frequencies. If your value is in that list, it uses the stored frequency directly. If not, it assumes the remaining values are evenly distributed among all the non-common distinct values.
For joins, the planner looks at statistics from both tables. It considers null fractions, distinct value counts, and whether values appear in MCV lists. The approach combines these factors to estimate the number of matching rows the join will produce.
There is a bunch of simple math involved in this estimation, and PostgreSQL’s documentation covers it pretty nicely. Have added it below.