I have a rankings table and it has 1302 rows in total. I am a bit confused by how postgres (8.2.11) calculates the cardinality for this rankings table based on < predicates on gradrate attribute.
select histogram_bounds from pg_stats where attname = 'gradrate' and tablename = 'rankings'; histogram_bounds
------------------------------------{8,33,40,46,55,61,69,75,81,90,118}
explain SELECT * FROM rankings WHERE gradrate < 11; QUERY PLAN
---------------------------------------------------------------------------------Index Scan using gradrate_idx on rankings (cost=0.00..44.24 rows=11 width=196) Index Cond: (gradrate < 11::double precision)
(2 rows)
explain select * from rankings where gradrate < 10;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using gradrate_idx on rankings (cost=0.00..32.24 rows=7 width=196)
Index Cond: (gradrate < 10::double precision)
(2 rows)
Both gradrate 10 and gradrate 11 would fall in the first bucket.
Shouldn't the row estimation be:
(11 - 8) / (33 - 8) / 10 * 1302 = 15.624
and
(10 - 8) / (33 - 8) / 10 * 1302 = 10.416
instead of 11 and 7?
Perhaps I am missing something. I'd appreciate if you can point it out. Thanks!
