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=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