| From: | Kumar Anand <kumar(dot)anand(at)merceworld(dot)com> | 
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org | 
| Subject: | select count is too slow | 
| Date: | 2009-08-20 09:36:45 | 
| Message-ID: | 4A8D192D.4050202@merceworld.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-admin | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jan-Peter Seifert | 2009-08-20 10:38:13 | text cast on regprocedure fails on 8.2 | 
| Previous Message | Scott Marlowe | 2009-08-20 02:44:06 | Re: trying to alter column |