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 3EA019DD585 for ; Wed, 22 Mar 2006 13:58:49 -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 03005-05 for ; Wed, 22 Mar 2006 13:58:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.200]) by postgresql.org (Postfix) with ESMTP id 22E089DCD45 for ; Wed, 22 Mar 2006 13:58:45 -0400 (AST) Received: by nproxy.gmail.com with SMTP id l37so160349nfc for ; Wed, 22 Mar 2006 09:58:45 -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=TQLAcD5U+qjWpbA70hikRkndmBfDowElL3EeP/+lVrRQBWvpYZ9yiJ3BI74eTv2VGsVp6drMxBShCt6Ku5aF2mmxqSI2cMfkFEVGd1V54z7gZUAggotrSFVCqGY9qmoL3Cx02XEuzUECqeD5QlMy5N7XY6+HZVjR7i2OMiP3zYw= Received: by 10.48.254.3 with SMTP id b3mr277966nfi; Wed, 22 Mar 2006 09:58:44 -0800 (PST) Received: by 10.49.1.16 with HTTP; Wed, 22 Mar 2006 09:58:44 -0800 (PST) Message-ID: <684362e10603220958s2dac37cfp@mail.gmail.com> Date: Wed, 22 Mar 2006 23:58:44 +0600 From: "Markus Bertheau" To: "Maciej Piekielniak" Subject: Re: How to optimize this query? Cc: pgsql-sql@postgresql.org In-Reply-To: <827987076.20060322181715@isb.com.pl> 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> 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/354 X-Sequence-Number: 24700 Send an EXPLAIN ANALYZE of the query along with the description of the involved tables. Also hardware information (RAM, disks, CPU), what other applications are running on that box and the parameter values in postgresql.conf that you changed from the defaults would be interesting. Markus 2006/3/22, Maciej Piekielniak : > Hello pgsql-sql, > > I have postgresql 8.1.3 and database with about 2,7GB (90% large > objects). > > When I execute this query postgresql calculate this 2min 50sec. How > can I optimize this query? > > select towar.id_towar,towar.key2,towar.nazwa,0 as min,0 as > max,towar.ilosc_jed,towar.ilosc_nom,towar.ilosc_paczkowa,dostawcy= .id_dostawcy,jednostka_miary.jednostka,0.0 > AS ilosc_magazyn,(select sum(zlecenia_towar.ilosc*zlecenia_elemen= ty.ilosc) > from > zlecenia_towar,zlecenia_elementy,zlecenia > where > zlecenia_towar.id_towar=3Dtowar.id_towar and zlecenia_towar.id_zl= ecenia_elementy=3Dzlecenia_elementy.id_zlecenia_elementy > and zlecenia_elementy.id_zlecenia=3Dzlecenia.id_zlecenia > and (zlecenia.id_paczka in (52,50,53))) as z_zamowien,towar.key1 > from (towar LEFT JOIN dostawcy ON (towar.id_dostawcy =3D dostawcy= .id_dostawcy) LEFT JOIN jednostka_miary ON > (towar.id_jednostka_miary =3D jednostka_miary.id_jednostka_miary)= ) where towar.policzalne=3DTrue group by > 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 order= by id_dostawcy; > > -- > Best regards, > Maciej mailto:piechcio@isb.com.pl > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >