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:
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
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?