Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 193189F9B04 for ; Fri, 14 Sep 2007 10:03:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 42227-07 for ; Fri, 14 Sep 2007 10:03:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by postgresql.org (Postfix) with ESMTP id 29D789F9AFF for ; Fri, 14 Sep 2007 10:03:55 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id u77so1877018pyb for ; Fri, 14 Sep 2007 06:03:53 -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:mime-version:content-type:content-transfer-encoding:content-disposition; bh=w2AA77kY7LQEcGQQhRRnkKH0ob/ViCz8xhFb2c4qCMg=; b=SA2+llPuOGrNpMPXyeftMLKodYtjzR+kCPcEJOIFddXXUZgJ2hwsvLG/MWv4UIL9MpxS1PJAYaoRajYJOHTxBpbq4g4k5/+mXWJUpS78zRTs48PU6QNLrGc3NFDMxow34CC9G4q7Jiv6pV1cXBS//uV5ILcE8phFqwRsKAv5OBk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=c5cGpE1KEc0SQK1jJIEE2kxFIqh8e460l9WhtvHWdQ+rKnUqcW4hBUnr4H5964bi3bKdGszMUV86wo6WkA4UHoOd+H/3jL0O+W7JdAgT1JqC2WWwifn5IoYHFCba8RxwM7I/zerWqi/6ISiSJW+UQVfMGwgXTBRjDIdslyDVsew= Received: by 10.35.121.12 with SMTP id y12mr2235889pym.1189775033501; Fri, 14 Sep 2007 06:03:53 -0700 (PDT) Received: by 10.90.70.5 with HTTP; Fri, 14 Sep 2007 06:02:59 -0700 (PDT) Message-ID: Date: Fri, 14 Sep 2007 15:02:59 +0200 From: "Stanislas de Larocque" To: pgsql-sql@postgresql.org Subject: Optimize querry sql MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/109 X-Sequence-Number: 29322 Hi, I had errors in my last emails. sorry 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=3D6 and a.idxreseller=3Db.reseller and a.month=3Ddate_part('month',now() - interval '1 month') and a.year=3Ddate_part('year',now() - interval '1 month') GROUP BY b.idxreseller,b.namereseller limit 15; Explain analyse : Limit (cost=3D1057.15..1057.16 rows=3D1 width=3D27) (actual time=3D2655.083..2655.176 rows=3D15 loops=3D1) -> HashAggregate (cost=3D1057.15..1057.16 rows=3D1 width=3D27) (actual time=3D2655.074..2655.132 rows=3D15 loops=3D1) -> Nested Loop (cost=3D0.00..1057.14 rows=3D1 width=3D27) (actua= l time=3D0.646..2464.563 rows=3D18543 loops=3D1) -> Seq Scan on stat a (cost=3D0.00..1042.98 rows=3D1 width=3D8) (actual time=3D0.273..1239.510 rows=3D24881 loops=3D1) Filter: (((month)::double precision =3D date_part('month'::text, (now() - '1 mon'::interval))) AND ((year)::double precision =3D date_part('year'::text, (now() - '1 mon'::interval)))) -> Index Scan using reseller_pkey on reseller b (cost=3D0.00..14.15 rows=3D1 width=3D23) (actual time=3D0.034..0.038 rows= =3D1 loops=3D24881) Index Cond: ("outer".idxrreseller =3D b.idxreseller) Filter: (asp =3D 6) Total runtime: 2655.713 ms dns=3D> \d stat; Table =ABpublic.stat=BB idxreseller | integer | not null idxdo | integer | not null idxd | integer | not null nbrq | integer | default 0 month | integer | default date_part('month'::text, (now() - '1 mon'::interval)) year | integer | default date_part('year'::text, (now() - '1 mon'::interval)) Index : =ABstat_dns_domaine_idx_idxr_idxreseller=BB btree (dxreseller) =ABstat_dns_domaine_idx_month_year_idxres=BB btree (month, year, idxrese= ller) \d reseller; Table =ABpublic.reseller=BB idxreseller | integer | not null default nextval(('idxrevendeur_seq'::text)::regclass) namereseller | text | asp | integer | Index : =ABreseller_pkey=BB PRIMARY KEY, btree (idxreseller) Have you advices ? Thank you Stan