Re: How to optimize this query?

Hello Markus,

Wednesday, March 22, 2006, 8:35:33 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.

Celeron 1200 Tualatin 256kb cache
HD 200GB 7200
512 SDRAM
Postgresql 8.1.3 on debian sarge with standard settings
No other running applications.

EXPLAIN ANALYZE

"Sort (cost=21413847.71..21413867.37 rows=7864 width=107) (actual time=615902.463..615933.049 rows=7881 loops=1)"
" Sort Key: dostawcy.id_dostawcy"
" -> Group (cost=1360.03..21413073.50 rows=7864 width=107) (actual time=473.511..615628.474 rows=7881 loops=1)"
" -> Sort (cost=1360.03..1379.69 rows=7864 width=107) (actual time=324.260..407.732 rows=7881 loops=1)"
" 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) (actual time=0.607..178.794 rows=7881 loops=1)"
" Hash Cond: ("outer".id_jednostka_miary = "inner".id_jednostka_miary)"
" -> Hash Left Join (cost=1.14..466.78 rows=7864 width=103) (actual time=0.397..121.835 rows=7881 loops=1)"
" Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy)"
" -> Seq Scan on towar (cost=0.00..347.68 rows=7864 width=103) (actual time=0.160..60.079 rows=7881 loops=1)"
" Filter: policzalne"
" -> Hash (cost=1.11..1.11 rows=11 width=4) (actual time=0.185..0.185 rows=11 loops=1)"
" -> Seq Scan on dostawcy (cost=0.00..1.11 rows=11 width=4) (actual time=0.085..0.126 rows=11 loops=1)"
" -> Hash (cost=1.06..1.06 rows=6 width=12) (actual time=0.173..0.173 rows=6 loops=1)"
" -> Seq Scan on jednostka_miary (cost=0.00..1.06 rows=6 width=12) (actual time=0.117..0.140 rows=6 loops=1)"
" SubPlan"
" -> Aggregate (cost=2722.71..2722.72 rows=1 width=14) (actual time=78.006..78.010 rows=1 loops=7881)"
" -> Nested Loop (cost=64.33..2722.28 rows=171 width=14) (actual time=73.991..77.930 rows=6 loops=7881)"
" -> Hash Join (cost=64.33..602.79 rows=368 width=12) (actual time=3.098..64.518 rows=627 loops=7881)"
" Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia)"
" -> Seq Scan on zlecenia_elementy (cost=0.00..488.85 rows=9185 width=20) (actual time=0.009..32.216 rows=9185 loops=7881)"
" -> Hash (cost=63.98..63.98 rows=140 width=8) (actual time=4.849..4.849 rows=195 loops=1)"
" -> Bitmap Heap Scan on zlecenia (cost=6.50..63.98 rows=140 width=8) (actual time=0.721..3.772 rows=195 loops=1)"
" Recheck Cond: ((id_paczka = 52) OR (id_paczka = 50) OR (id_paczka = 53))"
" -> BitmapOr (cost=6.50..6.50 rows=142 width=0) (actual time=0.549..0.549 rows=0 loops=1)"
" -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) (actual time=0.427..0.427 rows=73 loops=1)"
" Index Cond: (id_paczka = 52)"
" -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) (actual time=0.059..0.059 rows=49 loops=1)"
" Index Cond: (id_paczka = 50)"
" -> Bitmap Index Scan on zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0) (actual time=0.039..0.039 rows=73 loops=1)"
" Index Cond: (id_paczka = 53)"
" -> Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar (cost=0.00..5.75 rows=1 width=18) (actual time=0.015..0.015 rows=0 loops=4941387)"
" Index Cond: ((zlecenia_towar.id_zlecenia_elementy = "outer".id_zlecenia_elementy) AND (zlecenia_towar.id_towar = $0))"
"Total runtime: 615962.759 ms"

\d towar
max3 | smallint | default 0
max4 | smallint | default 0
typik | character varying(1) | default '_'::character varying
id_grupa_rabatowa | integer | not null default 0
id_jednostka_miary | integer | not null default 0
id_vat | integer | not null default 0
id_typ_towaru | integer | not null default 0
id_dostawcy | integer | not null default 0
grupa_produkcji | smallint |
dodatek | boolean | not null default false
policzalne | boolean | not null default true
simport | character varying(50) |
czy_procent | boolean | not null default false
subtyp | character varying(35) |
kontofk | character varying(40) |
typks | character varying(30) |
nazwarodzaju | character varying(50) |
nazwakatalogu | character varying(250) |
waluta | character varying(3) | not null default 'PLN'::character varying
bank | character varying(5) | not null default 'NBP'::character varying
procent_do_wyceny | smallint | not null default 0
waga | numeric(24,4) | not null default 0
cena_z | numeric(24,4) | not null default 0
ilosc_paczkowa | numeric(24,4) | not null default 0
ilosc_jed | numeric(24,4) | not null default 1
ilosc_nom | numeric(24,4) | not null default 1
odpad | numeric(24,4) | not null default 0
cena_jedn | numeric(24,4) | not null default 0
roboczojednostka | numeric(24,4) | not null default 0
Indexes:
"towar_pkey" PRIMARY KEY, btree (id_towar)
"towar_key1" btree (key1)
"towar_key2" btree (key2)
Foreign-key constraints:
"$1" FOREIGN KEY (id_grupa_rabatowa) REFERENCES grupa_rabatowa(id_grupa_rabatowa) ON UPDATE CASCADE ON DELETE SET NULL
"$2" FOREIGN KEY (id_jednostka_miary) REFERENCES jednostka_miary(id_jednostka_miary) ON UPDATE CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (id_vat) REFERENCES vat(id_vat) ON UPDATE CASCADE ON DELETE SET NULL
"$4" FOREIGN KEY (id_typ_towaru) REFERENCES typ_towaru(id_typ_towaru) ON UPDATE CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (id_dostawcy) REFERENCES dostawcy(id_dostawcy) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
towar_domyslne BEFORE INSERT ON towar FOR EACH ROW EXECUTE PROCEDURE domyslne_ustawienia()
zmiana_wagi AFTER UPDATE ON towar FOR EACH ROW EXECUTE PROCEDURE waga_przelicz()

\d zlecenia_towar
Table "public.zlecenia_towar"
Column | Type | Modifiers
----------------------+-----------------------+----------------------------------------------------------------------------
id_zlecenia_towar | bigint | not null default nextval('zlecenia_towar_id_zlecenia_towar_seq'::regclass)
id_zlecenia_elementy | bigint |
id_towar | bigint |
serwer | smallint |
gdzie | character varying(1) | not null default 'p'::character varying
opismf | character varying(30) |
waga | numeric(24,4) | not null default 0
sprzedaz_c | numeric(24,4) | not null default 0
zakup_c | numeric(24,4) | not null default 0
ilosc | numeric(48,4) | not null default 0
wysokosc | numeric(48,4) | not null default 0
szerokosc | numeric(48,4) | not null default 0
realizacja | numeric(48,4) | not null default 0
Indexes:
"zlecenia_towar_pkey" PRIMARY KEY, btree (id_zlecenia_towar)
"zlecenia_towar_id_towar" btree (id_towar)
"zlecenia_towar_id_zlecenia_elementy" btree (id_zlecenia_elementy)
"zlecenia_towar_serwer" btree (serwer)
"zlezenia_towar_elementy_towar" btree (id_zlecenia_elementy, id_towar)
Foreign-key constraints:
"$1" FOREIGN KEY (id_zlecenia_elementy) REFERENCES zlecenia_elementy(id_zlecenia_elementy) ON UPDATE CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (id_towar) REFERENCES towar(id_towar) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
insert_waga_elementu BEFORE INSERT ON zlecenia_towar FOR EACH ROW EXECUTE PROCEDURE waga_wstaw()
zmiana_waga_elementy AFTER INSERT OR UPDATE ON zlecenia_towar FOR EACH ROW EXECUTE PROCEDURE waga_elementy()

\d zlecenia_elementy
vat | smallint | default 0
serwer | smallint |
wykonane_okna | smallint | not null default 0
ksiegowosc_okna | smallint | not null default 0
figura | character varying(50) |
parametr | character varying(50) |
tx1 | character varying(50) |
tx2 | character varying(50) |
tx3 | character varying(50) |
opis | character varying(255) |
datap | date |
zmiana | smallint | not null default 0
linia | smallint | not null default 0
sz_szwiatlo | numeric(24,4) |
wy_szwiatlo | numeric(24,4) |
sz | numeric(24,4) |
wy | numeric(24,4) |
wartosc_netto_mat | numeric(24,4) | not null default 0
wartosc_netto_mat_jed | numeric(24,4) | not null default 0
waga_all | numeric(24,4) | not null default 0
waga_one | numeric(24,4) | not null default 0
metry_one | numeric(24,4) | not null default 0
metry_all | numeric(24,4) | not null default 0
metryb_one | numeric(24,4) | not null default 0
metryb_all | numeric(24,4) | not null default 0
cena | numeric(48,4) |
cena_netto | numeric(48,4) |
sprzedaz | numeric(48,4) | default 0
zakuppr | numeric(48,4) | not null default 0
Indexes:
"zlecenia_elementy_pkey" PRIMARY KEY, btree (id_zlecenia_elementy)
"zlecenia_elementy_data_p" btree (datap)
"zlecenia_elementy_id_zlecenia" btree (id_zlecenia)
"zlecenia_elementy_nr_w_zleceniu" btree (nr_w_zleceniu)
"zlecenia_elementy_serwer" btree (serwer)
Foreign-key constraints:
"$1" FOREIGN KEY (id_zlecenia) REFERENCES zlecenia(id_zlecenia) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
ilosc_okien_w_zleceniu AFTER INSERT ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE policz_okna_w_zleceniu()
ilosc_zrobionych_okien_w_zleceniu AFTER UPDATE ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE policz_zrobione_okna_w
_zleceniu()
insert_metry_elementu BEFORE INSERT ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE metry_wstaw()

\d zlecenia
linia | smallint | not null default 0
status | integer | not null default 0
Indexes:
"zlecenia_pkey" PRIMARY KEY, btree (id_zlecenia)
"zlecenia_data" btree (data)
"zlecenia_data_p" btree (data, id_paczka)
"zlecenia_data_pt" btree (data, id_paczka_tir)
"zlecenia_data_zam" btree (data_zam)
"zlecenia_data_zam_p" btree (data_zam, id_paczka)
"zlecenia_data_zam_pt" btree (data_zam, id_paczka_tir)
"zlecenia_id_firmy" btree (id_firmy)
"zlecenia_id_paczka" btree (id_paczka)
"zlecenia_id_paczka_tir" btree (id_paczka_tir)
"zlecenia_ksiegowosc" btree (ksiegowosc)
"zlecenia_ksiegowosc_p" btree (ksiegowosc, id_paczka)
"zlecenia_ksiegowosc_pt" btree (ksiegowosc, id_paczka_tir)
"zlecenia_ok" btree (ok)
"zlecenia_ok_p" btree (ok, id_paczka)
"zlecenia_ok_pt" btree (ok, id_paczka_tir)
"zlecenia_proforma" btree (proforma)
"zlecenia_proforma_p" btree (proforma, id_paczka)
"zlecenia_proforma_pt" btree (proforma, id_paczka_tir)
"zlecenia_serwer" btree (serwer)
"zlecenia_zamkniete" btree (zamkniete)
"zlecenia_zamkniete_czas" btree (zamkniete_czas)
"zlecenia_zamkniete_czas_p" btree (zamkniete_czas, id_paczka)
"zlecenia_zamkniete_czas_pt" btree (zamkniete_czas, id_paczka_tir)
"zlecenia_zamkniete_data" btree (zamkniete_data)
"zlecenia_zamkniete_data_p" btree (zamkniete_data, id_paczka)
"zlecenia_zamkniete_data_pt" btree (zamkniete_data, id_paczka_tir)
"zlecenia_zamkniete_p" btree (zamkniete, id_paczka)
"zlecenia_zamkniete_pt" btree (zamkniete, id_paczka_tir)
"zlecenia_zamowienie" btree (zamowienie)
"zlecenia_zamowienie_p" btree (zamowienie, id_paczka)
"zlecenia_zamowienie_pt" btree (zamowienie, id_paczka_tir)
Foreign-key constraints:
"$1" FOREIGN KEY (id_firmy) REFERENCES firmy(id_firmy) ON UPDATE CASCADE ON DELETE SET NULL
"$2" FOREIGN KEY (id_paczka) REFERENCES paczka(id_paczka) ON UPDATE CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (id_paczka_tir) REFERENCES paczka_tir(id_paczka_tir) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
ststus_zlecenia BEFORE UPDATE ON zlecenia FOR EACH ROW EXECUTE PROCEDURE test_ststusu_zlecenia()
synchronizacja_kontrachenta AFTER INSERT OR UPDATE ON zlecenia FOR EACH ROW EXECUTE PROCEDURE synchrinizacja_firm()

\d dostawcy
Table "public.dostawcy"
Column | Type | Modifiers
-------------+------------------------+----------------------------------------------------------------
id_dostawcy | integer | not null default nextval('dostawcy_id_dostawcy_seq'::regclass)
code | integer |
skrot | character varying(50) |
nazwa | character varying(50) |
nip | character varying(20) |
adres | character varying(50) |
miasto | character varying(30) |
kod | character varying(6) |
woj | character varying(20) |
panstwo | character varying(20) |
telefon | character varying(15) |
mobile | character varying(15) |
fax | character varying(15) |
email | character varying(50) |
bank | character varying(50) |
konto | character varying(100) |
regon | character varying(20) |
kk | character varying(50) |
Indexes:
"dostawcy_pkey" PRIMARY KEY, btree (id_dostawcy)

\d jednostka_miary
Table "public.jednostka_miary"
Column | Type | Modifiers
--------------------+-----------------------+------------------------------------------------------------------------------
id_jednostka_miary | integer | not null default nextval('jednostka_miary_id_jednostka_miary_seq'::regclass)
jednostka | character varying(4) | not null
opis | character varying(20) | not null
Indexes:
"jednostka_miary_pkey" PRIMARY KEY, btree (id_jednostka_miary)

--
Best regards,
Maciej mailto:piechcio(at)isb(dot)com(dot)pl