VIEW Viel langsamer als im Orakel

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)

maybe missing indexes. hard to guess without table-definition and execution plan.

YES! it could be this.
I did not imported indexes from oracle…
But I cant import indexes with date_trunc…
Do you know how to translate it to postgresql??

CREATE INDEX db_trxdate_idx ON db_transactions ((date_trunc(‘fmddd’, trans_date)));

Thank you,

caveman

works for me:

test=# create table caveman(ts timestamp);
CREATE TABLE
test=*# create index index_caveman on caveman ((date_trunc('fmddd', ts)));
CREATE INDEX
test=*# \d caveman 
             Tabelle »public.caveman«
 Spalte |             Typ             | Attribute 
--------+-----------------------------+-----------
 ts     | timestamp without time zone | 
Indexe:
    "index_caveman" btree (date_trunc('fmddd'::text, ts))

test=*#

but i’m not sure if this index can help you.

We need table and index ddl to get a clue of the problem.
btw. : Indices don’t get import, you try to create a usefull index on pg side, similar to the ones in oracle
And for the index You mentioned: What does it have to do with the view resp. the 3 joined tables?
There should be some more indices?!
And why not use code tags and formating?

Wir brauchen die Create Statements für Tabellen und Indizes, um dazu etwas sagen zu können.
btw: Indizes werden nicht importiert, man versucht eine nützliche Indizierung analog zu Oracle anzulegen.

Und zu dem genannten Index: Was hat der mit der View und den 3 Tabellen zu tun?
Sollten für die 3 Tabellen nicht mehr Indizes existieren?
Wie sieht es mit Code Tags und Formatierung aus?

but i’m not sure if this index can help you.

i’m sure that not in case of mentioned here ‘vw_period_prod’ view but I have to migrate whole database.

interesting that it works for you Akretschmer… I got the error
rsdb=# CREATE INDEX db_trxdate_idx ON db_transactions ((date_trunc(‘fmddd’, trans_date)));
ERROR: timestamp units “fmddd” not recognized

hymm maybe it can be fixed by postgresql configuration… I will investigate this.

ok the most important is that you pointed me to indexes.

to pogomips:
the whole database is huge. I dont wont to flood the forum with that

So, first I will work on indexes,constraints, virtual_columns (which I have omited) and be back after I finish.

Once again thank you All for your help.

caveman

yeah:

test=*# select date_trunc('fmddd', now());
FEHLER:  »timestamp with time zone«-Einheit »fmddd« nicht erkannt

you can use for date_trunc:

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

don’t know what ‘fmddd’ should be …

seems that ‘day’ is what i’m looking for. but not sure.
I will let the forum know if find optimal solution.
Thank you,
caveman

Yes
FMDDD is just aligned day of year

so You could use

 to_Char(now(), 'DDD')

or something similar

alternative:

extract(doy from now())

I guess it should be “similar” to the other trunc functions in the statement, which seem to do all the same.
Not sure, if aligned text output is a requirement, as long as it is done same same in groub by, where condition etc
‘extract’ would return integer, unaligned of course.

If You run into
functions in index expression must be marked IMMUTABLE
on index creation, try ‘extract …’
or change column type

Indexes indexes indexes !!!
After indexes import the postgresql views are as fast as oracle!

using date_trunc with option ‘day’ works for me.
CREATE INDEX db_trxdate_idx ON db_transactions (date_trunc(‘day’,trans_date))

Thank you,
caveman

but of course solving problems brings forth next problems. I will be back here soon

you are welcome. please open a new thread for a new problem.