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 A05939DCAD6 for ; Wed, 22 Mar 2006 14:18:05 -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 07273-07 for ; Wed, 22 Mar 2006 14:18:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from isb.com.pl (isb.com.pl [212.160.150.140]) by postgresql.org (Postfix) with ESMTP id ADFE99DC9BC for ; Wed, 22 Mar 2006 14:18:02 -0400 (AST) Received: from maciek (sembrz [192.168.1.50]) by isb.com.pl (Postfix) with ESMTP id 9EC6257842 for ; Wed, 22 Mar 2006 19:14:38 +0100 (CET) Date: Wed, 22 Mar 2006 19:18:01 +0100 From: Maciej Piekielniak X-Mailer: The Bat! (v1.62r) Reply-To: Maciej Piekielniak X-Priority: 3 (Normal) Message-ID: <1731392587.20060322191801@isb.com.pl> To: pgsql-sql@postgresql.org Subject: Re: How to optimize this query? In-Reply-To: <684362e10603220958s2dac37cfp@mail.gmail.com> References: <827987076.20060322181715@isb.com.pl> <684362e10603220958s2dac37cfp@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.098 required=5 tests=[AWL=0.098] X-Spam-Score: 0.098 X-Spam-Level: X-Archive-Number: 200603/355 X-Sequence-Number: 24701 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=21413847.71..21413867.37 rows=7864 width=107) Sort Key: dostawcy.id_dostawcy -> Group (cost=1360.03..21413073.50 rows=7864 width=107) -> Sort (cost=1360.03..1379.69 rows=7864 width=107) 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=2.21..585.81 rows=7864 width=107) Hash Cond: ("outer".id_jednostka_miary = "inner".id_jednostka_miary) -> Hash Left Join (cost=1.14..466.78 rows=7864 width=103) Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy) -> Seq Scan on towar (cost=0.00..347.68 rows=7864 width=103) Filter: policzalne -> Hash (cost=1.11..1.11 rows=11 width=4) -> Seq Scan on dostawcy (cost=0.00..1.11 rows=11 width=4) -> Hash (cost=1.06..1.06 rows=6 width=12) -> Seq Scan on jednostka_miary (cost=0.00..1.06 rows=6 width=12) SubPlan -> Aggregate (cost=2722.71..2722.72 rows=1 width=14) -> Nested Loop (cost=64.33..2722.28 rows=171 width=14) -> Hash Join (cost=64.33..602.79 rows=368 width=12) Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia) -> Seq Scan on zlecenia_elementy (cost=0.00..488.85 rows=9185 width=20) -> Hash (cost=63.98..63.98 rows=140 width=8) -> Bitmap Heap Scan on zlecenia (cost=6.50..63.98 rows=140 width=8) Recheck Cond: ((id_paczka = 52) OR (id_paczka = 50) OR (id_paczka = 53)) -> BitmapOr (cost=6.50..6.50 rows=142 width=0) -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) Index Cond: (id_paczka = 52) -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) Index Cond: (id_paczka = 50) -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) Index Cond: (id_paczka = 53) -> Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar (cost=0.00..5.75 rows=1 width=18) Index Cond: ((zlecenia_towar.id_zlecenia_elementy = "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar = $0)) (33 rows) -- Best regards, Maciej mailto:piechcio@isb.com.pl