Re: 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

+-----------------------------------------------------+
| Laurent Manchon |
| Email: lmanchon(at)univ-montp2(dot)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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of A. Kretschmer
Sent: Tuesday, January 23, 2007 6:17 AM
To: pgsql-general(at)postgresql(dot)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 are not 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 message from 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(at)winemantech(dot)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(at)wolff(dot)to>
said:
> On Tue, Jan 23, 2007 at 10:12:13 -0500,
> Brandon Aiken <BAiken(at)winemantech(dot)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(at)univ-montp2(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Tue, Jan 23, 2007 at 14:15:23 -0500,
Jeremy Haile <jhaile(at)fastmail(dot)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(at)postgresql(dot)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