Re: [GENERAL] Database Select Slow
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" <[EMAIL PROTECTED]> To: "carter ck" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] 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
Re: [GENERAL] Database Select Slow
On 8/10/07, carter ck <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Database Select Slow
On 10.08.2007, at 06:58, .ep wrote: Hi, what if I need to do a count with a WHERE condition? E.g., SELECT count(*) from customers where cust_id = 'georgebush' and created_on > current_date - interval '1 week' ; Can I get the info about this from somewhere in the pg system tables as well? Queries like these are very common in most applications, so I'm hoping I can avoid the sequential scans! If you have a qualified count(*) it goes to the index first, than checks whether the rows are live for your transaction. The problem is only the unqualified count with select count(*) from table_name; without any qualification. Or, of course, if your qualifier is not selective enough and you get a couple of millions rows back from a slow IO system ... I try to do counts only if I know that the selectivity is good enough not to kill the performance. Or I use "pleas wait" pages in the my application to tell the user, that his request is being processed and not hung. cug ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Database Select Slow
In response to ".ep" <[EMAIL PROTECTED]>:
> On Aug 10, 9:42 pm, [EMAIL PROTECTED] ("A.
> Kretschmer") wrote:
> > am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes:
> >
> > > 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.
> >
> > Indexes don't help in this case, a 'select count(*)' forces a seq. scan.
> > Do you realy need this information? An estimate for the number of rows
> > can you find in the system catalog (reltuples in pg_class,
> > seehttp://www.postgresql.org/docs/current/interactive/catalog-pg-class.html)
>
>
>
> Hi, what if I need to do a count with a WHERE condition? E.g.,
>
> SELECT count(*) from customers where cust_id = 'georgebush' and
> created_on > current_date - interval '1 week' ;
>
> Can I get the info about this from somewhere in the pg system tables
> as well? Queries like these are very common in most applications, so
> I'm hoping I can avoid the sequential scans!
>
> Many thanks for any tips.
If you only need an estimate, you can do an "explain" of the query, and
grep out the row count. The accuracy of this will vary depending on the
statistics, but it's very fast and works with a query of any complexity.
If you need fast, accurate counts, your best bet is to set up triggers on
your tables to maintain counts in a separate table. This can be rather
complex to set up, and you take a performance hit during inserts and updates,
but I don't know of any other way to do it.
--
Bill Moran
http://www.potentialtech.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database Select Slow
On Aug 10, 9:42 pm, [EMAIL PROTECTED] ("A.
Kretschmer") wrote:
> am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes:
>
> > 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.
>
> Indexes don't help in this case, a 'select count(*)' forces a seq. scan.
> Do you realy need this information? An estimate for the number of rows
> can you find in the system catalog (reltuples in pg_class,
> seehttp://www.postgresql.org/docs/current/interactive/catalog-pg-class.html)
Hi, what if I need to do a count with a WHERE condition? E.g.,
SELECT count(*) from customers where cust_id = 'georgebush' and
created_on > current_date - interval '1 week' ;
Can I get the info about this from somewhere in the pg system tables
as well? Queries like these are very common in most applications, so
I'm hoping I can avoid the sequential scans!
Many thanks for any tips.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Database Select Slow
am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes: > 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. Indexes don't help in this case, a 'select count(*)' forces a seq. scan. Do you realy need this information? An estimate for the number of rows can you find in the system catalog (reltuples in pg_class, see http://www.postgresql.org/docs/current/interactive/catalog-pg-class.html) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Database Select Slow
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. All helps and advice are appreciated. Thanks. _ Check it out! Windows Live Spaces is here! http://spaces.live.com/?mkt=en-sg ItÂ’s easy to create your own personal Web site. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
