X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B0B359DCAD1 for ; Wed, 22 Mar 2006 14:32:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11259-02 for ; Wed, 22 Mar 2006 14:32:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.198]) by postgresql.org (Postfix) with ESMTP id 13C2C9DCA83 for ; Wed, 22 Mar 2006 14:32:11 -0400 (AST) Received: by nproxy.gmail.com with SMTP id o60so166058nfa for ; Wed, 22 Mar 2006 10:32:11 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=googlemail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=BaZKG1Ku8ycLPUs048i92ow5D3sAnJ9cNemLPA5ynH9Za5MivCdQlYgTVzZNqNyb22li9FpjjREg4x4a/oRWQXq9O9vmEbLHSJsBVb4nktQYJE8OtgKH3H5xI4XZ3bFppfCoJIbAqKgMd7mMBAApslFdDXHT/VjKlv21uSmHS4I= Received: by 10.49.65.10 with SMTP id s10mr287253nfk; Wed, 22 Mar 2006 10:32:11 -0800 (PST) Received: by 10.49.1.16 with HTTP; Wed, 22 Mar 2006 10:32:11 -0800 (PST) Message-ID: <684362e10603221032t4b07f5edu@mail.gmail.com> Date: Thu, 23 Mar 2006 00:32:11 +0600 From: "Markus Bertheau" To: "Maciej Piekielniak" Subject: Re: How to optimize this query? Cc: pgsql-sql@postgresql.org In-Reply-To: <684362e10603221027t354b079bh@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <827987076.20060322181715@isb.com.pl> <684362e10603220958s2dac37cfp@mail.gmail.com> <1731392587.20060322191801@isb.com.pl> <684362e10603221027t354b079bh@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.118 required=5 tests=[AWL=0.118] X-Spam-Score: 0.118 X-Spam-Level: X-Archive-Number: 200603/357 X-Sequence-Number: 24703 In the meantime, try this: SELECT =09towar.id_towar, =09towar.key2, =09towar.nazwa, =090 AS min, =090 AS max, =09towar.ilosc_jed, =09towar.ilosc_nom, =09towar.ilosc_paczkowa, =09dostawcy.id_dostawcy, =09jednostka_miary.jednostka, =090.0 AS ilosc_magazyn, =09foo.z_zamowien, =09towar.key1 FROM =09towar =09JOIN ( =09=09SELECT =09=09=09zlecenia_towar.id_towar, =09=09=09SUM(zlecenia_towar.ilosc * zlecenia_elementy.ilosc) as z_zamowien =09=09FROM =09=09=09zlecenia_towar, =09=09=09zlecenia_elementy, =09=09=09zlecenia =09=09WHERE =09=09=09zlecenia_towar.id_zlecenia_elementy =3D zlecenia_elementy.id_zlece= nia_elementy =09=09 AND zlecenia_elementy.id_zlecenia =3D zlecenia.id_zlecenia =09=09 AND zlecenia.id_paczka IN (52,50,53) =09=09GROUP BY zlecenia_towar.id_towar =09) AS foo ON (foo.id_towar =3D towar.id_towar) =09LEFT JOIN dostawcy =09=09ON (towar.id_dostawcy =3D dostawcy.id_dostawcy) =09LEFT JOIN jednostka_miary =09=09ON (towar.id_jednostka_miary =3D jednostka_miary.id_jednostka_miary) WHERE =09towar.policzalne =3D True GROUP BY towar.id_towar, =09towar.key2, =09towar.nazwa, =09towar.min1, =09towar.max1, =09towar.ilosc_jed, =09towar.ilosc_nom, =09dostawcy.id_dostawcy, =09jednostka_miary.jednostka, =09towar.ilosc_paczkowa, =09towar.key1 ORDER BY =09id_dostawcy; I basically pulled the subselect from the field list into the from list. 2006/3/23, Markus Bertheau : > That's an explain. We need explain analyze. > > 2006/3/23, Maciej Piekielniak : > > Hello Markus, > > > > Wednesday, March 22, 2006, 6:58:44 PM, you wrote: > > > > MB> Send an EXPLAIN ANALYZE of the query along with the description of = the > > MB> involved tables. Also hardware information (RAM, disks, CPU), what > > MB> other applications are running on that box and the parameter values= in > > MB> postgresql.conf that you changed from the defaults would be > > MB> interesting. > > > > > > Sort (cost=3D21413847.71..21413867.37 rows=3D7864 width=3D107) > > Sort Key: dostawcy.id_dostawcy > > -> Group (cost=3D1360.03..21413073.50 rows=3D7864 width=3D107) > > -> Sort (cost=3D1360.03..1379.69 rows=3D7864 width=3D107) > > Sort Key: towar.id_towar, towar.key2, towar.nazwa, towar= .min1, towar.max1, towar.ilosc_jed, towar.ilosc_nom, dostawcy.id_dostawcy, = jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1 > > -> Hash Left Join (cost=3D2.21..585.81 rows=3D7864 wid= th=3D107) > > Hash Cond: ("outer".id_jednostka_miary =3D "inner"= .id_jednostka_miary) > > -> Hash Left Join (cost=3D1.14..466.78 rows=3D78= 64 width=3D103) > > Hash Cond: ("outer".id_dostawcy =3D "inner".= id_dostawcy) > > -> Seq Scan on towar (cost=3D0.00..347.68 = rows=3D7864 width=3D103) > > Filter: policzalne > > -> Hash (cost=3D1.11..1.11 rows=3D11 width= =3D4) > > -> Seq Scan on dostawcy (cost=3D0.00= ..1.11 rows=3D11 width=3D4) > > -> Hash (cost=3D1.06..1.06 rows=3D6 width=3D12) > > -> Seq Scan on jednostka_miary (cost=3D0.0= 0..1.06 rows=3D6 width=3D12) > > SubPlan > > -> Aggregate (cost=3D2722.71..2722.72 rows=3D1 width=3D14) > > -> Nested Loop (cost=3D64.33..2722.28 rows=3D171 wid= th=3D14) > > -> Hash Join (cost=3D64.33..602.79 rows=3D368 = width=3D12) > > Hash Cond: ("outer".id_zlecenia =3D "inner= ".id_zlecenia) > > -> Seq Scan on zlecenia_elementy (cost= =3D0.00..488.85 rows=3D9185 width=3D20) > > -> Hash (cost=3D63.98..63.98 rows=3D140 = width=3D8) > > -> Bitmap Heap Scan on zlecenia (c= ost=3D6.50..63.98 rows=3D140 width=3D8) > > Recheck Cond: ((id_paczka =3D = 52) OR (id_paczka =3D 50) OR (id_paczka =3D 53)) > > -> BitmapOr (cost=3D6.50..6.= 50 rows=3D142 width=3D0) > > -> Bitmap Index Scan on= zlecenia_id_paczka (cost=3D0.00..2.17 rows=3D47 width=3D0) > > Index Cond: (id_pa= czka =3D 52) > > -> Bitmap Index Scan on= zlecenia_id_paczka (cost=3D0.00..2.17 rows=3D47 width=3D0) > > Index Cond: (id_pa= czka =3D 50) > > -> Bitmap Index Scan on= zlecenia_id_paczka (cost=3D0.00..2.17 rows=3D47 width=3D0) > > Index Cond: (id_pa= czka =3D 53) > > -> Index Scan using zlezenia_towar_elementy_tow= ar on zlecenia_towar (cost=3D0.00..5.75 rows=3D1 width=3D18) > > Index Cond: ((zlecenia_towar.id_zlecenia_e= lementy =3D "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar =3D = $0)) > > (33 rows) > > > > -- > > Best regards, > > Maciej mailto:piechcio@isb.com.pl > > > > > > ---------------------------(end of broadcast)--------------------------= - > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > >