CODEWITHSUNDEEP
sqlpostgresql
Daniël Tulp
Daniël Tulp

Reputation: 1845

PostgreSQL Use a value from a select statement as a variable within that same statement

I have a pretty complex SQL statement in my PostgreSQL database which I use as a basis in several views. But in this statement, a sum aggregate function is called multiple times. I'm now seeking to optimize these views by getting rid of summarizing these values multiple times by using a variable.

I know I can use @myvalue = 1, but this is in pgScript and it can't be used in a view as it is pgScript.

I have read about abusing the config settings to store a variable in, but I don't know how this plays with views and I'm not really fond of such hacks.

The part from the SQL I want to store in a variable is sum(v."Surface")::double precision which is repeated multiple times.

If there are any other suggestions for optimizing this SQL I'm all for it.

CREATE OR REPLACE VIEW mydb."MyTable" AS 
SELECT p."Id",
    v."FunctionInt",
    v."TypeInt",
    sum(v."Surface") AS "SurfaceTotaal",
    round((sum(v."Surface"::double precision * v."Average") / sum(v."Surface")::double precision)::numeric, 1) AS "Average",
    round((sum(v."Surface"::double precision * v."E") / sum(v."Surface")::double precision)::numeric, 1) AS "E",
    round((sum(v."Surface"::double precision * v."E1") / sum(v."Surface")::double precision)::numeric, 1) AS "E1",
    round((sum(v."Surface"::double precision * v."E2") / sum(v."Surface")::double precision)::numeric, 1) AS "E2",
    round((sum(v."Surface"::double precision * v."E3") / sum(v."Surface")::double precision)::numeric, 1) AS "E3",
    round((sum(v."Surface"::double precision * v."M") / sum(v."Surface")::double precision)::numeric, 1) AS "M",
    round((sum(v."Surface"::double precision * v."M1") / sum(v."Surface")::double precision)::numeric, 1) AS "M1",
    round((sum(v."Surface"::double precision * v."M2") / sum(v."Surface")::double precision)::numeric, 1) AS "M2",
    round((sum(v."Surface"::double precision * v."M3") / sum(v."Surface")::double precision)::numeric, 1) AS "M3",
    round((sum(v."Surface"::double precision * v."G") / sum(v."Surface")::double precision)::numeric, 1) AS "G",
    round((sum(v."Surface"::double precision * v."G1") / sum(v."Surface")::double precision)::numeric, 1) AS "G1",
    round((sum(v."Surface"::double precision * v."G2") / sum(v."Surface")::double precision)::numeric, 1) AS "G2",
    round((sum(v."Surface"::double precision * v."G3") / sum(v."Surface")::double precision)::numeric, 1) AS "G3",
    round((sum(v."Surface"::double precision * v."G4") / sum(v."Surface")::double precision)::numeric, 1) AS "G4"
FROM mydb."PTable" p,
LATERAL ( 
    SELECT 
        v."Id",
        v."Surface",
        v."FunctionInt",
        v."TypeInt",
        r."YearGroupInt",
        r."Average",
        r."En" AS "E",
        r."En1" AS "E1",
        r."En2" AS "E2",
        r."En3" AS "E3",
        r."Mi" AS "M",
        r."Mi1" AS "M1",
        r."Mi2" AS "M2",
        r."Mi3" AS "M3",
        r."Gz" AS "G",
        r."Gz1" AS "G1",
        r."Gz2" AS "G2",
        r."Gz3" AS "G3",
        r."Gz4" AS "G4"
    FROM mydb."VTable" v
    JOIN mydb."RTable" r ON 
            v."FunctionInt" = r."FunctionInt" 
            AND v."TypeInt" = r."TypeInt" 
            AND v."YearGroupInt" = r."YearGroupInt"
    WHERE v."PId" = p."Id") v
GROUP BY p."Id", v."FunctionInt", v."TypeInt";

Upvotes: 0

Views: 263

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

Your assumption that sum(v."Surface")::double precision is calculated multiple times is incorrect. The planner handles that, test it for yourself.

CREATE TABLE foo AS
SELECT * FROM generate_series(1,1E7) AS t;

Then..

EXPLAIN ANALYZE SELECT sum(t) FROM foo;

Now, try it with two (no appreciable slow down)

EXPLAIN ANALYZE SELECT sum(t), sum(t) FROM foo;

Planner does not fold constants though,

EXPLAIN ANALYZE SELECT sum(t), sum(t+0) FROM foo;

Upvotes: 1

Related Questions