2

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)...

Is it possible to minimize the number of sorting by doing it after the aggregations or at least replacing the count distinct by another function?

**EXECUTION PLAN**

Query:

 SELECT 

"R"."Lat" AS "Lat",
"R"."Long R" AS "Long R",
"R"."Dept" AS "Dept",
"R"."Reg" AS "Reg",

"B3"."M" AS "M",
"B3"."St" AS "St",
"B3"."Sp"AS "Sp",
"B3"."Reg" AS "Reg",
"B3"."year" AS "year",
"B3"."id_program" AS "id_program",
"B3"."program" AS "program",
"B3"."Lib" AS "Lib",
"B3"."Ef"/"R2"."NB Dept" AS "Agg_Ef",
"B3"."Eex"/"R2"."NB Dept" AS "Agg_Eex",
"B3"."Ein"/"R2"."NB Dept" AS "Agg_Ein",
"B3"."Sehr"/"R2"."NB Dept" AS "Agg_Sehr",
"B3"."Sin"/"R2"."NB Dept" AS "Agg_Sin",
"B3"."Sr"/"R2"."NB Dept" AS "Agg_Sr",
"B3"."Sc"/"R2"."NB Dept" AS "Agg_Sr",

"C2"."RE"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RE",
"C2"."RD"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RD",
"C2"."RDP"/("R2"."NB Dept"*"B2"."NB St")AS "Agg_RDP",
"C2"."RC"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RC",
"C2"."RA"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_RA",
"C2"."EE"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_EE",
"C2"."BE"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BE",
"C2"."BD"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BD",
"C2"."BDP"/("R2"."NB Dept"*"B2"."NB St")AS "Agg_BDP",
"C2"."BC"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BC",
"C2"."BA"/("R2"."NB Dept"*"B2"."NB St") AS "Agg_BA"

FROM "Database"."R_Table" "R"

INNER JOIN (
 SELECT
 "R"."Reg" AS "Reg",
 COUNT (DISTINCT "R"."Dpt") AS "NB Dept"
FROM "Database"."R_Table" "R"  
 GROUP BY "R"."Reg") "R2" on "R2"."Reg" = "R"."Reg"

INNER JOIN (
 SELECT
 "B"."Reg" AS "Reg",
 COUNT (DISTINCT "B"."St") AS "NB St"
FROM "Database"."B_Table" "B"  
 GROUP BY "B"."Reg") "B2" on "B2"."Reg" = "R"."Reg"

INNER JOIN (
 SELECT
"B"."Job" AS "Job",
"B"."St" AS "St",
"B"."Sp" AS "Sp",
"B"."Reg" AS "Reg",
"B"."year" AS "year",
"B"."id_program" AS "id_program",
"B"."program" AS "program",
"B"."Lib" AS "Lib",
SUM("B"."Ef") AS "Ef",
SUM("B"."Eex") AS "Eex",
SUM("B"."Ein") AS "Ein",
SUM("B"."Sehr") AS "Sehr",
SUM("B"."Sin") AS "Sin",
SUM("B"."Sr") AS "Sr",
SUM("B"."Sc") AS "Sc"
FROM "Database"."B_Table" "B"
GROUP BY "B"."id_program","B"."program","B"."year","B"."Reg","B"."Job",
         "B"."Sp","B"."Lib","B"."St") "B3" on "B3"."Reg" = "R"."Reg"

LEFT JOIN (
SELECT
"C"."Job" AS "Job",
"C"."Sp" AS "Sp",
"C"."Reg" AS "Reg",
"C"."year" AS "year",
"C"."id_program" AS "id_program",
"C"."program" AS "program",
"C"."Lib" AS "Lib",
SUM("C"."RE") AS "RE",
SUM("C"."RD") AS "RE",
SUM("C"."RDP") AS "RDP",
SUM("C"."RC") AS "RC",
SUM("C"."RA") AS "RA",
SUM("C"."EE") AS "EE",
SUM("C"."BE") AS "BE",
SUM("C"."BD") AS "BD",
SUM("C"."BDP") AS "BPE",
SUM("C"."BC") AS "BC",
SUM("C"."BA") AS "BA"
FROM "Database"."Content" "C"
GROUP BY "C"."id_program","C"."program","C"."year",
         "C"."Reg","C"."Job", "C"."Sp","C"."Lib") "C2" on  
         concat("C2"."id_program","C2"."program","C2"."year","C2"."Reg","C2"."Job","C2"."Sp","C2"."Lib") = 
         concat("B3"."id_program","B3"."program","B3"."year","B3"."Reg","B3"."Job","B3"."Sp","B3"."Lib")
  • Why do you use concat() in the ON clause? Try replacing with proper condition ON "C2"."id_program" = "B3"."id_program" AND "C2"."program" = "B3"."program" AND ... – ypercubeᵀᴹ Feb 4 at 10:35
  • Or: ON ("C2"."id_program", "C2"."program", "C2"."year", "C2"."Reg", "C2"."Job", "C2"."Sp", "C2"."Lib") = ("B3"."id_program", "B3"."program", "B3"."year", "B3"."Reg", "B3"."Job", "B3"."Sp", "B3"."Lib") – ypercubeᵀᴹ Feb 4 at 10:37
  • Hi ypercube, Thank you for your help. I tried without concat and it's slower than before...Is it normal? – inux Feb 4 at 11:21
  • We'll need to see the execution plan (output of EXPLAIN (analyze, buffers) SELECT ...;) of your query and the CREATE TABLE statements of the 3 tables involved, including indexes (or you can use the output of \dt table , eg. \dt "Database"."R_Table"). Please edit the question and add those. The size of tables, column types and available indexes is crucial for performance questions. – ypercubeᵀᴹ Feb 4 at 12:27
  • 2
    Hi inux, Some people are more comfortable with the text output of explain, some would rather have the tree you posted and other will prefer the explain.despesz output. If you give us the text output, we can choose our prefered explain display and thus be more accurate with our comments. Could you post the explain analyze in text please ? – Arkhena Feb 4 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.