Re: How to optimize this query? - Mailing list pgsql-sql

In the meantime, try this:

SELECTtowar.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.0AS
ilosc_magazyn,foo.z_zamowien,towar.key1
FROMtowarJOIN (    SELECT        zlecenia_towar.id_towar,        SUM(zlecenia_towar.ilosc * zlecenia_elementy.ilosc) as
z_zamowien   FROM        zlecenia_towar,        zlecenia_elementy,        zlecenia    WHERE
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)    GROUP BY zlecenia_towar.id_towar) AS foo ON
(foo.id_towar= towar.id_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) 
WHEREtowar.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 BYid_dostawcy;

I basically pulled the subselect from the field list into the from list.

2006/3/23, Markus Bertheau <mbertheau.pg@googlemail.com>:
> 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 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=47width=0) 
> >                                                      Index Cond: (id_paczka = 52)
> >                                                ->  Bitmap Index Scan on zlecenia_id_paczka  (cost=0.00..2.17
rows=47width=0) 
> >                                                      Index Cond: (id_paczka = 50)
> >                                                ->  Bitmap Index Scan on zlecenia_id_paczka  (cost=0.00..2.17
rows=47width=0) 
> >                                                      Index Cond: (id_paczka = 53)
> >                        ->  Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar  (cost=0.00..5.75
rows=1width=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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>