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/

---------------------------(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

Reply via email to