From: Markus Bertheau <mbertheau.pg@googlemail.com>
To: Maciej Piekielniak <piechcio@isb.com.pl>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query?
Date: Wed, 22 Mar 2006 23:58:44 +0600
Message-ID: <684362e10603220958s2dac37cfp@mail.gmail.com> (raw)
In-Reply-To: <827987076.20060322181715@isb.com.pl>
References: <827987076.20060322181715@isb.com.pl>
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 <piechcio@isb.com.pl>:
> 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_elementy.ilosc)
> from
> zlecenia_towar,zlecenia_elementy,zlecenia
> where
> zlecenia_towar.id_towar=towar.id_towar and zlecenia_towar.id_zlecenia_elementy=zlecenia_elementy.id_zlecenia_elementy
> and zlecenia_elementy.id_zlecenia=zlecenia.id_zlecenia
> and (zlecenia.id_paczka in (52,50,53))) as z_zamowien,towar.key1
> from (towar LEFT JOIN dostawcy ON (towar.id_dostawcy = dostawcy.id_dostawcy) LEFT JOIN jednostka_miary ON
> (towar.id_jednostka_miary = jednostka_miary.id_jednostka_miary)) where towar.policzalne=True 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mbertheau.pg@googlemail.com, piechcio@isb.com.pl
Subject: Re: How to optimize this query?
In-Reply-To: <684362e10603220958s2dac37cfp@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox