Re: How to optimize this query?

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(at)isb(dot)com(dot)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(at)isb(dot)com(dot)pl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>