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

Denis Perchine <dyp(at)perchine(dot)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