Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 184169FA687 for ; Fri, 14 Sep 2007 10:36:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 74294-06 for ; Fri, 14 Sep 2007 10:36:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from depesz.com (depesz.com [88.198.37.40]) by postgresql.org (Postfix) with ESMTP id A43909FA65E for ; Fri, 14 Sep 2007 10:36:43 -0300 (ADT) Received: from depesz by depesz.com with local (Exim 4.63) (envelope-from ) id 1IWBLd-0008M8-N0; Fri, 14 Sep 2007 15:36:41 +0200 Date: Fri, 14 Sep 2007 15:36:41 +0200 From: hubert depesz lubaczewski To: Stanislas de Larocque Cc: pgsql-sql@postgresql.org Subject: Re: Optimize querry sql Message-ID: <20070914133641.GA31759@depesz.com> Reply-To: depesz@depesz.com References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/110 X-Sequence-Number: 29323 On Fri, Sep 14, 2007 at 03:02:59PM +0200, Stanislas de Larocque wrote: > I want to optimize my query sql (execution time : 2665 ms) : SELECT b.idxreseller, sum(a.nbrq), b.namereseller from stat a, reseller b where b.asp=6 and a.idxreseller=b.reseller and a.month=date_part('month',now() - interval '1 month') and a.year=date_part('year',now() - interval '1 month') GROUP BY b.idxreseller,b.namereseller limit 15; 1. cast all date_parts to int4, like in: a.month = cast( date_part('month',now() - interval '1 month') as int4) 2. why there is a limit without any order by? 3. change it to get namereseller from subselect, not from join. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)