Subject: Re: join optimization problem
From: Rod Taylor <rbt@rbt.ca>
To: toby@lyricist.com.au
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200308032021.45212.toby@lyricist.com.au>
References: <200307281412.36981.toby@lyricist.com.au>
	<1059698003.33557.5.camel@jester>
	<200308032021.45212.toby@lyricist.com.au>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-iqnQkHOvykGDe6uQ/Axm"
Message-Id: <1059915851.43336.208.camel@jester>
Mime-Version: 1.0
Date: 03 Aug 2003 09:04:11 -0400

--=-iqnQkHOvykGDe6uQ/Axm
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> 	thanks for that - I tried it out, fixed the minor typos and it ran at al=
most=20
> the same speed!  Any other ideas?

Could you post an explain analyze for both queries?


There is always an application change.  Add a trigger onto msg that will
bump the date and count on msg_sum when a msg is added.  This will
support virtually unlimited msg table growth (any aggregate will
eventually be slow on a large table).

Just be sure to vacuum the msg_sum table regularly (once per 25%
turnover) -- possibly as often as once a minute!


--=-iqnQkHOvykGDe6uQ/Axm
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/LQhK6DETLow6vwwRAokkAJwKDkPwi1OklHRyiylkfZ9rvofF1ACfWnvT
qXPbE2Zor7nBB/frVpRS5Xg=
=gbe0
-----END PGP SIGNATURE-----

--=-iqnQkHOvykGDe6uQ/Axm--