Thread: How to optimize this query?
Hello pgsql-sql, I have postgresql 8.1.3 and database with about 2,7GB (90% large objects). When I execute this query postgresql calculate this 2min 50sec. How can I optimize this query? 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,(selectsum(zlecenia_towar.ilosc*zlecenia_elementy.ilosc) from zlecenia_towar,zlecenia_elementy,zlecenia where zlecenia_towar.id_towar=towar.id_towar and 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))) as z_zamowien,towar.key1 from (towar LEFT JOIN dostawcy ON (towar.id_dostawcy = dostawcy.id_dostawcy) LEFT JOIN jednostka_miaryON (towar.id_jednostka_miary = jednostka_miary.id_jednostka_miary)) where towar.policzalne=True groupby 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.key1order by id_dostawcy; -- Best regards,Maciej mailto:piechcio@isb.com.pl
Send an EXPLAIN ANALYZE of the query along with the description of the involved tables. Also hardware information (RAM, disks, CPU), what other applications are running on that box and the parameter values in postgresql.conf that you changed from the defaults would be interesting. Markus 2006/3/22, Maciej Piekielniak <piechcio@isb.com.pl>: > Hello pgsql-sql, > > I have postgresql 8.1.3 and database with about 2,7GB (90% large > objects). > > When I execute this query postgresql calculate this 2min 50sec. How > can I optimize this query? > > 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,(select sum(zlecenia_towar.ilosc*zlecenia_elementy.ilosc) > from > zlecenia_towar,zlecenia_elementy,zlecenia > where > zlecenia_towar.id_towar=towar.id_towar and 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))) as z_zamowien,towar.key1 > from (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; > > -- > Best regards, > Maciej mailto:piechcio@isb.com.pl > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
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.50rows=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.81rows=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.68rows=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=11width=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.79rows=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.98rows=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.75rows=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
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
>
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
> >
>
Hello Markus, Wednesday, March 22, 2006, 7:32:11 PM, you wrote: MB> foo.z_zamowien, MB> ) AS foo ON (foo.id_towar = towar.id_towar) foo? -- Best regards,Maciej mailto:piechcio@isb.com.pl
Hello Markus, ERROR: column "foo.z_zamowien" must appear in the GROUP BY clause or be used in an aggregate function -- Best regards,Maciej mailto:piechcio@isb.com.pl
Hello Markus,
Sorry, I 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, foo.z_zamowien
ORDER BY id_dostawcy;
--
Best regards,Maciej mailto:piechcio@isb.com.pl
Hello Markus, Oryginal query return 7881 rows , your query only 729 rows. -- Best regards,Maciej mailto:piechcio@isb.com.pl
Maciej Piekielniak wrote: >Hello Markus, > >Oryginal query return 7881 rows , your query only 729 rows. > > > But it's faster!
Hello Markus,
Wednesday, March 22, 2006, 8:12:35 PM, you wrote:
MB> Well, send the table descriptions, please.
\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()
--
Best regards,Maciej mailto:piechcio@isb.com.pl
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