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 16DDFD1B53C for ; Fri, 3 Oct 2003 21:09:52 +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 42799-10 for ; Fri, 3 Oct 2003 18:09:04 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 6E38AD1B55A for ; Fri, 3 Oct 2003 18:09:02 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3719664; Fri, 03 Oct 2003 14:09:38 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Dror Matalon , pgsql-performance@postgresql.org Subject: Re: Speeding up Aggregates Date: Fri, 3 Oct 2003 14:07:10 -0700 User-Agent: KMail/1.4.3 References: <20031003202120.GO87525@rlx11.zapatec.com> In-Reply-To: <20031003202120.GO87525@rlx11.zapatec.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310031407.10343.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/107 X-Sequence-Number: 3855 Dror, > select articlenumber, channel, description, title, link, dtstamp from > items, my_channels where items.channel =3D '22222' and my_channels.id = =3D > '22222' and owner =3D 'drormata' and dtstamp > last_viewed and > articlenumber not in (select item from viewed_items where channel > =3D'22222' and owner =3D 'drormata'); the NOT IN is a bad idea unless the subselect never returns more than a=20 handful of rows. If viewed_items can grow to dozens of rows, wyou should= =20 use WHERE NOT EXISTS instead. Unless you're using 7.4. > item_max_date() looks like this: > select max(dtstamp) from items where channel =3D $1 and link =3D $2; Change it to=20 SELECT dtstamp from iterm where channel =3D $1 and link =3D $2 ORDER BY dtstamp DESC LIMIT 1 and possibly build an index on channel, link, dtstamp --=20 -Josh Berkus Aglio Database Solutions San Francisco