On Thu, Aug 20, 2009 at 2:21 PM, bricklen <bricklen@xxxxxxxxx> wrote:
Did you vacuum your table after the delete?On Thu, Aug 20, 2009 at 2:36 AM, Kumar Anand <kumar.anand@xxxxxxxxxxxxxx> wrote:
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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
