Re: PostgreSQL 7.1 forces sequence scan when there is no reason

Denis Perchine <dyp@perchine.com> writes:
> db=> explain analyze select count(*) from listmembers where
>  server_id = 15182; NOTICE:  QUERY PLAN:
> Aggregate  (cost=31428.34..31428.34 rows=1 width=0) (actual
>  time=38632.84..38632.84 rows=1 loops=1) ->  Seq Scan on listmembers
>  (cost=0.00..31379.16 rows=19671 width=0) (actual time=198.13..38494.36
>  rows=10011 loops=1) Total runtime: 38633.01 msec
> EXPLAIN
> db=> set enable_seqscan to no;
> SET VARIABLE
> db=> explain analyze select count(*) from listmembers where
>  server_id = 15182; NOTICE:  QUERY PLAN:
> Aggregate  (cost=60210.41..60210.41 rows=1 width=0) (actual
>  time=2117.61..2117.61 rows=1 loops=1) ->  Index Scan using
>  listmembers_sid_key on listmembers  (cost=0.00..60161.24 rows=19671 width=0)
>  (actual time=81.41..2096.67 rows=10011 loops=1) Total runtime: 2117.83 msec
> EXPLAIN

Hm.  Is it possible that the rows with server_id = 15182 are clustered
together?  Given that you are fetching 10011 rows from a 14224-page
table, it seems unlikely that an indexscan could be such a big win
unless there was a very strong clustering effect.

> db=# select * from pg_statistic where starelid=6429402 ;

This is pretty unhelpful (not to mention unreadable) since we have no
idea which row is which.  Could we see the pg_stats view, instead?

            regards, tom lane