pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean Chittenden <sean@chittenden.org>
To: Josh Berkus <josh@agliodbs.com>
Cc: Don Bowman <don@sandvine.com>
Cc: 'pgsql-hackers@postgresql.org' <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] not using index for select min(...)
Date: Fri, 31 Jan 2003 20:09:54 -0800
Message-ID: <20030201040954.GK15936@perrin.int.nxad.com> (raw)
In-Reply-To: <200301311531.12605.josh@agliodbs.com>
References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com>
	<200301311531.12605.josh@agliodbs.com>

> > I have a table which is very large (~65K rows). I have
> > a column in it which is indexed, and I wish to use for
> > a join. I'm finding that I'm using a sequential scan
> > for this when selecting a MIN.
> 
> Due to Postgres' system of extensible aggregates (i.e. you can write
> your own aggregates), all aggregates will trigger a Seq Scan in a
> query.  It's a known drawrback that nobody has yet found a good way
> around.

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:

  ALTER TABLE tab1 ADD AGGREGATE CACHE ON count(*);
  ALTER TABLE tab1 ADD AGGREGATE CACHE ON sum(col2);
  ALTER TABLE tab1 ADD AGGREGATE CACHE ON sum(col2) WHERE col2 > 100;
  ALTER TABLE tab1 ADD AGGREGATE CACHE ON sum(col2) WHERE col2 <= 100;


Which would translate into some kind of action on a pg_aggregate_cache
catalog:

aggregate_cache_oid	  OID   -- OID for the aggregate cache
aggregate_table_oid	  OID   -- table OID
ins_aggfn_oid		  OID	-- aggregate function id for inserts
upd_aggfn_oid		  OID	-- aggregate function id for updates
del_aggfn_oid		  OID	-- aggregate function id for deletes
cache_value		  INT	-- the value of the cache
private_data		  INT[4]   -- temporary data space for needed
				   -- data necessary to calculate cache_value
				   -- four is just a guesstimate for how much
				   -- space would be necessary to calculate
				   -- the most complex of aggregates
where_clause              ???   -- I haven't the faintest idea how to
				-- express some kind of conditional like this


Part two: setup a RULE or TRIGGER that runs on INSERT, UPDATE, or
DELETE.  For the count(*) exercise, the ON UPDATE would be a no-op.
For ON INSERT, the count(*) rule would have to do something like:

UPDATE pg_catalog.pg_aggregate_cache SET cached_value = (cached_value + 1)
       WHERE aggregate_cache_oid = 1111111;


For the sum(col2) aggregate cache, the math is a little more complex,
but I think it's quite reasonable given that it obviates a full table
scan.  For an insert:

UPDATE pg_catalog.pg_aggregate_cache SET cached_value =
       ((cached_value * private_data[0] + NEW.col2) / (private_data[0] + 1))
       WHERE aggregate_cache_oid = 1111112;


Now, there are some obvious problems:

1) avg requires a floating point return value, therefore an INT may
   not be an appropriate data type for cache_value or private_data.

2) aggregate caching wouldn't speed up anything but full table
   aggregates or regions of a column that are frequently needed.

3) all of the existing aggregates would have to be updated to include
   an insert, update, delete procedure (total of 60 aggregates, but
   only 7 by name).

4) the planner would have to be taught how to use/return values from
   the cache.

5) Each aggregate type makes use of the private_data column
   differently.  It's up to the cached aggregate function authors to
   not jumble up their private data space.

6) I don't know of a way to handle mixing of floating point numbers
   and integers.  That said, there's some margin of error that could
   creep into the floating point calculations such as avg.


And some benefits:

1) You only get caching for aggregates that you frequently use
   (sum(col2), count(*), etc.).

2) Aggregate function authors can write their own caching routines.

3) For tens of millions of rows, it can be very time consuming to
   sum() fifty million rows, but it's easy to amortize the cost of
   updating the cache on insert, update, delete over the course of a
   month.

4) If an aggregate cache definition isn't setup, it should be easy for
   the planner to fall back to a full table scan, as it currently is.


This definitely would be a performance boost and something that would
only be taken advantage of by DBAs that are intentionally performance
tuning their database, but for those that do, it could be a massive
win.  Thoughts?  -sc

-- 
Sean Chittenden



view thread (12+ messages)  latest in thread

Message-ID: <20030201040954.GK15936@perrin.int.nxad.com>
Permalink:  ../20030201040954.GK15936@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030201040954.GK15936@perrin.int.nxad.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: sean@chittenden.org, josh@agliodbs.com, don@sandvine.com, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] not using index for select min(...)
  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>

* 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