Hallo zusammen,
Ich versuche, von Orakel zu postgresql zu migrieren. Ich habe ein Problem mit Ansichten. Ich habe sie umgewandelt und sie arbeiten. Aber viel langsamer als im Orakel. Ich möchte fragen, was ist dein erster Eindruck, wenn du es ansiehst. “Es muss langsam sein”, “es sollte nicht langsamer sein als Orakel”, “es sollte mit dem Linken umgeschrieben werden”.
Vielen Dank,
Hi All,
I’m trying to migrate from oracle to postgresql.
I have a problem with views. I have converted them and they are working.
But much slower than in oracle.
I would like to ask what is your first impression when look at it “it must be slow”, “it should not to be slower than oracle”, “it should be rewritted with left join”.
Thank you very much,
SET client_encoding TO ‘UTF8’;
\set ON_ERROR_STOP ON
CREATE OR REPLACE VIEW vw_period_prod (selected, gen, line, wb, location, hatch, mort_date, mort_reason, mate_bird, min_pdate, max_pdate, period, eggs, defects, per_death, comp_period, main_key)
AS SELECT S.SELECTED, S.GEN, S.LINE, A.WB, A.LOCATION, S.HATCH, MAX(A.MORT_DATE) MORT_DATE, MAX(A.MORT_REASON) MORT_REASON,
MAX(CASE WHEN ISMATING(a.Main_Key) IS NOT NULL THEN ‘M’ ELSE NULL END) MATE_BIRD,
MIN(b.Prod_Date::date) MIN_PDATE,
MAX(b.Prod_Date::date) MAX_PDATE,
FLOOR(((B.PROD_DATE::date - S.HATCH_DATE::date)-126)/14)+1 PERIOD,
SUM(CASE WHEN TRX_TYPE=‘EN’ THEN 1 ELSE 0 END) EGGS,
SUM(CASE WHEN TRX_TYPE=‘DF’ THEN 1 ELSE 0 END) DEFECTS,
MAX(CASE WHEN A.MORT_DATE IS NULL THEN NULL
WHEN A.MORT_DATE < S.HATCH_DATE+126* interval ‘1 day’+1* interval ‘1 day’ THEN 0
else FLOOR((A.MORT_DATE::date - S.HATCH_DATE::date-126)/14)+1 END) PER_DEATH,
((least(localtimestamp, S.End_Prod_Date::date ,A.Mort_Date::date)::date - S.hatch_date::date)-126)/14 COMP_PERIOD,
MAX(A.MAIN_KEY) MAIN_KEY
FROM SCHED_FILE S, Gen_File a, Production b
Where (a.Selected=S.Selected and a.Gen=S.Gen and a.Line=S.Line and a.HATCH=S.HATCH) and
(a.Selected=b.Selected and a.Gen=B.Gen and a.Line=b.Line and a.wb=b.wb and a.sex=‘F’)
GROUP BY S.SELECTED, S.GEN, S.LINE, S.HATCH, A.WB, A.LOCATION, FLOOR(((B.PROD_DATE::date-S.HATCH_DATE::date)-126)/14)+1,S.End_Prod_Date ,A.Mort_Date,S.hatch_date
;
Hier ist die ursprüngliche Version
Here is original version
CREATE OR REPLACE VIEW vw_period_prod (selected, gen, line, wb, location, hatch, mort_date, mort_reason, mate_bird, min_pdate, max_pdate, period, eggs, defects, per_death, comp_period, main_key) AS SELECT S.SELECTED, S.GEN, S.LINE, A.WB, A.LOCATION, S.HATCH, MAX(A.MORT_DATE) MORT_DATE, MAX(A.MORT_REASON) MORT_REASON,
MAX(CASE WHEN rsdb.ISMATING(a.Main_Key) IS NOT NULL THEN ‘M’ ELSE NULL END) MATE_BIRD,
MIN(TRUNC(b.Prod_Date,‘FMDDD’)) MIN_PDATE,
MAX(TRUNC(b.Prod_Date,‘FMDDD’)) MAX_PDATE,
FLOOR(((TRUNC(B.PROD_DATE,‘FMDDD’)-TRUNC(S.HATCH_DATE,‘FMDDD’))-126)/14)+1 PERIOD,
SUM(CASE WHEN TRX_TYPE=‘EN’ THEN 1 ELSE 0 END) EGGS,
SUM(CASE WHEN TRX_TYPE=‘DF’ THEN 1 ELSE 0 END) DEFECTS,
MAX(CASE WHEN A.MORT_DATE IS NULL THEN NULL
WHEN A.MORT_DATE < S.HATCH_DATE+126+1 THEN 0
ELSE FLOOR(((A.MORT_DATE-S.HATCH_DATE)-126)/14)+1END) PER_DEATH,
MAX(TRUNC((ROUND((LEAST(SYSDATE, NVL(S.End_Prod_Date, SYSDATE) ,NVL(A.Mort_Date, SYSDATE)) - S.hatch_date), 0) - 126) / 14)) COMP_PERIOD,
MAX(A.MAIN_KEY) MAIN_KEY
FROM RSDB.SCHED_FILE S, Rsdb.Gen_File a, Rsdb.Production b
Where (a.Selected=S.Selected and a.Gen=S.Gen and a.Line=S.Line and a.HATCH=S.HATCH) and
(a.Selected=b.Selected and a.Gen=B.Gen and a.Line=b.Line and a.wb=b.wb and a.sex=‘F’
GROUP BY S.SELECTED, S.GEN, S.LINE, S.HATCH, A.WB, A.LOCATION, FLOOR(((TRUNC(B.PROD_DATE,‘FMDDD’)-TRUNC(S.HATCH_DATE,‘FMDDD’))-126)/14)+1
here is some table size
rsdb=# select count(*) from production ;
count
125869630
(1 row)
rsdb=# select count(*) from gen_file ;
count
3271335
(1 row)
rsdb=# select count(*) from sched_file ;
count
7464
(1 row)