X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 22C87D1B506 for ; Fri, 3 Oct 2003 22:10:34 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 55998-04 for ; Fri, 3 Oct 2003 19:09:47 -0300 (ADT) Received: from mail.hive.nj2.inquent.com (mc.carriermail.com [205.178.180.9]) by svr1.postgresql.org (Postfix) with SMTP id 2E115D1B514 for ; Fri, 3 Oct 2003 19:09:44 -0300 (ADT) Received: (qmail 24494 invoked from network); 3 Oct 2003 22:09:11 -0000 Received: from unknown (HELO ?192.168.1.199?) (134.22.136.235) by 205.178.180.9 with SMTP; 3 Oct 2003 22:09:11 -0000 Subject: Re: Speeding up Aggregates From: Rod Taylor To: Dror Matalon Cc: Postgresql Performance In-Reply-To: <20031003215347.GQ87525@rlx11.zapatec.com> References: <20031003202120.GO87525@rlx11.zapatec.com> <1065217488.91586.3.camel@jester> <20031003215347.GQ87525@rlx11.zapatec.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-jkZdsfcTRaSOAH4mQj0+" Message-Id: <1065219028.91586.8.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Fri, 03 Oct 2003 18:10:29 -0400 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/115 X-Sequence-Number: 3863 --=-jkZdsfcTRaSOAH4mQj0+ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Fri, 2003-10-03 at 17:53, Dror Matalon wrote: > On Fri, Oct 03, 2003 at 05:44:49PM -0400, Rod Taylor wrote: > > > item_max_date() looks like this: > > > select max(dtstamp) from items where channel =3D $1 and link =3D $= 2; > >=20 > > It is too bad the (channel, link) index doesn't have dtstamp at the end > > of it, otherwise the below query would be a gain (might be a small one > > anyway). > >=20 > > select dtstamp > > from items > > where channel =3D $1 > > and link =3D $2 > > ORDER BY dtstamp DESC > > LIMIT 1; It didn't make a difference even with the 3 term index? I guess you don't have very many common values for channel / link combination. How about the below? Note the word STABLE on the end. CREATE or REPLACE FUNCTION item_max_date (int4, varchar) RETURNS timestamptz AS ' select max(dtstamp) from items where channel =3D $1 and link =3D $2; ' LANGUAGE 'sql' STABLE; --=-jkZdsfcTRaSOAH4mQj0+ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQA/ffPU6DETLow6vwwRAqqoAJ98/xpgZnGCWs9w5LVIvOWCWeqbOQCfSvp5 vGPd63zUg9xdu4dKuawyPb8= =LuwE -----END PGP SIGNATURE----- --=-jkZdsfcTRaSOAH4mQj0+--