select count is too slow

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.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Kumar Anand [ Do, 20 August 2009 11:36 ] [ ID #2012793 ]

Re: select count is too slow

--000e0cd6b30a78c6f80471995506
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Did you vacuum your table after the delete?

On Thu, Aug 20, 2009 at 2:36 AM, Kumar Anand <kumar.anand [at] merceworld.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.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--000e0cd6b30a78c6f80471995506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Did you vacuum your table after the delete?<br><br><div class=3D"gmail_quot=
e">On Thu, Aug 20, 2009 at 2:36 AM, Kumar Anand <span dir=3D"ltr"><<a hr=
ef=3D"mailto:kumar.anand [at] merceworld.com">kumar.anand [at] mercewo rld.com</a>>=
</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Dear all,<br>
<br>
I face this problem from last few days.<br>
<br>
here is test2 table =C2=A0with only one column id<br>
erp_test=3D> \d test2<br>
=C2=A0 =C2=A0Table "public.test2"<br>
Column | =C2=A0Type =C2=A0 | Modifiers<br>
--------+---------+-----------<br>
id =C2=A0 =C2=A0 | integer |<br>
<br>
I insert 10,000,000 entries in this table.<br>
erp_test=3D> INSERT INTO test2 VALUES (generate_series(1,10000000));<br>
INSERT 0 10000000<br>
<br>
then i delete all the entries<br>
erp_test=3D> delete from test2;<br>
DELETE 10000000<br>
<br>
and i insert only 10 entries<br>
erp_test=3D> INSERT INTO test2 VALUES (generate_series(1,10));<br>
INSERT 0 10<br>
<br>
now i try to count no of rows in this table which take long time for this 1=
0 rows only<br>
(about 2-3 second)<br>
erp_test=3D> SELECT count(*) from test2;<br>
count<br>
-------<br>
=C2=A0 10<br>
(1 row)<br>
<br>
this is the output of explain analyze query of the same<br>
<br>
erp_test=3D> EXPLAIN ANALYZE SELECT count(*) from test2;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0------------------------------------------------------ -=
------------------------------------------------------------ -----<br>

Aggregate =C2=A0(cost=3D198652.13..198652.14 rows=3D1 width=3D0) (actual ti=
me=3D2123.471..2123.472 rows=3D1 loops=3D1)<br>
=C2=A0-> =C2=A0Seq Scan on test2 =C2=A0(cost=3D0.00..169732.70 rows=3D1=
1567770 width=3D0) (actual time=3D2123.454..2123.454 rows=3D10 loops=3D1)<b=
r>
Total runtime: 2123.609 ms<br>
(3 rows)<br>
<br>
<br>
can any one =C2=A0solve my problem to speed up my select count query.<br>
<br>
-- <br>
Thanks & Regards<br>
Kumar Anand<br><font color=3D"#888888">
<br>
-- <br>
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin [at] postgresql=
..org" target=3D"_blank">pgsql-admin [at] postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a><br>
</font></blockquote></div><br>

--000e0cd6b30a78c6f80471995506--
Bricklen [ Do, 20 August 2009 23:21 ] [ ID #2012794 ]

Re: select count is too slow

--0016367d565645e9d4047199592f
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

VACUUM ANALYZE, rather

On Thu, Aug 20, 2009 at 2:21 PM, bricklen <bricklen [at] gmail.com> wrote:

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

--0016367d565645e9d4047199592f
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

VACUUM ANALYZE, rather<br><br><div class=3D"gmail_quote">On Thu, Aug 20, 20=
09 at 2:21 PM, bricklen <span dir=3D"ltr"><<a href=3D"mailto:bricklen [at] gm=
ail.com">bricklen [at] gmail.com</a>></span> wrote:<br><blockquote class=3D"g=
mail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
0pt 0pt 0.8ex; padding-left: 1ex;">
Did you vacuum your table after the delete?<div><div></div><div class=3D"h5=
"><br><br><div class=3D"gmail_quote">On Thu, Aug 20, 2009 at 2:36 AM, Kumar=
Anand <span dir=3D"ltr"><<a href=3D"mailto:kumar.anand [at] merceworld.com" =
target=3D"_blank">kumar.anand [at] merceworld.com</a>></span> wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Dear all,<br>
<br>
I face this problem from last few days.<br>
<br>
here is test2 table =C2=A0with only one column id<br>
erp_test=3D> \d test2<br>
=C2=A0 =C2=A0Table "public.test2"<br>
Column | =C2=A0Type =C2=A0 | Modifiers<br>
--------+---------+-----------<br>
id =C2=A0 =C2=A0 | integer |<br>
<br>
I insert 10,000,000 entries in this table.<br>
erp_test=3D> INSERT INTO test2 VALUES (generate_series(1,10000000));<br>
INSERT 0 10000000<br>
<br>
then i delete all the entries<br>
erp_test=3D> delete from test2;<br>
DELETE 10000000<br>
<br>
and i insert only 10 entries<br>
erp_test=3D> INSERT INTO test2 VALUES (generate_series(1,10));<br>
INSERT 0 10<br>
<br>
now i try to count no of rows in this table which take long time for this 1=
0 rows only<br>
(about 2-3 second)<br>
erp_test=3D> SELECT count(*) from test2;<br>
count<br>
-------<br>
=C2=A0 10<br>
(1 row)<br>
<br>
this is the output of explain analyze query of the same<br>
<br>
erp_test=3D> EXPLAIN ANALYZE SELECT count(*) from test2;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0------------------------------------------------------ -=
------------------------------------------------------------ -----<br>


Aggregate =C2=A0(cost=3D198652.13..198652.14 rows=3D1 width=3D0) (actual ti=
me=3D2123.471..2123.472 rows=3D1 loops=3D1)<br>
=C2=A0-> =C2=A0Seq Scan on test2 =C2=A0(cost=3D0.00..169732.70 rows=3D1=
1567770 width=3D0) (actual time=3D2123.454..2123.454 rows=3D10 loops=3D1)<b=
r>
Total runtime: 2123.609 ms<br>
(3 rows)<br>
<br>
<br>
can any one =C2=A0solve my problem to speed up my select count query.<br>
<br>
-- <br>
Thanks & Regards<br>
Kumar Anand<br><font color=3D"#888888">
<br>
-- <br>
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin [at] postgresql=
..org" target=3D"_blank">pgsql-admin [at] postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a><br>
</font></blockquote></div><br>
</div></div></blockquote></div><br>

--0016367d565645e9d4047199592f--
Bricklen [ Do, 20 August 2009 23:22 ] [ ID #2012795 ]

Re: select count is too slow

--=-/qpZOd0beP75ydkpHcEG
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

You don't have accurate stats over that table.

Try this after deleting:
VACUUM ANALYZE test2;

I suggest to activate autovacuum in your postgresql.conf, so you won't
have to analyze table by hand.

-----Original Message-----
From: Kumar Anand <kumar.anand [at] merceworld.com>
To: pgsql-admin [at] postgresql.org
Subject: [ADMIN] select count is too slow
Date: Thu, 20 Aug 2009 15:06:45 +0530


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


--=-/qpZOd0beP75ydkpHcEG
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.26.0">
</HEAD>
<BODY>
You don't have accurate stats over that table.<BR>
<BR>
Try this after deleting:<BR>
VACUUM ANALYZE test2;<BR>
<BR>
I suggest to activate autovacuum in your postgresql.conf, so you won't have to analyze table by hand.<BR>
<BR>
-----Original Message-----<BR>
<B>From</B>: Kumar Anand <<A HREF="mailto:Kumar%20Anand%20%3ckumar.anand [at] merceworld.com%3e">kumar.anand [at] merceworld.com</A>><BR>
<B>To</B>: <A HREF="mailto:pgsql-admin [at] postgresql.org">pgsql-admin [at] postgresql.org</A><BR>
<B>Subject</B>: [ADMIN] select count is too slow<BR>
<B>Date</B>: Thu, 20 Aug 2009 15:06:45 +0530<BR>
<BR>
<PRE>
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

</PRE>
</BODY>
</HTML>

--=-/qpZOd0beP75ydkpHcEG--
imartinez [ Fr, 21 August 2009 13:10 ] [ ID #2012970 ]

Re: select count is too slow

On Thu, Aug 20, 2009 at 3:36 AM, Kumar Anand<kumar.anand [at] merceworld.com> wr=
ote:
> Dear all,
>
> I face this problem from last few days.
>
> here is test2 table =A0with only one column id
> erp_test=3D> \d test2
> =A0 =A0Table "public.test2"
> Column | =A0Type =A0 | Modifiers
> --------+---------+-----------
> id =A0 =A0 | integer |
>
> I insert 10,000,000 entries in this table.
> erp_test=3D> INSERT INTO test2 VALUES (generate_series(1,10000000));
> INSERT 0 10000000
>
> then i delete all the entries
> erp_test=3D> delete from test2;
> DELETE 10000000
>
> and i insert only 10 entries
> erp_test=3D> 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=3D> SELECT count(*) from test2;
> count
> -------
> =A0 10
> (1 row)
>
> this is the output of explain analyze query of the same
>
> erp_test=3D> EXPLAIN ANALYZE SELECT count(*) from test2;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN
>
> =A0--------------------------------------------------------- -------------=
--------------------------------------------------
> Aggregate =A0(cost=3D198652.13..198652.14 rows=3D1 width=3D0) (actual
> time=3D2123.471..2123.472 rows=3D1 loops=3D1)
> =A0-> =A0Seq Scan on test2 =A0(cost=3D0.00..169732.70 rows=3D11567770 wid=
th=3D0) (actual
> time=3D2123.454..2123.454 rows=3D10 loops=3D1)
> Total runtime: 2123.609 ms
> (3 rows)
>
>
> can any one =A0solve 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.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Scott Marlowe [ Fr, 21 August 2009 19:47 ] [ ID #2012972 ]
Datenbanken » gmane.comp.db.postgresql.admin » select count is too slow

Vorheriges Thema: Background color in query tool
Nächstes Thema: text cast on regprocedure fails on 8.2

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum