Thread: Postgresql.conf
Hi,
I have a slow response of my PostgreSQL database 7.4 using this query below
on a table with 800000 rows:
select count(*)from tbl;
PostgreSQL return result in 28 sec every time.
although MS-SQL return result in 0.02 sec every time.
My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
with 3GBytes RAM
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 50000
sort_mem = 10000
max_connections = 200
shared_buffers = 3000
vacuum_mem = 32000
wal_buffers = 8
max_fsm_pages = 2000
max_fsm_relations = 100
Can you tell me is there a way to increase performance ?
Thank you
I have a slow response of my PostgreSQL database 7.4 using this query below
on a table with 800000 rows:
select count(*)from tbl;
PostgreSQL return result in 28 sec every time.
although MS-SQL return result in 0.02 sec every time.
My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
with 3GBytes RAM
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 50000
sort_mem = 10000
max_connections = 200
shared_buffers = 3000
vacuum_mem = 32000
wal_buffers = 8
max_fsm_pages = 2000
max_fsm_relations = 100
Can you tell me is there a way to increase performance ?
Thank you
+-----------------------------------------------------+
| Laurent Manchon |
| Email: lmanchon@univ-montp2.fr |
+-----------------------------------------------------+
am Tue, dem 23.01.2007, um 12:11:40 +0100 mailte Laurent Manchon folgendes: > Hi, > > I have a slow response of my PostgreSQL database 7.4 using this query below > on a table with 800000 rows: > > select count(*)from tbl; How often do you want to ask the very same question? You have enough answers, read this! Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Out of curiosity, has the COUNT(*) with no WHERE clause slowness been fixed in 8.x? Or is it still an issue of "there's no solution that won't harm aggregates with WHERE clauses"? -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer Sent: Tuesday, January 23, 2007 6:17 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgresql.conf am Tue, dem 23.01.2007, um 12:11:40 +0100 mailte Laurent Manchon folgendes: > Hi, > > I have a slow response of my PostgreSQL database 7.4 using this query below > on a table with 800000 rows: > > select count(*)from tbl; How often do you want to ask the very same question? You have enough answers, read this! 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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -------------------------------------------------------------------- ** LEGAL DISCLAIMER ** Statements made in this email may or may not reflect the views and opinions of Wineman Technology, Inc. This E-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you arenot the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s),you are hereby notified that any dissemination, distribution or copying of this E-mail message is strictly prohibited.If you have received this message in error, please immediately notify the sender and delete this E-mail messagefrom your computer. QS Disclaimer Demo. Copyright (C) Pa-software. Visit www.pa-software.com for more information.
am Tue, dem 23.01.2007, um 10:12:13 -0500 mailte Brandon Aiken folgendes: > Out of curiosity, has the COUNT(*) with no WHERE clause slowness been > fixed in 8.x? Or is it still an issue of "there's no solution that > won't harm aggregates with WHERE clauses"? I will try it: scholl=# \timing Timing is on. scholl=# select count(1) from bde_meldungen ; count --------- 1813210 (1 row) Time: 1925.471 ms scholl=*# select count(1) from bde_meldungen where datum = current_date-'1day'::interval; count ------- 2694 (1 row) Time: 5.670 ms Btw: yes, the table has more rows than the table from the origin poster and the count(1) is much faster. Perhaps he should show us an 'explain analyse'. My guess: many dead tuples. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer schrieb:
> am Tue, dem 23.01.2007, um 10:12:13 -0500 mailte Brandon Aiken folgendes:
>> Out of curiosity, has the COUNT(*) with no WHERE clause slowness been
>> fixed in 8.x? Or is it still an issue of "there's no solution that
>> won't harm aggregates with WHERE clauses"?
>
> I will try it:
>
> scholl=# \timing
> Timing is on.
> scholl=# select count(1) from bde_meldungen ;
^^^^^^^^^^^^^^
eeeek localized object names ;)))
Tino
On Tue, Jan 23, 2007 at 10:12:13 -0500, Brandon Aiken <BAiken@winemantech.com> wrote: > Out of curiosity, has the COUNT(*) with no WHERE clause slowness been > fixed in 8.x? Or is it still an issue of "there's no solution that > won't harm aggregates with WHERE clauses"? Probably not in the sense that you mean. The underlying problem is that in MVCC there is no single global answer to the question and the pain of maintaining the mutliple answers outweighs the cost of doing so in normal usage. People that need to run count(*) queries a lot may want to make a different trade off and some ways of maintaining counts are covered in the archives.
But there are ways that we could optimize count(*) queries for specific circumstances right? Obviously this isn't trivial, but I think it would be nice if we could maintain a number of rows count that could be used when performing a count(*) on the whole table (no where clause). I don't know if the overhead of keeping track of that number is worth the benefits - but I know that querying for the number of rows in a table is a common need and other RDBMSs do optimize for that special case. On Tue, 23 Jan 2007 12:53:43 -0600, "Bruno Wolff III" <bruno@wolff.to> said: > On Tue, Jan 23, 2007 at 10:12:13 -0500, > Brandon Aiken <BAiken@winemantech.com> wrote: > > Out of curiosity, has the COUNT(*) with no WHERE clause slowness been > > fixed in 8.x? Or is it still an issue of "there's no solution that > > won't harm aggregates with WHERE clauses"? > > Probably not in the sense that you mean. > > The underlying problem is that in MVCC there is no single global answer > to the question and the pain of maintaining the mutliple answers > outweighs > the cost of doing so in normal usage. > > People that need to run count(*) queries a lot may want to make a > different > trade off and some ways of maintaining counts are covered in the > archives. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
On 1/23/07, Laurent Manchon <lmanchon@univ-montp2.fr> wrote: > Hi, > > I have a slow response of my PostgreSQL database 7.4 using this query below > on a table with 800000 rows: > > select count(*)from tbl; > > PostgreSQL return result in 28 sec every time. > although MS-SQL return result in 0.02 sec every time. > > My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz > with 3GBytes RAM if you need a fast approximate answer (up to date as of last analyze), you can do something like: select reltuples from pg_class where relname = 'tbl' and relkind = 'r'; if you need a fast exact answer, you need to write a trigger. merlin
On Tue, Jan 23, 2007 at 02:15:23PM -0500, Jeremy Haile wrote: > But there are ways that we could optimize count(*) queries for specific > circumstances right? Obviously this isn't trivial, but I think it would > be nice if we could maintain a number of rows count that could be used > when performing a count(*) on the whole table (no where clause). Not really. SQL has fairly strict specifications to the answer to that query and anything that would optimise it comes at a not inconsiderable cost. If you don't care about an exact answer, you can find a number of methods in the archives. > I don't know if the overhead of keeping track of that number is worth > the benefits - but I know that querying for the number of rows in a > table is a common need and other RDBMSs do optimize for that special > case. It's not just keeping track of the number of rows. It keeping track of the number of rows for each currently executing transaction, since each transaction could get a different answer. So any accurate method is going to be tracking the number of tuples even for transactions that don't want to know. For people who really want to spend the overhead, you can make a working system. But most people can live with estimates... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Tue, Jan 23, 2007 at 14:15:23 -0500, Jeremy Haile <jhaile@fastmail.fm> wrote: > But there are ways that we could optimize count(*) queries for specific > circumstances right? Obviously this isn't trivial, but I think it would > be nice if we could maintain a number of rows count that could be used > when performing a count(*) on the whole table (no where clause). People can already do that. How to do it right (to avoid update contention) is even described in the mailing list archives. There just isn't a nice contrib or pgfoundry project to wrap it up for them. Of course if there was people might install the project even though there was a net loss in performance for them. > I don't know if the overhead of keeping track of that number is worth > the benefits - but I know that querying for the number of rows in a > table is a common need and other RDBMSs do optimize for that special > case. That is debatable. Certainly a lot of people run adhoc unconstrained count(*) queries. Whether they normally need exact counts or whether the number of such queries is large enough compared to other queries being done to be considered common is another matter.
Andreas, Would you mind explaining what you mean by "localized object names" and why it might be bad? Or where I might go to learn more? Thanks, -Ben On Tuesday 23 January 2007 07:38, Tino Wildenhain wrote: > A. Kretschmer schrieb: > > am Tue, dem 23.01.2007, um 10:12:13 -0500 mailte Brandon Aiken folgendes: > >> Out of curiosity, has the COUNT(*) with no WHERE clause slowness been > >> fixed in 8.x? Or is it still an issue of "there's no solution that > >> won't harm aggregates with WHERE clauses"? > > > > I will try it: > > > > scholl=# \timing > > Timing is on. > > scholl=# select count(1) from bde_meldungen ; > > ^^^^^^^^^^^^^^ > eeeek localized object names ;))) > > Tino > > ---------------------------(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 > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- "I kept looking around for somebody to solve the problem. Then I realized I am somebody" -Anonymous
am Tue, dem 23.01.2007, um 20:48:28 -0800 mailte Benjamin Smith folgendes: > Andreas, > > Would you mind explaining what you mean by "localized object names" and why it > might be bad? Or where I might go to learn more? > > Thanks, Tino wrote this ;-) Btw.: Fullquote below make its harder to understand what do you meen. > > -Ben > > On Tuesday 23 January 2007 07:38, Tino Wildenhain wrote: > > A. Kretschmer schrieb: > > > am Tue, dem 23.01.2007, um 10:12:13 -0500 mailte Brandon Aiken folgendes: > > >> Out of curiosity, has the COUNT(*) with no WHERE clause slowness been > > >> fixed in 8.x? Or is it still an issue of "there's no solution that > > >> won't harm aggregates with WHERE clauses"? > > > > > > I will try it: > > > > > > scholl=# \timing > > > Timing is on. > > > scholl=# select count(1) from bde_meldungen ; > > > > ^^^^^^^^^^^^^^ > > eeeek localized object names ;))) > > > > Tino As I said, Tino wrote this, ask him, not me. (But I think I know what he means...) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net