Re: select count is too slow

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
>