Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 185AF9F9C66 for ; Mon, 17 Sep 2007 04:18:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36865-01 for ; Mon, 17 Sep 2007 04:17:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.235]) by postgresql.org (Postfix) with ESMTP id ED35C9F9C64 for ; Mon, 17 Sep 2007 04:17:58 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id r21so1224807wxc for ; Mon, 17 Sep 2007 00:17:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=D9LE0TUQf58hyhQu0BPH/9FZPbyJQnCzzcEY1VFGvo8=; b=Ij147JLOuQ+pFyB4ZDq9SKoozhOS+AZSeShMo7be0fivWnSSzvnFAhTNOZ3azuKUBnUWRx5by4QrYUSAFXe9iOi2zJ3ifjUnxxiDdLUoWzJSzVi0KrLjBLSwOS+0biRi2QVAu+N1lfT2SM0TE98lpjbV7pW1xyt2ZGCaKIzqHs0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=RoNvjszLdQJmglJR/TTi0DGN0/QuMfzrEZ2288qX6aQAHC7mAElBVKyVeMSMgt1FK2AD0N5Yw7pVDODiQ4FdNoYDzM3XwlMBBTDU780PPGfoW1RRzTgCqjDuydPisw7QlsJnUKYDwR4EO3NqNkb3W1++cN8olveawfTK14yIlK0= Received: by 10.90.52.18 with SMTP id z18mr2789726agz.1190013478173; Mon, 17 Sep 2007 00:17:58 -0700 (PDT) Received: by 10.90.70.5 with HTTP; Mon, 17 Sep 2007 00:17:58 -0700 (PDT) Message-ID: Date: Mon, 17 Sep 2007 09:17:58 +0200 From: "Stanislas de Larocque" To: depesz@depesz.com Subject: Re: Optimize querry sql Cc: pgsql-sql@postgresql.org In-Reply-To: <20070914133641.GA31759@depesz.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070914133641.GA31759@depesz.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/123 X-Sequence-Number: 29336 Hi, I deleted the fonction date_part and now the time of the querry is : 464 ms !!! Limit (cost=1373.32..1373.50 rows=15 width=27) (actual time=463.762..463.857 rows=15 loops=1) -> HashAggregate (cost=1373.32..1408.52 rows=2816 width=27) (actual time=463.755..463.820 rows=15 loops=1) -> Hash Join (cost=149.72..1189.22 rows=24546 width=27) (actual time=42.106..348.561 rows=18543 loops=1) Hash Cond: ("outer".idxreseller = "inner".idxreseller) -> Seq Scan on stat a (cost=0.00..545.27 rows=24877 width=8) (actual time=0.054..167.340 rows=24881 loops=1) Filter: ((month = 8) AND (year = 2007)) -> Hash (cost=142.68..142.68 rows=2816 width=23) (actual time=41.954..41.954 rows=2816 loops=1) -> Seq Scan on reseller b (cost=0.00..142.68 rows=2816 width=23) (actual time=0.035..28.447 rows=2816 loops=1) Filter: (asp = 6) Total runtime: 464.337 ms Have you advices to optimize the query please ? Stan 2007/9/14, hubert depesz lubaczewski : > 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) > -- Stanislas de Larocque dllstan@gmail.com 06 63 64 00 47