Re: Weird indices

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(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com