Hi,

When running queries like "select count(*) from table", seq scan is
used. It makes such queries very slow for large tables. I've tested it
on Oracle and it seems to also use the "seq scan". But, for some
reason, MS-SQL uses index scans for counting rows, so similar queries
are accomplished almost immediately.

I've tried to prevent PostgreSQL from using seq scans by running "set
enable_seqscan=off", but for some reason it was ignored and the seq
scan was used anyway.

Is there any way to force using index scans?

I am using PostgreSQL 8.1.

I've run both "VACUUM" and "ANALYZE" commands.

Here is the test table:

testdb1=# \d test1
Table "public.test1"
Column | Type | Modifiers
--------+-------------------+-----------
a | character varying | not null
b | character varying |
Indexes:
"test1_pkey" PRIMARY KEY, btree (a)

testdb1=#


"Explain analyze" output:

testdb1=# explain analyze select count(*) from test1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21741.50..21741.51 rows=1 width=0) (actual
time=2781.242..2781.243 rows=1 loops=1)
-> Seq Scan on test1 (cost=0.00..19242.20 rows=999720 width=0)
(actual time=0.042..1575.085 rows=1000000 loops=1)
Total runtime: 2781.314 ms
(3 rows)

testdb1=#


A sample query that does use the index:

testdb1=# explain analyze select * from test1 where a = 'a0';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Scan using test1_pkey on test1 (cost=0.00..3.96 rows=1
width=32) (actual time=0.025..0.027 rows=1 loops=1)
Index Cond: ((a)::text = 'a0'::text)
Total runtime: 0.065 ms
(3 rows)

testdb1=#

Any help would be very much appreciated.

Thanks!