Date: Sun, 23 Oct 2005 18:55:00 +0200
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: Kishore B <kishorebh@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Need help in setting optimal configuration for a huge
Message-ID: <20051023165500.GA15053@samfundet.no>
References: <42567e060510221415r2516523l4c3f9b42dee91a09@mail.gmail.com>
	<435BBAF0.8080500@modgraph-usa.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <435BBAF0.8080500@modgraph-usa.com>
User-Agent: Mutt/1.5.9i

On Sun, Oct 23, 2005 at 09:31:44AM -0700, Craig A. James wrote:
> COUNT() -- There is no good substitute.  What I do is create a new column, 
> "ROW_NUM" with an auto-incrementing sequence.  Every time I insert a row, 
> it gets a new value.  Unfortunately, this doesn't work if you ever delete a 
> row.  The alternative is a more complex pair of triggers, one for insert 
> and one for delete, that maintains the count in a separate one-row table.  
> It's a nuisance, but it's a lot faster than doing a full table scan for 
> every COUNT().

This will sometimes give you wrong results if your transactions ever roll
back, for instance. The correct way to do it is to maintain a table of
deltas, and insert a new positive record every time you insert rows, and a
negative one every time you delete them (using a trigger, of course). Then
you can query it for SUM(). (To keep the table small, run a SUM() in a cron
job or such to combine the deltas.)

There has, IIRC, been talks of supporting fast (index-only) scans on
read-only (ie. archived) partitions of tables, but it doesn't look like this
is coming in the immediate future. I guess others know more than me here :-)

> MIN() and MAX() -- These are surprisingly slow, because they seem to do a 
> full table scan EVEN ON AN INDEXED COLUMN!  I don't understand why, but 
> happily there is an effective substitute:

They are slow because PostgreSQL has generalized aggregates, ie. MAX() gets
fed exactly the same data as SUM() would. PostgreSQL 8.1 (soon-to-be
released) can rewrite a MAX() or MIN() to an appropriate LIMIT form, though,
which solves the problem.

/* Steinar */
-- 
Homepage: http://www.sesse.net/