Re: select count is too slow

On Thu, Aug 20, 2009 at 3: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.

Don't bloat your tables so much? In Postgresql, when you delete a
tuple it's still there in case some other transaction might still see
it. Try truncating your table instead of deleting it. Right now
pgsql is having to trawl through a few million dead rows to find the
few that are alive. You can also use vacuum full to reclaim all the
space, but you might need to reindex after that to keep bloat down on
the indexes.