1.15.0
Execution time:
N/A
Planning time:
N/A
Triggers: N/A
| #1 | Hash Join | |
| #2 | ├ Merge Join | |
| #3 | │├ Index Scan | |
| #4 | │└ Materialize | |
| #5 | │ └ Unique | |
| #6 | │ └ Sort | |
| #7 | │ └ Hash Join | |
| #8 | │ ├ Seq Scan | |
| #9 | │ └ Hash | |
| #10 | │ └ Seq Scan | |
| #11 | └ Hash | |
| #12 | └ Seq Scan |
[
{
"Plan": {
"Node Type": "Hash Join",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 294383.42,
"Total Cost": 311197.40,
"Plan Rows": 33,
"Plan Width": 269,
"Inner Unique": true,
"Hash Cond": "(table4.table3_uuid = table3.uuid)",
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 294341.40,
"Total Cost": 311154.71,
"Plan Rows": 33,
"Plan Width": 74,
"Inner Unique": false,
"Merge Cond": "(table4.date = b.date)",
"Join Filter": "(table4.table3_uuid = table1.table3_uuid)",
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "ix_table4_date",
"Relation Name": "table4",
"Alias": "table4",
"Startup Cost": 0.43,
"Total Cost": 15159.51,
"Plan Rows": 33,
"Plan Width": 33,
"Index Cond": "(date > '2023-02-17 00:00:00+00'::timestamp with time zone)",
"Filter": "actif"
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 294340.97,
"Total Cost": 295857.24,
"Plan Rows": 55137,
"Plan Width": 65,
"Plans": [
{
"Node Type": "Unique",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 294340.97,
"Total Cost": 295168.03,
"Plan Rows": 55137,
"Plan Width": 65,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 294340.97,
"Total Cost": 294478.82,
"Plan Rows": 55137,
"Plan Width": 65,
"Sort Key": ["b.date", "table1.table3_uuid", "b.type", "(((b.qte)::numeric * 1.0026))", "table1.volume"],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 1915.03,
"Total Cost": 287734.23,
"Plan Rows": 55137,
"Plan Width": 65,
"Inner Unique": true,
"Hash Cond": "(table1.id_table2 = b.identifiant)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "table1",
"Alias": "table1",
"Startup Cost": 0.00,
"Total Cost": 256667.00,
"Plan Rows": 11000000,
"Plan Width": 24
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 1910.01,
"Total Cost": 1910.01,
"Plan Rows": 401,
"Plan Width": 21,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "table2",
"Alias": "b",
"Startup Cost": 0.00,
"Total Cost": 1910.01,
"Plan Rows": 401,
"Plan Width": 21,
"Filter": "(actif AND (date > '2023-02-17 00:00:00+00'::timestamp with time zone))"
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 27.01,
"Total Cost": 27.01,
"Plan Rows": 1201,
"Plan Width": 49,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "table3",
"Alias": "table3",
"Startup Cost": 0.00,
"Total Cost": 27.01,
"Plan Rows": 1201,
"Plan Width": 49
}
]
}
]
}
}
]EXPLAIN (FORMAT JSON)
SELECT foo1.date,foo2.libelle as etiquette,foo1.volume_peak,foo1.table3_uuid,
foo1.source,foo1.status,
foo1."qte_peak",foo1."4h_peak_energy",
foo1."4h_peak_hour",foo1.hour_peak,foo1.hour_peak_e
FROM
(
SELECT cij.date,"volume_peak",source,
cij.table3_uuid,"qte_peak",
CASE
WHEN foob."qte_peak" IS NOT NULL AND foob.type = 'Mois' THEN 'Ok'
ELSE 'Provisional'
END status,
CASE
WHEN foob."qte_peak" IS NOT NULL AND foob.type = 'Jour' THEN foob."qte_peak"
ELSE null
END "4h_peak_energy",
CASE
WHEN foob."qte_peak" IS NOT NULL AND foob.type = 'Jour' THEN foob."type"
ELSE null
END "4h_peak_hour",
CASE
WHEN foob."qte_peak" IS NOT NULL AND foob.type = 'Jour' THEN cij."source"
ELSE null
END hour_peak,
CASE
WHEN foob."qte_peak" IS NOT NULL AND foob.type = 'Jour' THEN cij."table3_uuid"
ELSE null
END hour_peak_e,type
FROM
(
SELECT uuid,date,
CASE
WHEN valide = 'false' AND statut1 = 'Mesuré' THEN 'Meas'
ELSE 'Back'
END source,table3_uuid
FROM table4 WHERE actif = 'true' AND date > '2023-02-17'
)cij
LEFT JOIN
(
SELECT DISTINCT b.date,dbpm.table3_uuid,b.type,b.qte * 1.0026 AS "qte_peak",volume AS "volume_peak"
FROM table2 b
LEFT JOIN
(SELECT id_table2,table3_uuid,qte,volume
FROM table1)dbpm
ON b.identifiant = dbpm.id_table2
WHERE actif = 'true' AND date > '2023-02-17'
)foob
ON
cij.date = foob.date AND cij.table3_uuid = foob.table3_uuid)foo1
LEFT JOIN
(SELECT uuid,code,libelle FROM table3)foo2
ON foo1.table3_uuid = foo2.uuid| Node Type | Count | Time | |
|---|---|---|---|
| Hash Join | 2 | N/A | - |
| #7 Hash Join | N/A | - | |
| #1 Hash Join | N/A | - | |
| Merge Join | 1 | N/A | - |
| #2 Merge Join | N/A | - | |
| Index Scan | 1 | N/A | - |
| #3 Index Scan | N/A | - | |
| Materialize | 1 | N/A | - |
| #4 Materialize | N/A | - | |
| Unique | 1 | N/A | - |
| #5 Unique | N/A | - | |
| Sort | 1 | N/A | - |
| #6 Sort | N/A | - | |
| Seq Scan | 3 | N/A | - |
| #12 Seq Scan | N/A | - | |
| #10 Seq Scan | N/A | - | |
| #8 Seq Scan | N/A | - | |
| Hash | 2 | N/A | - |
| #11 Hash | N/A | - | |
| #9 Hash | N/A | - | |