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

Question

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";

You don't need to "optimize" this SQL. Even if this sum(v."Surface") appears 1000 times in your query, the optimizer is smart enought and knows that this is the same value, and calculates it only once, not 1000 times. — krokodilko
– krokodilko, Commented Dec 12, 2016 at 20:27
@krokodilko, I was hoping for something like this, great, that pretty much answers my question, any other suggestions apart from the numerous casts that I intent to get rid of? — Daniël Tulp
– Daniël Tulp, Commented Dec 12, 2016 at 20:31

Evan Carroll · Accepted Answer · 2016-12-12 20:31:07Z

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;

answered Dec 12, 2016 at 20:31

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

2 Comments

Evan Carroll Over a year ago

I would never put an aggregate in a view that is used elsewhere as a base for other views. The planner can't push WHERE conditions down in that. And, moreover. I also would not do casting like that. If they need to be double precision, store them as a double precision. Keep them as a double precision. Use a math function if you need more or less precision. Your use of surface also tells me you should probably be using PostGIS or cube, or geometry base types.

Daniël Tulp Over a year ago

I agree, you misunderstand, I use the code in multiple views, I do not reference one view in another (came to the same conclusion you are stating the hard way). Surface is a data field of type integer stored in a database I use, so it is not a geometry type

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related