Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 777699F9EBB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 14 Sep 2007 07:26:09 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 01776-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 14 Sep 2007 07:25:58 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from rv-out-0910.google.com (rv-out-0910.google.com
 [209.85.198.187])
	by postgresql.org (Postfix) with ESMTP id CA28F9F9C15
	for <pgsql-sql@postgresql.org>; Fri, 14 Sep 2007 07:26:01 -0300 (ADT)
Received: by rv-out-0910.google.com with SMTP id l15so600512rvb
	for <pgsql-sql@postgresql.org>; Fri, 14 Sep 2007 03:26:00 -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=IOKwEFBwe4BbszagQg+GyciSGwkAgsTeXwMQq72Dxeg=;
	b=fOb/zDYrb1f896O/NigLChzI0RppjT5Q3O6pTHQL+ZtWdiEa1yGlff8UmS9IofVW53nXoBf3huAS2aIEdB6LFWeXWdKBXsMHnxVVo273VkvGgTQyyVxKZnUhKdESDv1RCckm28NyXrwnziK6gfqIr1M/X3O5f0un49x/M15YPkk=
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=GnSeOVNuLp6QD5h+z/fZqJT2ul/YUzA1/uZWWBCTizoBCnZdQUerDzORuibQXVuqbRi9owSPBE9gkgeAL4uCk5Z3BuLEanRDTP/k+XMIbltmPVzvf9OVLFGSx50ghM3+OPBQ6nK5QJyiy79ehIdBkrczG3hAPjI0x5DzZRxSYkk=
Received: by 10.143.12.19 with SMTP id p19mr365535wfi.1189765560311;
	Fri, 14 Sep 2007 03:26:00 -0700 (PDT)
Received: by 10.140.172.2 with HTTP; Fri, 14 Sep 2007 03:26:00 -0700 (PDT)
Message-ID: <bc40d0a30709140326m7210f895o5a5383c5670a9602@mail.gmail.com>
Date: Fri, 14 Sep 2007 12:26:00 +0200
From: "Stanislas de Larocque" <dllstan@gmail.com>
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Subject: Re: Optimize querry sql
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20070914084952.GB7443@a-kretschmer.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <bc40d0a30709140131u3f23c6e1h17b8bf1f96235a91@mail.gmail.com>
	<20070914084952.GB7443@a-kretschmer.de>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200709/106
X-Sequence-Number: 29319

Hi,

Explain my sql querry :

Limit  (cost=1057.15..1057.16 rows=1 width=27)
   ->  HashAggregate  (cost=1057.15..1057.16 rows=1 width=27)
         ->  Nested Loop  (cost=0.00..1057.14 rows=1 width=27)
               ->  Seq Scan on stat a  (cost=0.00..1042.98 rows=1 width=8)
                     Filter: (((month)::double precision =
date_part('month'::text, (now() - '1 mon'::interval))) AND
((year)::double precision = date_part('year'::text, (now() - '1
mon'::interval))))
               ->  Index Scan using resaller_pkey on revendeur b
(cost=0.00..14.15 rows=1 width=23)
                     Index Cond: ("outer".idxresaller = b.idxresaller)
                     Filter: (asp = 6)

I would optimize "Seq Scan on stat a  (cost=0.00..1042.98 rows=1 width=8)"

What is your advice ?

Thank you

Stan


2007/9/14, A. Kretschmer <andreas.kretschmer@schollglas.com>:
> am  Fri, dem 14.09.2007, um 10:31:39 +0200 mailte Stanislas de Larocque folgendes:
> > Hi,
> >
> > I want to optimize my qerry sql (execution time : 2448 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;
>
> Show us the output from EXLAIN ANALYSE <your query>.
>
> My guess: you need at least an index in reseller.asp. Why do you have
> columns such a.month and a.year? se a regular DATE or TIMESTAMPTZ field
> instead and an index on this.
> And use CURRENT_DATE instead now().
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>