pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed 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: Thu, 23 Mar 2006 00:32:11 +0600
Message-ID: <684362e10603221032t4b07f5edu@mail.gmail.com> (raw )
In-Reply-To: <684362e10603221027t354b079bh@mail.gmail.com >
References: <827987076.20060322181715@isb.com.pl >
<684362e10603220958s2dac37cfp@mail.gmail.com >
<1731392587.20060322191801@isb.com.pl >
<684362e10603221027t354b079bh@mail.gmail.com >
In the meantime, try this:
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,
foo.z_zamowien,
towar.key1
FROM
towar
JOIN (
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)
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;
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=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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
view thread (28+ messages) latest in thread
Message-ID: <684362e10603221032t4b07f5edu@mail.gmail.com>
Permalink: ../684362e10603221032t4b07f5edu@mail.gmail.com/
Also on: postgresql.org/message-id/684362e10603221032t4b07f5edu@mail.gmail.com
copy link · copy postgr.es
reply 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: <684362e10603221032t4b07f5edu@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