Joseph Shraibman wrote:
>
> Can't postgres do the index lookup first and find out there are only a
> few tuples that might match?
>
Actually it looks like postgres is doing this:
o=# explain select * from usertable where p = 33;
NOTICE: QUERY PLAN:
Seq Scan on usertable (cost=0.00..30.54 rows=502 width=72)
EXPLAIN
o=# explain select * from usertable where p = 1;
NOTICE: QUERY PLAN:
Index Scan using usertable_p_key on usertable (cost=0.00..25.68 rows=50
width=72)
EXPLAIN
o=# explain select count(*) from usertable where p = 1;
NOTICE: QUERY PLAN:
Aggregate (cost=25.81..25.81 rows=1 width=4)
-> Index Scan using usertable_p_key on usertable (cost=0.00..25.68
rows=50 width=4)
EXPLAIN
o=# explain select count(*) from usertable where p = 33;
NOTICE: QUERY PLAN:
Aggregate (cost=31.79..31.79 rows=1 width=4)
-> Seq Scan on usertable (cost=0.00..30.54 rows=502 width=4)
o=# select count(*) from usertable where p in(1,33) group by p;
count
-------
16
502
(2 rows)
This raises some other questions. Why can't postgres get the count(*)
from the index? Why doesn't it predict the correct number of rows in
the planner? (25 estimated vs 16 actual).
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com