[HACKERS] is possible cache tupledesc templates in execution plan? significantperformance issue, maybe bug?
От | Pavel Stehule |
---|---|
Тема | [HACKERS] is possible cache tupledesc templates in execution plan? significantperformance issue, maybe bug? |
Дата | |
Msg-id | CAFj8pRAKEmg2tYQNbJSLAfsFo7ofthMPPzTiPUdBx8aGm8q-Mg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: [HACKERS] is possible cache tupledesc templates in executionplan? significant performance issue, maybe bug?
[HACKERS] Re: is possible cache tupledesc templates in execution plan?significant performance issue, maybe bug? |
Список | pgsql-hackers |
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.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;
ExecTypeFromTLInternal(List *targetList, bool hasoid, bool skipjunk)
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: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$
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
В списке pgsql-hackers по дате отправления: