pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (12+ messages)  latest in thread

Message-ID: <200302011141.42245.josh@agliodbs.com>
Permalink:  ../200302011141.42245.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200302011141.42245.josh@agliodbs.com

 · 

reply

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