[HACKERS] Re: is possible cache tupledesc templates in execution plan?significant performance issue, maybe bug? - Mailing list 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.

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

Pavel