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
The numbers are related to number of columns of tables najzalobpr, najvzallok, NAJZALOBST, ..