Re: select count is too slow

VACUUM ANALYZE, rather

On Thu, Aug 20, 2009 at 2:21 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

> Did you vacuum your table after the delete?
>
>
> On Thu, Aug 20, 2009 at 2:36 AM, Kumar Anand <kumar(dot)anand(at)merceworld(dot)com>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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>