From: | bricklen <bricklen(at)gmail(dot)com> |
---|---|
To: | Kumar Anand <kumar(dot)anand(at)merceworld(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: select count is too slow |
Date: | 2009-08-20 21:21:24 |
Message-ID: | 33b743250908201421y4d2f6f6ao80c7e0102cc4914c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | bricklen | 2009-08-20 21:22:28 | Re: select count is too slow |
Previous Message | Tena Sakai | 2009-08-20 20:22:03 | Re: Sharing /etc/passwd with PostgreSQL |