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=uEnfV6ldUaCRZLwqDZH2zggemPb1qalYf21XfPEQRpwHvn+E8hJQOD8MImTfJSBSd7uHzTXad6m7x/DsR3t+gIn8gvlWrcc/9zHHtOQLQhOdjL0nQCzJn8N3cDfUKCQVe7zi/gOlAWvjRgd3HPD13l4JjnvHjB0dciOSVuOYi1E=
Message-ID: <684362e10603221027t354b079bh@mail.gmail.com>
Date: Thu, 23 Mar 2006 00:27:07 +0600
From: "Markus Bertheau" <mbertheau.pg@googlemail.com>
To: "Maciej Piekielniak" <piechcio@isb.com.pl>
Subject: Re: How to optimize this query?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1731392587.20060322191801@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>
	<684362e10603220958s2dac37cfp@mail.gmail.com>
	<1731392587.20060322191801@isb.com.pl>

That's an explain. We need explain analyze.

2006/3/23, Maciej Piekielniak <piechcio@isb.com.pl>:
> 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 th=
e
> MB> involved tables. Also hardware information (RAM, disks, CPU), what
> MB> other applications are running on that box and the parameter values i=
n
> 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.m=
in1, towar.max1, towar.ilosc_jed, towar.ilosc_nom, dostawcy.id_dostawcy, je=
dnostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1
>                ->  Hash Left Join  (cost=3D2.21..585.81 rows=3D7864 width=
=3D107)
>                      Hash Cond: ("outer".id_jednostka_miary =3D "inner".i=
d_jednostka_miary)
>                      ->  Hash Left Join  (cost=3D1.14..466.78 rows=3D7864=
 width=3D103)
>                            Hash Cond: ("outer".id_dostawcy =3D "inner".id=
_dostawcy)
>                            ->  Seq Scan on towar  (cost=3D0.00..347.68 ro=
ws=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.00.=
.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 width=
=3D14)
>                        ->  Hash Join  (cost=3D64.33..602.79 rows=3D368 wi=
dth=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 wi=
dth=3D8)
>                                    ->  Bitmap Heap Scan on zlecenia  (cos=
t=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 z=
lecenia_id_paczka  (cost=3D0.00..2.17 rows=3D47 width=3D0)
>                                                      Index Cond: (id_pacz=
ka =3D 52)
>                                                ->  Bitmap Index Scan on z=
lecenia_id_paczka  (cost=3D0.00..2.17 rows=3D47 width=3D0)
>                                                      Index Cond: (id_pacz=
ka =3D 50)
>                                                ->  Bitmap Index Scan on z=
lecenia_id_paczka  (cost=3D0.00..2.17 rows=3D47 width=3D0)
>                                                      Index Cond: (id_pacz=
ka =3D 53)
>                        ->  Index Scan using zlezenia_towar_elementy_towar=
 on zlecenia_towar  (cost=3D0.00..5.75 rows=3D1 width=3D18)
>                              Index Cond: ((zlecenia_towar.id_zlecenia_ele=
menty =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
>