From: Gregory Stark <stark@enterprisedb.com>
To: Palle Girgensohn <girgen@pingpong.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: select max(field) from table much faster with a group by clause?
Date: Thu, 01 Nov 2007 15:47:32 +0000
Message-ID: <873avqrmqz.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <DFCABF1E9EE0F7A9D44CA50A@rambutan.pingpong.net>
References: <D26563C7B9CCEB0A338FF08E@rambutan.pingpong.net>
<4305.1193924619@sss.pgh.pa.us>
<DFCABF1E9EE0F7A9D44CA50A@rambutan.pingpong.net>
"Palle Girgensohn" <girgen@pingpong.net> writes:
> Are there any other things I can modify?
You might consider an index on <userid,when>. Keep in mind that every new
index imposes an incremental cost on every update and insert and increases the
time for vacuum.
> max_prepared_transactions = 100 # can be 0 or more
Are you actually using prepared transactions (are you synchronising multiple
databases using a transaction manager)? If not then set this to 0 as it takes
some resources.
> maintenance_work_mem = 16384 # min 1024, size in KB
Raising this might decrease vacuum times if that's a problem.
> fsync = off
You realize that this means if the system loses power or the kernel crashes
you could have data corruption? Do you take very frequent backups or can you
reconstruct your data?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, girgen@pingpong.net, tgl@sss.pgh.pa.us
Subject: Re: select max(field) from table much faster with a group by clause?
In-Reply-To: <873avqrmqz.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox