Re: How to optimize this query?
От | Maciej Piekielniak |
---|---|
Тема | Re: How to optimize this query? |
Дата | |
Msg-id | 754165713.20060322210134@isb.com.pl обсуждение исходный текст |
Ответ на | How to optimize this query? (Maciej Piekielniak <piechcio@isb.com.pl>) |
Список | pgsql-sql |
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=7881loops=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=11loops=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=6loops=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.216rows=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.772rows=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=0loops=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 towarmax3 | smallint | default 0max4 | smallint | default 0typik | character varying(1) | default '_'::character varyingid_grupa_rabatowa | integer |not null default 0id_jednostka_miary | integer | not null default 0id_vat | integer | not null default 0id_typ_towaru | integer | not null default 0id_dostawcy | integer | not null default 0grupa_produkcji | smallint |dodatek | boolean | not null default falsepoliczalne | boolean | not null default truesimport | charactervarying(50) |czy_procent | boolean | not null default falsesubtyp | charactervarying(35) |kontofk | character varying(40) |typks | character varying(30) |nazwarodzaju | character varying(50) |nazwakatalogu | character varying(250) |waluta | charactervarying(3) | not null default 'PLN'::character varyingbank | character varying(5) | not null default'NBP'::character varyingprocent_do_wyceny | smallint | not null default 0waga | numeric(24,4) | not null default 0cena_z | numeric(24,4) | not null default 0ilosc_paczkowa | numeric(24,4) | not null default 0ilosc_jed | numeric(24,4) | not null default1ilosc_nom | numeric(24,4) | not null default 1odpad | numeric(24,4) | notnull default 0cena_jedn | numeric(24,4) | not null default 0roboczojednostka | 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 CASCADEON DELETE SET NULL "$2" FOREIGN KEY (id_jednostka_miary) REFERENCES jednostka_miary(id_jednostka_miary) ON UPDATECASCADE 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" FOREIGNKEY (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 AFTERUPDATE 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) | notnull default 'p'::character varyingopismf | character varying(30) |waga | numeric(24,4) | not null default 0sprzedaz_c | numeric(24,4) | not null default 0zakup_c | numeric(24,4) | not null default 0ilosc | numeric(48,4) | not null default 0wysokosc | numeric(48,4) | not null default 0szerokosc | numeric(48,4) | not nulldefault 0realizacja | 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) ONUPDATE 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_elementyAFTER INSERT OR UPDATE ON zlecenia_towar FOR EACH ROW EXECUTE PROCEDURE waga_elementy() \d zlecenia_elementyvat | smallint | default 0serwer | smallint |wykonane_okna | smallint | not null default 0ksiegowosc_okna | smallint |not null default 0figura | character varying(50) |parametr | character varying(50) |tx1 | character varying(50) |tx2 | character varying(50) |tx3 | charactervarying(50) |opis | character varying(255) |datap | date |zmiana | smallint | not null default 0linia | smallint | not nulldefault 0sz_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 0wartosc_netto_mat_jed | numeric(24,4) | not null default 0waga_all | numeric(24,4) | not null default 0waga_one | numeric(24,4) | not null default0metry_one | numeric(24,4) | not null default 0metry_all | numeric(24,4) | not null default 0metryb_one | numeric(24,4) | not null default 0metryb_all | numeric(24,4) | not null default 0cena | numeric(48,4) |cena_netto | numeric(48,4) |sprzedaz | numeric(48,4) | default 0zakuppr | 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 PROCEDUREpolicz_zrobione_okna_w _zleceniu() insert_metry_elementu BEFORE INSERT ON zlecenia_elementy FOR EACH ROW EXECUTE PROCEDURE metry_wstaw() \d zlecenialinia | smallint | not null default 0status | integer | notnull 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_kontrachentaAFTER 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 | charactervarying(50) |konto | character varying(100) |regon | character varying(20) |kk | charactervarying(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 nullopis | character varying(20) | not null Indexes: "jednostka_miary_pkey" PRIMARY KEY, btree (id_jednostka_miary) -- Best regards,Maciej mailto:piechcio@isb.com.pl