Re: is possible cache tupledesc templates in execution plan? significant performance issue, maybe bug?

2017-10-08 18:36 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>
>
>