Message-ID: <4B83664D.3030401@joeconway.com>
Date: Mon, 22 Feb 2010 21:23:25 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.12) Gecko/20071019 Fedora/1.5.0.12-3.fc6 pango-text
	Thunderbird/1.5.0.12 Mnenhy/0.7.5.666
MIME-Version: 1.0
To: Dave Crooke <dcrooke@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Advice requested on structuring aggregation queries
References: <ca24673e1002221901s12c44a38t9a64fdde6b604cbb@mail.gmail.com>
In-Reply-To: <ca24673e1002221901s12c44a38t9a64fdde6b604cbb@mail.gmail.com>
OpenPGP: id=B699434A
Content-Type: multipart/signed; micalg=pgp-sha256;
	protocol="application/pgp-signature";
	boundary="------------enigE04AA9CB866EC8CCB56E32CE"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigE04AA9CB866EC8CCB56E32CE
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On 02/22/2010 07:01 PM, Dave Crooke wrote:
> The original data is keyed by a 3-ary tuple of strings .... to keep the=

> row size down, in the new data model I'm actually storing 32-bit int's
> in Postgres. The new schema for each table looks like this:
>=20
> (a integer,
>  b integer,
>  c integer,
>  ts timestamp without timezone,
>  value double precision)
>=20
> with two indexes: (a, b, ts) and (b, ts)

[...snip...]

> There are about 60 different values of b, and for each such value there=

> is a exactly one type of rollup. The old code is doing the rollups in
> Postgres with 60 bulk "insert into .... select" statements, hence the
> need for the second index.

[...snip...]

> For better scalability, I am partitioning these tables by time .... I a=
m
> not using PG's table inheritance and triggers to do the partitioning,
> but instead dynamically generating the SQL and table names in the
> application code (Java). In most cases, the rollups will still happen
> from a single source "data_table" and I plan to continue using the
> existing SQL, but I have a few cases where the source "data_table" rows=

> may actually come from two adjacent tables.

Without going through your very long set of questions in detail, it
strikes me that you might be better off if you:

1) use PostgreSQL partitioning (constraint exclusion)
2) partition by ts range
3) consider also including b in your partitioning scheme
4) create one index as (ts, a)
5) use dynamically generated SQL and table names in the application
   code to create (conditionally) and load the tables

But of course test both this and your proposed method and compare ;-)

Also you might consider PL/R for some of your analysis (e.g. mode would
be simple, but perhaps not as fast):
  http://www.joeconway.com/web/guest/pl/r

HTH,

Joe


--------------enigE04AA9CB866EC8CCB56E32CE
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iQIcBAEBCAAGBQJLg2ZNAAoJEDfy90M199hlgFcP/i9GgdVb9ZkweF25uf2fE1Ih
ORWFUkVCeOZ89wCjEm1tQLIdCVI9q6DUmYWjLjS8Mx0MFrNwQ4dWB4nDPpaIcK83
rFbxMeQTv/arJ8hbCEnX28YOwKSuQNfJd6PMKjE408saGiHUdLa5ub9ERwUOQv9J
2Coa5nS09cK2ehpkmUoqWlVLpje5IF7kXGz/Y+eOQadEawj7od3JZpluc5cc2WG/
K6HBwNZUm9S5r0iBpM9VBBoFcd31QXoaQZpjELeKzwEAYQqhnGeGwstv/USsESLj
1jwdzXg3/X7re3ZDUkrrryzBnv/FRCA9xXE86DxJMwqbD5joYZCBPa7Pa2/vQ19z
2xPYN9DAc7AFpc7FT7XxpJ6OjWS6aKux6Z6Xk9MXSM7xznn4UexNHxC8RQjsANWw
EadbETGXtUpX5oe9QopdjDMiOLj2/A9RM5nFZWqhHZYTVXC78piPZWwUOgKGKUx9
LPY2KPCCX+upWx/GlvJQDA3LdD3AMqzp87ho4Rz0bUiA0dqVHZueKz2hkONhPjQ9
0V5nLTBtb87ybKeKjd89yHCVmEjuE3rAcCta/WE+nUsDz1L9K4j95soGL0VjaPdI
zUm7sjykXzo0oIPrBUSvBwVuGcKnRYRwkq1699qpCZ23OLCMN/ZbL+BhsPja4uHm
ntNxMdjMxeBmEaMm518b
=IBbL
-----END PGP SIGNATURE-----

--------------enigE04AA9CB866EC8CCB56E32CE--