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 7CEAAD1B506 for ; Fri, 3 Oct 2003 21:29:37 +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 47510-10 for ; Fri, 3 Oct 2003 18:28:49 -0300 (ADT) Received: from rlx13.zapatec.com (66-117-144-213.zapatec.lmi.net [66.117.144.213]) by svr1.postgresql.org (Postfix) with ESMTP id DA584D1B500 for ; Fri, 3 Oct 2003 18:28:46 -0300 (ADT) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by rlx13.zapatec.com (Postfix) with ESMTP id 20A74ACAC for ; Fri, 3 Oct 2003 21:28:48 +0000 (GMT) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h93LSmE7097315 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 14:28:48 -0700 (PDT) (envelope-from dror) Date: Fri, 3 Oct 2003 14:28:48 -0700 From: Dror Matalon To: pgsql-performance@postgresql.org Subject: Re: Speeding up Aggregates Message-ID: <20031003212848.GP87525@rlx11.zapatec.com> References: <20031003202120.GO87525@rlx11.zapatec.com> <200310031407.10343.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200310031407.10343.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/108 X-Sequence-Number: 3856 Hi Josh, On Fri, Oct 03, 2003 at 02:07:10PM -0700, Josh Berkus wrote: > Dror, > > > select articlenumber, channel, description, title, link, dtstamp from > > items, my_channels where items.channel = '22222' and my_channels.id = > > '22222' and owner = 'drormata' and dtstamp > last_viewed and > > articlenumber not in (select item from viewed_items where channel > > ='22222' and owner = 'drormata'); > > the NOT IN is a bad idea unless the subselect never returns more than a > handful of rows. If viewed_items can grow to dozens of rows, wyou should > use WHERE NOT EXISTS instead. Unless you're using 7.4. > I am using 7.4, and had tried NOT EXISTS and didn't see any improvements. > > item_max_date() looks like this: > > select max(dtstamp) from items where channel = $1 and link = $2; > > Change it to > > SELECT dtstamp from iterm where channel = $1 and link = $2 > ORDER BY dtstamp DESC LIMIT 1 > Didn't make a difference. And plugging real values into this query as well as into the original select max(dtstamp) from items where channel = $1 and link = $2; and doing an explain analyze shows that the cost is the same. The strange things is that when I run the above queries by hand they take about .5 msec. Yet on a resultset that fetches 5 rows, I go up from 15 msec to 300 msec. It would seem like it should be something like 15 + (0.5 * 5) + small overhead, = 30 msec or so rather than the 300 I'm seeing. > and possibly build an index on channel, link, dtstamp Didn't make a difference either. Explain analyze shows that it didn't use it. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com