From: | DAVID ROTH <adaptron(at)comcast(dot)net> |
---|---|
To: | Thomas Kellerer <shammat(at)gmx(dot)net>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: General Performance Question |
Date: | 2021-11-18 14:48:40 |
Message-ID: | 966668681.565028.1637246920878@connect.xfinity.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Agreed.
The code I am migrating uses the EXECUTE immediate form for almost everything. The path of least resistance for me is to just copy the code to Postgres and change "EXECUTE IMMEDIATE" to "EXECUTE".
I am asking about performance differences to see if it is worth converting code that does not have any dynamic elements.
I am also hoping to get a better understanding of the way the Postgres optimizer works. Any links you could suggest for this would be appreciated.
> On 11/18/2021 9:27 AM Thomas Kellerer <shammat(at)gmx(dot)net> wrote:
>
>
> DAVID ROTH schrieb am 18.11.2021 um 15:15:
> > I am working on a large Oracle to Postgres migration.
> > The existing code frequently constructs a string and then uses Oracle's "EXECUTE IMMEDIATE" to run it.
> > "EXECUTE" has the same functionality in Postgres.
> >
> > For example:
> > CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number)
> > RETURN VARCHAR2
> > AS
> > v_sql VARCHAR2(1000);
> > v_name VARCHAR2(30);
> > BEGIN
> > v_sql := 'SELECT name FROM employees';
> > v_sql := v_sql ||' WHERE employee_number = '||p_emp_no;
> > EXECUTE IMMEDIATE v_sql INTO v_name;
> > RETURN v_name;
> > END;
> > /
> >
> > CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number)
> > RETURN VARCHAR2
> > AS
> > v_name VARCHAR2(30);
> > BEGIN
> > SELECT name INTO v_name FROM employees
> > WHERE employee_number = p_emp_no;
> > RETURN v_name;
> > END;
> > /
> >
> > These are oversimplified samples of some very complex queries I need to migrate.
> >
> > How does the Postgres optimizer handle these 2 formats?
> > Which format is likely to perform better?
>
> The query does not use any dynamic parts, so EXECUTE is not needed to begin with.
> (not even in the Oracle implementation)
>
> For functions that just encapsulate a SQL query, a "language sql" function might be better:
>
> CREATE OR REPLACE FUNCTION djr_foo_fnc (p_emp_no integer)
> RETURNS text
> AS
> $$
> SELECT name
> FROM employees
> WHERE employee_number = p_emp_no;
> $$
> language sql
> rows 1;
>
>
> They are optimized together with the calling function which can be an advantage
> but doesn't have to be in all cases.
>
> If the function is called frequently avoiding the overhead of PL/pgSQL can make
> a difference though.
From | Date | Subject | |
---|---|---|---|
Next Message | Marcin Giedz | 2021-11-18 14:55:11 | restore with pg engine upgrade in a middle |
Previous Message | Dennis | 2021-11-18 14:45:37 | check scripts after database code change |