Understanding rows count computation

Question

PostgreSQL 9.4

I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column:

 most_common_vals
{20000,80000,40000,60000,100000}

Now, I run the following simple query EXPLAIN ANALYZE SELECT * FROM customers WHERE income=123123 to understand the rows count estimating. Output:

Seq Scan on customers (cost=0.00..738.00 rows=1 width=268) (actual time=4.669..4.669 rows=0 loops=1)
 Filter: (income = 123123)
 Rows Removed by Filter: 20000

Since the optimizer doesn't have statistic for the income value 123123, it made a wild guess for 0.5% of the table size. So, the estimated row count should have been 500. But the optimizer returned the 1. Why? Maybe I didn't understand the estimating process of row counting of unknown values?

Couldn't you explain it a bit?

Answer 1

Since it was not in most_common_vals, PostgreSQL then looks in the histogram. If there is no histogram, it would conclude the most common values are the only values present in the table, and therefore the estimate for 123123 is zero. But it doesn't allow zero estimates in most places, to prevent div by zero error, and instead clamps it at 1.

0.5% is for cases where there is no information, like for a generic query plan or a join where the value won't be known at planning time.

But having a list of MCV and the one you want not being present in that list and there being no histogram to fallback on does constitute information.

• St.Antario: You mean the histogram_bounds column? In my case it's an empty string. BTW, correlation = 0.199117
• St.Antario: But what if there's a histogram. I tried another column of customers. Percisely, zip has most_common_vals and histogram_bounds non empty. I tried to execute EXPLAIN ANALYZE SELECT * FROM customers WHERE zip=62358 where 62538 within the last bucket of the histogram_bound: 62539 and 72365, but not presented in the most_common_vals. The planner guessed that there's only one row again. So, if it doesn't have the most_common_vals statistic, it conludes that there's only one row?