Re: select count is too slow

You don't have accurate stats over that table.

Try this after deleting:
VACUUM ANALYZE test2;

I suggest to activate autovacuum in your postgresql.conf, so you won't have to analyze table by hand.

-----Original Message-----
From: Kumar Anand <kumar.anand@merceworld.com>
To: pgsql-admin@postgresql.org
Subject: [ADMIN] select count is too slow
Date: Thu, 20 Aug 2009 15:06:45 +0530

Dear all,

I face this problem from last few days.

here is test2 table  with only one column id
erp_test=> \d test2    Table "public.test2"Column |  Type   | Modifiers
--------+---------+-----------id     | integer |

I insert 10,000,000 entries in this table.
erp_test=> INSERT INTO test2 VALUES (generate_series(1,10000000));
INSERT 0 10000000

then i delete all the entries
erp_test=> delete from test2;
DELETE 10000000
and i insert only 10 entries
erp_test=> INSERT INTO test2 VALUES (generate_series(1,10));
INSERT 0 10

now i try to count no of rows in this table which take long time for 
this 10 rows only
(about 2-3 second)
erp_test=> SELECT count(*) from test2;count
-------   10
(1 row)

this is the output of explain analyze query of the same

erp_test=> EXPLAIN ANALYZE SELECT count(*) from test2;                                                      QUERY 
PLAN                                                      
------------------------------------------------------------------------------------------------------------------------Aggregate  (cost=198652.13..198652.14 rows=1 width=0) (actual 
time=2123.471..2123.472 rows=1 loops=1)  ->  Seq Scan on test2  (cost=0.00..169732.70 rows=11567770 width=0) 
(actual time=2123.454..2123.454 rows=10 loops=1)Total runtime: 2123.609 ms
(3 rows)

can any one  solve my problem to speed up my select count query.

-- 
Thanks & Regards
Kumar Anand