Hi,
Thanks for the clarification. It helps to resolve the problem. Now, the page
can be fully loaded within 2 seconds.
Thanks.
From: "Scott Marlowe" <scott.marlowe@xxxxxxxxx>
To: "carter ck" <carterck32@xxxxxxxxxxx>
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Database Select Slow
Date: Fri, 10 Aug 2007 10:57:19 -0500
On 8/10/07, carter ck <carterck32@xxxxxxxxxxx> wrote:
> Hi all,
>
> I am facing a performance issue here. Whenever I do a count(*) on a
table
> that contains about 300K records, it takes few minutes to complete.
Whereas
> my other application which is counting > 500K records just take less
than 10
> seconds to complete.
>
> I have indexed all the essential columns and still it does not improve
the
> speed.
As previously mentioned, indexes won't help with a count(*) with no
where clause.
They might help with a where clause, if it's quite selective, but if
you're grabbing a noticeable percentage of a table, pgsql will rightly
switch to a seq scan.
Here's some examples from my goodly sized stats db here at work:
\timing
explain select * from businessrequestsummary;
QUERY PLAN
-------------------------------------------------------------------------------------
Seq Scan on businessrequestsummary (cost=0.00..3280188.63
rows=67165363 width=262)
Time: 0.441 ms
gives me an approximate value of 67,165,363 rows.
explain select * from businessrequestsummary where lastflushtime >
now() - interval '1 day';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using businessrequestsummary_lastflushtime_dx on
businessrequestsummary (cost=0.00..466.65 rows=6661 width=262)
Index Cond: (lastflushtime > (now() - '1 day'::interval))
says 6661 rows. and takes 0.9 ms and would use the index.
To run the real queries I get much slower times. :)
Now, to run the real count(*) queries:
select count(*) from businessrequestsummary where lastflushtime >
now() - interval '1 day';
count
--------
274192
(1 row)
Time: 546.528 ms
(data in the buffers makes it fast)
select count(*) from businessrequestsummary where lastflushtime >
now() - interval '1 week';
count
---------
1700050
(1 row)
Time: 26291.155 ms
second run (data now in buffer)
select count(*) from businessrequestsummary where lastflushtime >
now() - interval '1 week';
count
---------
1699689
(1 row)
Time: 2592.573 ms
Note the number changed, because this db is constantly being updated
in real time with production statistics.
I'm not going to run a select count(*) on that db, because it would
take about 30 minutes to run. It's got about 67million rows in it.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
_________________________________________________________________
Find just what you are after with the more precise, more powerful new MSN
Search. http://search.msn.com.sg/ Try it now.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
