Skip site navigation (1) Skip section navigation (2)

Speeding up aggregates

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Speeding up aggregates
Date: 2002-12-06 17:30:46
Message-ID: web-2024223@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-performance
Folks,

One of Postgres' poorest performing areas is aggregates.   This is the
unfortunate side effect of our fully extensible aggregate and type
system.   However, I thought that the folks on this list might have  a
few tips on making aggregates perform faster.

Here's mine:  Aggregate Caching Table

This is a brute-force approach.   However, if you have a table with a
million records for which users *frequently* ask for grand totals or
counts, it can work fine.

A simple example:

Table client_case_counts (
	client_id INT NOT NULL REFERENCES clients(client_id) ON DELETE
CASCADE;
	no_cases INT NOT NULL DEFAULT 0
);

Then create triggers:

Function tf_maintain_client_counts () 
returns opaque as '
BEGIN
UPDATE client_case_counts SET no_cases = no_cases + 1
WHERE client_id = NEW.client_id;
INSERT INTO client_case_counts ( client_id, no_cases )
VALUES ( NEW.client_id, 1 )
WHERE NOT EXISTS (SELECT client_id FROM client_case_counts ccc2
	WHERE ccc2.client_id = NEW.client_id);
RETURN NEW;
END;' LANGUAGE 'plpgsql';

Trigger tg_maintain_client_counts ON INSERT INTO cases
FOR EACH ROW EXECUTE tf_maintain_client_counts();
etc.

While effective, this approach is costly in terms of update/insert
processing.  It is also limited to whatever aggregate requests you have
anticipated ... it does no good for aggregates over a user-defined
range.

What have other Postgres users done to speed up aggregates on large
tables?

-Josh Berkus




	

Responses

pgsql-performance by date

Next:From: Joe ConwayDate: 2002-12-06 18:10:45
Subject: Re: Speeding up aggregates
Previous:From: john cartmellDate: 2002-12-06 11:32:04
Subject: Re: ORDER BY ... LIMIT.. performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group