Re: Database Select Slow - Mailing list pgsql-general

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@gmail.com>
>To: "carter ck" <carterck32@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Database Select Slow
>Date: Fri, 10 Aug 2007 10:57:19 -0500
>
>On 8/10/07, carter ck <carterck32@hotmail.com> 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@postgresql.org 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.