2017-10-08 18:36 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>: > Hi > > I am looking why some queries are significantly slower on PostgreSQL than > on Oracle, although there is pretty simple plan. The queries are usually > 10x times slower on Postgres than on Oracle. > > I migrate old Oracle application to Postgres. There are important two > factors: > > 1. Often usage of "view" functions (I don't know better terminology) like: > > CREATE OR REPLACE FUNCTION foo(_id integer) > RETURNS text AS $$ > BEGIN > RETURN (SELECT xx FROM a JOIN b ON a.id = b.id WHERE b.y = _id) > END; > $$ LANGUAGE plpgsql; > > These functions are used in views > > CREATE VIEW xx AS > SELECT a, b, c, foo(id) as d, ... > > And sometimes are used in filters > > SELECT * FROM xx WHERE d IN NOT NULL; > > 2. Lot of used tables are pretty wide - 60, 120, .. columns > > Now, I am doing profiling, and I see so most time is related to > > ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk) > > This function is executed in exec init time - in this case pretty often. > Although there are used few columns from the table, the target list is > build for columns (maybe it is bug) > > I have a function > > CREATE OR REPLACE FUNCTION ides_funcs.najdatsplt_cislo_exekuce(mid_najdatsplt > bigint) > RETURNS character varying > LANGUAGE plpgsql > STABLE SECURITY DEFINER COST 1000 > AS $function$ > DECLARE > > Result varchar(200); > > --mZALOBCISLO NAJZALOBCE.ZALOBCISLO%TYPE; > mAdra varchar(200); > > > BEGIN > BEGIN > -- there are only tables > select CISLOEXEKUCE INTO STRICT mADRA > from najzalobpr MT, najvzallok A1, > NAJZALOBST A2, NAJZALOBCE A3 where > MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND > A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND > A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE AND > MT.ID_NAJDATSPLT = mID_NAJDATSPLT LIMIT 1; > EXCEPTION > WHEN OTHERS THEN > mADRA := NULL; > END; > > > > Result:=mADRA; > return(Result); > end; > $function$ > > where is necessary only few columns: > > but it processing target list of length > > NOTICE: plpgsql_exec_function: ides_funcs.najdatsplt_cislo_ > exekuce(bigint) > NOTICE: >>len: 38, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 21, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 1, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 65, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 1, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 93, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 1, hasoid: 0, skipjunk: 0 > NOTICE: >>len: 1, hasoid: 0, skipjunk: 0 > > len is length of targetlist > > The numbers are related to number of columns of tables najzalobpr, > najvzallok, NAJZALOBST, .. > > Because these tables are wide, then the queries are too slow > > So, my questions? > > 1. Why target list is too long in this case. It should be reduced to few > fields? > > 2. If is not possible to reduce the number of fields of target list, is > possible to store tupledesc template to plan? > > Without this issue, the Postgres has same speed or is faster than Ora. > > I can send a schema by some private channel. > > Regards > > The following workaround is working
create view xxxx as select CISLOEXEKUCE, MT.ID_NAJDATSPLT from najzalobpr MT, najvzallok A1, NAJZALOBST A2, NAJZALOBCE A3 where MT.ID_NAJVZALLOK= A1.ID_NAJVZALLOK AND A1.ID_NAJZALOBST=A2.ID_NAJZALOBST AND A2.ID_NAJZALOBCE= A3.ID_NAJZALOBCE; and function should be changed to BEGIN BEGIN select CISLOEXEKUCE INTO STRICT mADRA from xxxx WHERE id_najdatsplt = mID_najdatsplt LIMIT 1; EXCEPTION WHEN OTHERS THEN mADRA := NULL; END; Result:=mADRA; return(Result); end; So this issue is really related to tupleDesc management > Pavel > > >