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