From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: not using index for select min(...)
Date: Sat, 1 Feb 2003 11:41:42 -0800
Message-ID: <200302011141.42245.josh@agliodbs.com> (raw)
In-Reply-To: <20030201040954.GK15936@perrin.int.nxad.com>
References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com>
<200301311531.12605.josh@agliodbs.com>
<20030201040954.GK15936@perrin.int.nxad.com>
Sean,
> I've spent some time in the past thinking about this, and here's the
> best idea that I can come up with:
>
> Part one: setup an ALTER TABLE directive that allows for the
> addition/removal of cached aggregates. Ex:
Actually, Joe Conway and I may be working on something like this for a client.
Joe's idea is to use a hacked version of the statistics collector to cache
selected aggregate values in memory. These aggregates would be
non-persistent, but the main concern for us is having aggregate values that
are instantly accessable, and that don't increase the cost of INSERTS and
UPDATES more than 10%.
This is to satisfy the needs of a particular client, though, so it may never
make it into the general PostgreSQL source. We'll post it somewhere if it
works, though.
We already implemented caching aggregates to tables, with is trivially easy to
do with triggers. The problem with this approach is the
UPDATE/INSERT/DELETE overhead; even with an SPI-optimized C trigger, it's
costing us up to 40% additional time when under heavy write activity ...
which is exactly when we can't afford delays.
For a database which has a low level of UPDATE activity, though, you can
already implement cached aggregates as tables without inventing any new
Postgres extensions.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: not using index for select min(...)
In-Reply-To: <200302011141.42245.josh@agliodbs.com>
References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com> <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> <200302011141.42245.josh@agliodbs.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