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)
Following the formula outlined in
http://www.postgresql.org/docs/8.3/static/row-estimation-examples.html
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!
--
Reynold Xin
