1.12.1
Execution time:
13s 227ms
Planning time:
28.6ms
Triggers: N/A
| Main Query Plan | |||
|---|---|---|---|
| #1 | Hash Join | ||
| #22 | ├ Merge Join | ||
| #23 | │├ Sort | ||
| #24 | ││└ Hash Join | ||
| #25 | ││ ├ Hash Join | ||
| #26 | ││ │├ Seq Scan | ||
| #27 | ││ │└ Hash | ||
| #28 | ││ │ └ Hash Join | ||
| #29 | ││ │  ├ Hash Join | ||
| #30 | ││ │  │├ Seq Scan | ||
| #31 | ││ │  │└ Hash | ||
| #32 | ││ │  │ └ Seq Scan | ||
| #33 | ││ │  └ Hash | ||
| #34 | ││ │   └ Hash Join | ||
| #35 | ││ │    ├ Hash Join | ||
| #36 | ││ │    │├ Seq Scan | ||
| #37 | ││ │    │└ Hash | ||
| #38 | ││ │    │ └ Seq Scan | ||
| #39 | ││ │    └ Hash | ||
| #40 | ││ │     └ Hash Join | ||
| #41 | ││ │      ├ Seq Scan | ||
| #42 | ││ │      └ Hash | ||
| #43 | ││ │       └ Seq Scan | ||
| #44 | ││ └ Hash | ||
| #45 | ││  └ Seq Scan | ||
| #46 | │└ GroupAggregate | ||
| #52 | │ └ Nested Loop | ||
| #53 | │  ├ Merge Join | ||
| #54 | │  │├ Sort | ||
| #55 | │  ││└ Hash Join | ||
| #56 | │  ││ ├ Seq Scan | ||
| #57 | │  ││ └ Hash | ||
| #58 | │  ││  └ Seq Scan | ||
| #59 | │  │└ Sort | ||
| #60 | │  │ └ Seq Scan | ||
| #61 | │  └ Materialize | ||
| #62 | │   └ Nested Loop | ||
| #63 | │    ├ HashAggregate | ||
| #64 | │    │└ CTE Scan | ||
| #65 | │    └ Bitmap Heap Scan | ||
| #66 | │     └ Bitmap Index Scan | ||
| #67 | â”” Hash | ||
| #68 |  └ Subquery Scan | ||
| #69 |   └ HashAggregate | ||
| #70 |    └ Merge Join | ||
| #71 |     ├ Sort | ||
| #72 |     │└ Hash Join | ||
| #73 |     │ ├ Hash Join | ||
| #74 |     │ │├ Seq Scan | ||
| #75 |     │ │└ Hash | ||
| #76 |     │ │ └ Hash Join | ||
| #77 |     │ │  ├ Seq Scan | ||
| #78 |     │ │  └ Hash | ||
| #79 |     │ │   └ Seq Scan | ||
| #80 |     │ └ Hash | ||
| #81 |     │  └ CTE Scan | ||
| #82 |     └ Materialize | ||
| #83 |      └ Sort | ||
| #84 |       └ Seq Scan | ||
| CTE latest_trh_locations | |||
| #2 | Nested Loop | ||
| #3 | ├ Nested Loop | ||
| #4 | │├ Subquery Scan | ||
| #5 | ││└ WindowAgg | ||
| #6 | ││ └ Sort | ||
| #7 | ││  └ Seq Scan | ||
| #8 | │└ Index Scan | ||
| #9 | â”” Index Scan | ||
| CTE location_distances | |||
| #10 | Nested Loop | ||
| #11 | ├ Seq Scan | ||
| #12 | â”” Materialize | ||
| #13 |  └ Seq Scan | ||
| CTE sensor_distances | |||
| #14 | Hash Join | ||
| #15 | ├ CTE Scan | ||
| #16 | â”” Hash | ||
| #17 |  └ CTE Scan | ||
| CTE closests_trh_sensors | |||
| #18 | Subquery Scan | ||
| #19 | â”” WindowAgg | ||
| #20 |  └ Sort | ||
| #21 |   └ CTE Scan | ||
| CTE propagation_trh_sensors | |||
| #47 | Nested Loop | ||
| #48 | ├ Nested Loop | ||
| #49 | │├ CTE Scan | ||
| #50 | │└ Index Only Scan | ||
| #51 | â”” Seq Scan | ||
[
{
"Plan": {
"Node Type": "Hash Join",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 342377.76,
"Total Cost": 540414.23,
"Plan Rows": 218,
"Plan Width": 380,
"Actual Startup Time": 3270.969,
"Actual Total Time": 13036.28,
"Actual Rows": 217,
"Actual Loops": 1,
"Output": [
"batches.id",
"crop_types.name",
"batches.tray_size",
"batches.number_of_trays",
"batch_events.event_time",
"batch_events_1.event_time",
"batch_events_2.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"batch_events_3.event_time",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production",
"(batch_events_3.event_time - batch_events_2.event_time)",
"grow_trh.avg_temp",
"grow_trh.avg_rh",
"grow_trh.avg_vpd",
"(avg(aranet_trh_data.temperature))",
"(avg(aranet_trh_data.humidity))",
"(avg((('610.78'::double precision * exp((('17.2694'::double precision * aranet_trh_data.temperature) / (aranet_trh_data.temperature + '237.3'::double precision)))) * ('1'::double precision - (aranet_trh_data.humidity / '100'::double precision)))))"
],
"Inner Unique": true,
"Hash Cond": "(batches.id = grow_trh.batch_id)",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE latest_trh_locations",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 3.14,
"Total Cost": 13.35,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.116,
"Actual Total Time": 0.349,
"Actual Rows": 21,
"Actual Loops": 1,
"Output": [
"ss.sensor_id",
"ss.location_id",
"ss.installation_date"
],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 3.01,
"Total Cost": 12.79,
"Plan Rows": 1,
"Plan Width": 20,
"Actual Startup Time": 0.065,
"Actual Total Time": 0.249,
"Actual Rows": 58,
"Actual Loops": 1,
"Output": [
"ss.sensor_id",
"ss.location_id",
"ss.installation_date",
"sensors.type_id"
],
"Inner Unique": true,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "ss",
"Startup Cost": 2.87,
"Total Cost": 4.34,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.054,
"Actual Total Time": 0.143,
"Actual Rows": 58,
"Actual Loops": 1,
"Output": [
"ss.sensor_id",
"ss.location_id",
"ss.installation_date",
"ss.max_date"
],
"Filter": "(ss.installation_date = ss.max_date)",
"Rows Removed by Filter": 17,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 2.87,
"Total Cost": 3.72,
"Plan Rows": 49,
"Plan Width": 24,
"Actual Startup Time": 0.054,
"Actual Total Time": 0.136,
"Actual Rows": 75,
"Actual Loops": 1,
"Output": [
"sensor_location.sensor_id",
"sensor_location.location_id",
"sensor_location.installation_date",
"max(sensor_location.installation_date) OVER (?)"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 2.87,
"Total Cost": 2.99,
"Plan Rows": 49,
"Plan Width": 16,
"Actual Startup Time": 0.044,
"Actual Total Time": 0.048,
"Actual Rows": 75,
"Actual Loops": 1,
"Output": [
"sensor_location.sensor_id",
"sensor_location.location_id",
"sensor_location.installation_date"
],
"Sort Key": [
"sensor_location.sensor_id"
],
"Sort Method": "quicksort",
"Sort Space Used": 28,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "sensor_location",
"Schema": "public",
"Alias": "sensor_location",
"Startup Cost": 0,
"Total Cost": 1.49,
"Plan Rows": 49,
"Plan Width": 16,
"Actual Startup Time": 0.017,
"Actual Total Time": 0.026,
"Actual Rows": 75,
"Actual Loops": 1,
"Output": [
"sensor_location.sensor_id",
"sensor_location.location_id",
"sensor_location.installation_date"
]
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "sensors_pkey",
"Relation Name": "sensors",
"Schema": "public",
"Alias": "sensors",
"Startup Cost": 0.14,
"Total Cost": 8.2,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 58,
"Output": [
"sensors.id",
"sensors.type_id"
],
"Index Cond": "(sensors.id = ss.sensor_id)",
"Rows Removed by Index Recheck": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "sensor_types_pkey",
"Relation Name": "sensor_types",
"Schema": "public",
"Alias": "sensor_types",
"Startup Cost": 0.13,
"Total Cost": 0.34,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 0,
"Actual Loops": 58,
"Output": [
"sensor_types.id",
"sensor_types.sensor_type",
"sensor_types.source",
"sensor_types.origin",
"sensor_types.frequency",
"sensor_types.data",
"sensor_types.description",
"sensor_types.time_created",
"sensor_types.time_updated"
],
"Index Cond": "(sensor_types.id = sensors.type_id)",
"Rows Removed by Index Recheck": 0,
"Filter": "((sensor_types.sensor_type)::text = 'Aranet T&RH'::text)",
"Rows Removed by Filter": 1
}
]
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE location_distances",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0,
"Total Cost": 97.81,
"Plan Rows": 2704,
"Plan Width": 12,
"Actual Startup Time": 0.033,
"Actual Total Time": 1.594,
"Actual Rows": 7396,
"Actual Loops": 1,
"Output": [
"l1.id",
"l2.id",
"(((CASE WHEN ((l1.zone)::text = (l2.zone)::text) THEN 0 ELSE NULL::integer END + CASE WHEN ((l1.aisle)::text = (l2.aisle)::text) THEN 0 ELSE 1 END) + abs((l1.\"column\" - l2.\"column\"))) + abs((l1.shelf - l2.shelf)))"
],
"Inner Unique": false,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "locations",
"Schema": "public",
"Alias": "l1",
"Startup Cost": 0,
"Total Cost": 1.52,
"Plan Rows": 52,
"Plan Width": 22,
"Actual Startup Time": 0.016,
"Actual Total Time": 0.022,
"Actual Rows": 86,
"Actual Loops": 1,
"Output": [
"l1.id",
"l1.zone",
"l1.aisle",
"l1.\"column\"",
"l1.shelf"
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0,
"Total Cost": 1.78,
"Plan Rows": 52,
"Plan Width": 22,
"Actual Startup Time": 0,
"Actual Total Time": 0.003,
"Actual Rows": 86,
"Actual Loops": 86,
"Output": [
"l2.id",
"l2.zone",
"l2.aisle",
"l2.\"column\"",
"l2.shelf"
],
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "locations",
"Schema": "public",
"Alias": "l2",
"Startup Cost": 0,
"Total Cost": 1.52,
"Plan Rows": 52,
"Plan Width": 22,
"Actual Startup Time": 0.012,
"Actual Total Time": 0.016,
"Actual Rows": 86,
"Actual Loops": 1,
"Output": [
"l2.id",
"l2.zone",
"l2.aisle",
"l2.\"column\"",
"l2.shelf"
]
}
]
}
]
},
{
"Node Type": "Hash Join",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE sensor_distances",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.03,
"Total Cost": 64.39,
"Plan Rows": 14,
"Plan Width": 12,
"Actual Startup Time": 0.086,
"Actual Total Time": 3.955,
"Actual Rows": 1806,
"Actual Loops": 1,
"Output": [
"location_distances.id1",
"latest_trh_locations.sensor_id",
"location_distances.distance"
],
"Inner Unique": false,
"Hash Cond": "(location_distances.id2 = latest_trh_locations.location_id)",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "location_distances",
"Alias": "location_distances",
"Startup Cost": 0,
"Total Cost": 54.08,
"Plan Rows": 2704,
"Plan Width": 12,
"Actual Startup Time": 0.034,
"Actual Total Time": 3.203,
"Actual Rows": 7396,
"Actual Loops": 1,
"Output": [
"location_distances.id1",
"location_distances.id2",
"location_distances.distance"
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.02,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.009,
"Actual Total Time": 0.009,
"Actual Rows": 21,
"Actual Loops": 1,
"Output": [
"latest_trh_locations.sensor_id",
"latest_trh_locations.location_id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "latest_trh_locations",
"Alias": "latest_trh_locations",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.006,
"Actual Rows": 21,
"Actual Loops": 1,
"Output": [
"latest_trh_locations.sensor_id",
"latest_trh_locations.location_id"
]
}
]
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE closests_trh_sensors",
"Parallel Aware": false,
"Alias": "ss_1",
"Startup Cost": 0.55,
"Total Cost": 0.97,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 4.66,
"Actual Total Time": 5.368,
"Actual Rows": 83,
"Actual Loops": 1,
"Output": [
"ss_1.location_id",
"ss_1.sensor_id"
],
"Filter": "(ss_1.distance = ss_1.min_distance)",
"Rows Removed by Filter": 1723,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 0.55,
"Total Cost": 0.79,
"Plan Rows": 14,
"Plan Width": 16,
"Actual Startup Time": 4.658,
"Actual Total Time": 5.295,
"Actual Rows": 1806,
"Actual Loops": 1,
"Output": [
"sensor_distances.location_id",
"sensor_distances.sensor_id",
"sensor_distances.distance",
"min(sensor_distances.distance) OVER (?)"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.55,
"Total Cost": 0.58,
"Plan Rows": 14,
"Plan Width": 12,
"Actual Startup Time": 4.647,
"Actual Total Time": 4.699,
"Actual Rows": 1806,
"Actual Loops": 1,
"Output": [
"sensor_distances.location_id",
"sensor_distances.sensor_id",
"sensor_distances.distance"
],
"Sort Key": [
"sensor_distances.location_id"
],
"Sort Method": "quicksort",
"Sort Space Used": 133,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "sensor_distances",
"Alias": "sensor_distances",
"Startup Cost": 0,
"Total Cost": 0.28,
"Plan Rows": 14,
"Plan Width": 12,
"Actual Startup Time": 0.087,
"Actual Total Time": 4.394,
"Actual Rows": 1806,
"Actual Loops": 1,
"Output": [
"sensor_distances.location_id",
"sensor_distances.sensor_id",
"sensor_distances.distance"
],
"Filter": "(sensor_distances.sensor_id IS NOT NULL)",
"Rows Removed by Filter": 0
}
]
}
]
}
]
},
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 2429.02,
"Total Cost": 200464.37,
"Plan Rows": 218,
"Plan Width": 340,
"Actual Startup Time": 353.584,
"Actual Total Time": 10118.314,
"Actual Rows": 217,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"crop_types.name",
"batch_events.event_time",
"batch_events_1.event_time",
"batch_events_2.event_time",
"batch_events_3.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production",
"(avg(aranet_trh_data.temperature))",
"(avg(aranet_trh_data.humidity))",
"(avg((('610.78'::double precision * exp((('17.2694'::double precision * aranet_trh_data.temperature) / (aranet_trh_data.temperature + '237.3'::double precision)))) * ('1'::double precision - (aranet_trh_data.humidity / '100'::double precision)))))"
],
"Inner Unique": true,
"Merge Cond": "(batches.id = batches_1.id)",
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 108.21,
"Total Cost": 108.76,
"Plan Rows": 218,
"Plan Width": 316,
"Actual Startup Time": 0.949,
"Actual Total Time": 1.017,
"Actual Rows": 217,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"crop_types.name",
"batch_events.event_time",
"batch_events_1.event_time",
"batch_events_2.event_time",
"batch_events_3.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Sort Key": [
"batches.id"
],
"Sort Method": "quicksort",
"Sort Space Used": 72,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 82.35,
"Total Cost": 99.75,
"Plan Rows": 218,
"Plan Width": 316,
"Actual Startup Time": 0.672,
"Actual Total Time": 0.819,
"Actual Rows": 217,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"crop_types.name",
"batch_events.event_time",
"batch_events_1.event_time",
"batch_events_2.event_time",
"batch_events_3.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Inner Unique": true,
"Hash Cond": "(batches.crop_type_id = crop_types.id)",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 66.05,
"Total Cost": 82.86,
"Plan Rows": 218,
"Plan Width": 102,
"Actual Startup Time": 0.631,
"Actual Total Time": 0.747,
"Actual Rows": 217,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time",
"batch_events_1.event_time",
"batch_events_2.event_time",
"batch_events_3.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Inner Unique": false,
"Hash Cond": "(batch_events_1.batch_id = batches.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_1",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 207,
"Plan Width": 12,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.059,
"Actual Rows": 207,
"Actual Loops": 1,
"Output": [
"batch_events_1.id",
"batch_events_1.batch_id",
"batch_events_1.location_id",
"batch_events_1.growapp_id",
"batch_events_1.event_type",
"batch_events_1.event_time",
"batch_events_1.next_action_time"
],
"Filter": "(batch_events_1.event_type = 'propagate'::eventtype)",
"Rows Removed by Filter": 447
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 63.74,
"Total Cost": 63.74,
"Plan Rows": 185,
"Plan Width": 94,
"Actual Startup Time": 0.617,
"Actual Total Time": 0.622,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time",
"batch_events_2.event_time",
"batch_events_3.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 30,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 47.58,
"Total Cost": 63.74,
"Plan Rows": 185,
"Plan Width": 94,
"Actual Startup Time": 0.458,
"Actual Total Time": 0.58,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time",
"batch_events_2.event_time",
"batch_events_3.event_time",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Inner Unique": false,
"Hash Cond": "(batch_events_2.batch_id = batches.id)",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 2.17,
"Total Cost": 16.51,
"Plan Rows": 133,
"Plan Width": 30,
"Actual Startup Time": 0.05,
"Actual Total Time": 0.127,
"Actual Rows": 141,
"Actual Loops": 1,
"Output": [
"batch_events_2.event_time",
"batch_events_2.batch_id",
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf"
],
"Inner Unique": true,
"Hash Cond": "(batch_events_2.location_id = locations.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_2",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 133,
"Plan Width": 16,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.059,
"Actual Rows": 141,
"Actual Loops": 1,
"Output": [
"batch_events_2.id",
"batch_events_2.batch_id",
"batch_events_2.location_id",
"batch_events_2.growapp_id",
"batch_events_2.event_type",
"batch_events_2.event_time",
"batch_events_2.next_action_time"
],
"Filter": "(batch_events_2.event_type = 'transfer'::eventtype)",
"Rows Removed by Filter": 513
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 1.52,
"Total Cost": 1.52,
"Plan Rows": 52,
"Plan Width": 22,
"Actual Startup Time": 0.035,
"Actual Total Time": 0.036,
"Actual Rows": 86,
"Actual Loops": 1,
"Output": [
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"locations.id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 13,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "locations",
"Schema": "public",
"Alias": "locations",
"Startup Cost": 0,
"Total Cost": 1.52,
"Plan Rows": 52,
"Plan Width": 22,
"Actual Startup Time": 0.01,
"Actual Total Time": 0.022,
"Actual Rows": 86,
"Actual Loops": 1,
"Output": [
"locations.zone",
"locations.aisle",
"locations.\"column\"",
"locations.shelf",
"locations.id"
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 43.1,
"Total Cost": 43.1,
"Plan Rows": 185,
"Plan Width": 68,
"Actual Startup Time": 0.402,
"Actual Total Time": 0.405,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time",
"batch_events_3.event_time",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 25,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 38.13,
"Total Cost": 43.1,
"Plan Rows": 185,
"Plan Width": 68,
"Actual Startup Time": 0.297,
"Actual Total Time": 0.365,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time",
"batch_events_3.event_time",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Inner Unique": false,
"Hash Cond": "(batch_events_3.batch_id = batches.id)",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 15.39,
"Total Cost": 18.81,
"Plan Rows": 113,
"Plan Width": 44,
"Actual Startup Time": 0.095,
"Actual Total Time": 0.127,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": [
"batch_events_3.event_time",
"batch_events_3.batch_id",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
],
"Inner Unique": true,
"Hash Cond": "(harvests.batch_event_id = batch_events_3.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "harvests",
"Schema": "public",
"Alias": "harvests",
"Startup Cost": 0,
"Total Cost": 3.13,
"Plan Rows": 113,
"Plan Width": 36,
"Actual Startup Time": 0.01,
"Actual Total Time": 0.018,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": [
"harvests.id",
"harvests.batch_event_id",
"harvests.growapp_id",
"harvests.location_id",
"harvests.crop_yield",
"harvests.waste_disease",
"harvests.waste_defect",
"harvests.over_production"
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 13.98,
"Total Cost": 13.98,
"Plan Rows": 113,
"Plan Width": 16,
"Actual Startup Time": 0.077,
"Actual Total Time": 0.077,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": [
"batch_events_3.event_time",
"batch_events_3.batch_id",
"batch_events_3.id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 14,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_3",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 113,
"Plan Width": 16,
"Actual Startup Time": 0.01,
"Actual Total Time": 0.063,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": [
"batch_events_3.event_time",
"batch_events_3.batch_id",
"batch_events_3.id"
],
"Filter": "(batch_events_3.event_type = 'harvest'::eventtype)",
"Rows Removed by Filter": 537
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 20.43,
"Total Cost": 20.43,
"Plan Rows": 185,
"Plan Width": 28,
"Actual Startup Time": 0.196,
"Actual Total Time": 0.198,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 20,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 5.96,
"Total Cost": 20.43,
"Plan Rows": 185,
"Plan Width": 28,
"Actual Startup Time": 0.076,
"Actual Total Time": 0.167,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id",
"batch_events.event_time"
],
"Inner Unique": true,
"Hash Cond": "(batch_events.batch_id = batches.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 185,
"Plan Width": 12,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.073,
"Actual Rows": 189,
"Actual Loops": 1,
"Output": [
"batch_events.id",
"batch_events.batch_id",
"batch_events.location_id",
"batch_events.growapp_id",
"batch_events.event_type",
"batch_events.event_time",
"batch_events.next_action_time"
],
"Filter": "(batch_events.event_type = 'weigh'::eventtype)",
"Rows Removed by Filter": 465
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 3.76,
"Total Cost": 3.76,
"Plan Rows": 176,
"Plan Width": 20,
"Actual Startup Time": 0.057,
"Actual Total Time": 0.057,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 19,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batches",
"Schema": "public",
"Alias": "batches",
"Startup Cost": 0,
"Total Cost": 3.76,
"Plan Rows": 176,
"Plan Width": 20,
"Actual Startup Time": 0.009,
"Actual Total Time": 0.031,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches.id",
"batches.tray_size",
"batches.number_of_trays",
"batches.crop_type_id"
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 12.8,
"Total Cost": 12.8,
"Plan Rows": 280,
"Plan Width": 222,
"Actual Startup Time": 0.034,
"Actual Total Time": 0.034,
"Actual Rows": 22,
"Actual Loops": 1,
"Output": [
"crop_types.name",
"crop_types.id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 10,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "crop_types",
"Schema": "public",
"Alias": "crop_types",
"Startup Cost": 0,
"Total Cost": 12.8,
"Plan Rows": 280,
"Plan Width": 222,
"Actual Startup Time": 0.017,
"Actual Total Time": 0.02,
"Actual Rows": 22,
"Actual Loops": 1,
"Output": [
"crop_types.name",
"crop_types.id"
]
}
]
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 2320.81,
"Total Cost": 200350.68,
"Plan Rows": 176,
"Plan Width": 28,
"Actual Startup Time": 352.631,
"Actual Total Time": 10116.742,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_1.id",
"avg(aranet_trh_data.temperature)",
"avg(aranet_trh_data.humidity)",
"avg((('610.78'::double precision * exp((('17.2694'::double precision * aranet_trh_data.temperature) / (aranet_trh_data.temperature + '237.3'::double precision)))) * ('1'::double precision - (aranet_trh_data.humidity / '100'::double precision))))"
],
"Group Key": [
"batches_1.id"
],
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE propagation_trh_sensors",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.14,
"Total Cost": 5.97,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.212,
"Actual Total Time": 0.736,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": [
"sensors_1.id"
],
"Inner Unique": true,
"Join Filter": "(latest_trh_locations_1.location_id = locations_1.id)",
"Rows Removed by Join Filter": 19,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.14,
"Total Cost": 4.31,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.126,
"Actual Total Time": 0.389,
"Actual Rows": 21,
"Actual Loops": 1,
"Output": [
"sensors_1.id",
"latest_trh_locations_1.location_id"
],
"Inner Unique": true,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "latest_trh_locations",
"Alias": "latest_trh_locations_1",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.117,
"Actual Total Time": 0.354,
"Actual Rows": 21,
"Actual Loops": 1,
"Output": [
"latest_trh_locations_1.sensor_id",
"latest_trh_locations_1.location_id",
"latest_trh_locations_1.installation_date"
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "sensors_pkey",
"Relation Name": "sensors",
"Schema": "public",
"Alias": "sensors_1",
"Startup Cost": 0.14,
"Total Cost": 4.16,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 21,
"Output": [
"sensors_1.id"
],
"Index Cond": "(sensors_1.id = latest_trh_locations_1.sensor_id)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0
}
]
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "locations",
"Schema": "public",
"Alias": "locations_1",
"Startup Cost": 0,
"Total Cost": 1.65,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.01,
"Actual Total Time": 0.01,
"Actual Rows": 1,
"Actual Loops": 21,
"Output": [
"locations_1.id",
"locations_1.zone",
"locations_1.aisle",
"locations_1.\"column\"",
"locations_1.shelf"
],
"Filter": "((locations_1.zone)::text = 'Propagation'::text)",
"Rows Removed by Filter": 85
}
]
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 2314.84,
"Total Cost": 172984.52,
"Plan Rows": 911904,
"Plan Width": 20,
"Actual Startup Time": 11.982,
"Actual Total Time": 10045.612,
"Actual Rows": 180934,
"Actual Loops": 1,
"Output": [
"batches_1.id",
"aranet_trh_data.temperature",
"aranet_trh_data.humidity"
],
"Inner Unique": false,
"Join Filter": "((aranet_trh_data.\"timestamp\" >= batch_events_4.event_time) AND (aranet_trh_data.\"timestamp\" <= batch_events_5.event_time))",
"Rows Removed by Join Filter": 33141534,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 47.12,
"Total Cost": 50.35,
"Plan Rows": 207,
"Plan Width": 20,
"Actual Startup Time": 0.398,
"Actual Total Time": 1.012,
"Actual Rows": 218,
"Actual Loops": 1,
"Output": [
"batches_1.id",
"batch_events_4.event_time",
"batch_events_5.event_time"
],
"Inner Unique": false,
"Merge Cond": "(batches_1.id = batch_events_5.batch_id)",
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 28.45,
"Total Cost": 28.97,
"Plan Rows": 207,
"Plan Width": 12,
"Actual Startup Time": 0.281,
"Actual Total Time": 0.343,
"Actual Rows": 218,
"Actual Loops": 1,
"Output": [
"batches_1.id",
"batch_events_4.event_time"
],
"Sort Key": [
"batches_1.id"
],
"Sort Method": "quicksort",
"Sort Space Used": 35,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 5.96,
"Total Cost": 20.49,
"Plan Rows": 207,
"Plan Width": 12,
"Actual Startup Time": 0.059,
"Actual Total Time": 0.17,
"Actual Rows": 218,
"Actual Loops": 1,
"Output": [
"batches_1.id",
"batch_events_4.event_time"
],
"Inner Unique": true,
"Hash Cond": "(batch_events_4.batch_id = batches_1.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_4",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 207,
"Plan Width": 12,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.074,
"Actual Rows": 207,
"Actual Loops": 1,
"Output": [
"batch_events_4.id",
"batch_events_4.batch_id",
"batch_events_4.location_id",
"batch_events_4.growapp_id",
"batch_events_4.event_type",
"batch_events_4.event_time",
"batch_events_4.next_action_time"
],
"Filter": "(batch_events_4.event_type = 'propagate'::eventtype)",
"Rows Removed by Filter": 447
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 3.76,
"Total Cost": 3.76,
"Plan Rows": 176,
"Plan Width": 4,
"Actual Startup Time": 0.045,
"Actual Total Time": 0.046,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_1.id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 15,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batches",
"Schema": "public",
"Alias": "batches_1",
"Startup Cost": 0,
"Total Cost": 3.76,
"Plan Rows": 176,
"Plan Width": 4,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.025,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_1.id"
]
}
]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 18.67,
"Total Cost": 19,
"Plan Rows": 133,
"Plan Width": 12,
"Actual Startup Time": 0.115,
"Actual Total Time": 0.201,
"Actual Rows": 166,
"Actual Loops": 1,
"Output": [
"batch_events_5.batch_id",
"batch_events_5.event_time"
],
"Sort Key": [
"batch_events_5.batch_id"
],
"Sort Method": "quicksort",
"Sort Space Used": 31,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_5",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 133,
"Plan Width": 12,
"Actual Startup Time": 0.015,
"Actual Total Time": 0.091,
"Actual Rows": 141,
"Actual Loops": 1,
"Output": [
"batch_events_5.batch_id",
"batch_events_5.event_time"
],
"Filter": "(batch_events_5.event_type = 'transfer'::eventtype)",
"Rows Removed by Filter": 513
}
]
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 2267.72,
"Total Cost": 29408.4,
"Plan Rows": 39648,
"Plan Width": 24,
"Actual Startup Time": 0.086,
"Actual Total Time": 34.936,
"Actual Rows": 152855,
"Actual Loops": 218,
"Output": [
"aranet_trh_data.temperature",
"aranet_trh_data.humidity",
"aranet_trh_data.\"timestamp\""
],
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2267.72,
"Total Cost": 29210.16,
"Plan Rows": 39648,
"Plan Width": 24,
"Actual Startup Time": 11.566,
"Actual Total Time": 65.608,
"Actual Rows": 152855,
"Actual Loops": 1,
"Output": [
"aranet_trh_data.temperature",
"aranet_trh_data.humidity",
"aranet_trh_data.\"timestamp\""
],
"Inner Unique": false,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.02,
"Total Cost": 0.03,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.74,
"Actual Total Time": 0.743,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": [
"propagation_trh_sensors.id"
],
"Group Key": [
"propagation_trh_sensors.id"
],
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "propagation_trh_sensors",
"Alias": "propagation_trh_sensors",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.213,
"Actual Total Time": 0.737,
"Actual Rows": 2,
"Actual Loops": 1,
"Output": [
"propagation_trh_sensors.id"
]
}
]
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "aranet_trh_data",
"Schema": "public",
"Alias": "aranet_trh_data",
"Startup Cost": 2267.7,
"Total Cost": 28813.65,
"Plan Rows": 39648,
"Plan Width": 28,
"Actual Startup Time": 6.48,
"Actual Total Time": 22.861,
"Actual Rows": 76428,
"Actual Loops": 2,
"Output": [
"aranet_trh_data.id",
"aranet_trh_data.sensor_id",
"aranet_trh_data.\"timestamp\"",
"aranet_trh_data.temperature",
"aranet_trh_data.humidity",
"aranet_trh_data.time_created",
"aranet_trh_data.time_updated"
],
"Recheck Cond": "(aranet_trh_data.sensor_id = propagation_trh_sensors.id)",
"Rows Removed by Index Recheck": 0,
"Exact Heap Blocks": 11414,
"Lossy Heap Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "aranet_trh_data_sensor_id_timestamp_key",
"Startup Cost": 0,
"Total Cost": 2257.79,
"Plan Rows": 39648,
"Plan Width": 0,
"Actual Startup Time": 5.439,
"Actual Total Time": 5.439,
"Actual Rows": 76428,
"Actual Loops": 2,
"Index Cond": "(aranet_trh_data.sensor_id = propagation_trh_sensors.id)"
}
]
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 339770.02,
"Total Cost": 339770.02,
"Plan Rows": 176,
"Plan Width": 28,
"Actual Startup Time": 2917.363,
"Actual Total Time": 2917.367,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"grow_trh.avg_temp",
"grow_trh.avg_rh",
"grow_trh.avg_vpd",
"grow_trh.batch_id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 17,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "grow_trh",
"Startup Cost": 339765.18,
"Total Cost": 339770.02,
"Plan Rows": 176,
"Plan Width": 28,
"Actual Startup Time": 2917.252,
"Actual Total Time": 2917.331,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"grow_trh.avg_temp",
"grow_trh.avg_rh",
"grow_trh.avg_vpd",
"grow_trh.batch_id"
],
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 339765.18,
"Total Cost": 339768.26,
"Plan Rows": 176,
"Plan Width": 28,
"Actual Startup Time": 2917.25,
"Actual Total Time": 2917.311,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"avg(aranet_trh_data_1.temperature)",
"avg(aranet_trh_data_1.humidity)",
"avg((('610.78'::double precision * exp((('17.2694'::double precision * aranet_trh_data_1.temperature) / (aranet_trh_data_1.temperature + '237.3'::double precision)))) * ('1'::double precision - (aranet_trh_data_1.humidity / '100'::double precision))))"
],
"Group Key": [
"batches_2.id"
],
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 174169.3,
"Total Cost": 316505.22,
"Plan Rows": 775332,
"Plan Width": 20,
"Actual Startup Time": 2810.286,
"Actual Total Time": 2907.22,
"Actual Rows": 21031,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"aranet_trh_data_1.temperature",
"aranet_trh_data_1.humidity"
],
"Inner Unique": false,
"Merge Cond": "(closests_trh_sensors.sensor_id = aranet_trh_data_1.sensor_id)",
"Join Filter": "((aranet_trh_data_1.\"timestamp\" >= batch_events_6.event_time) AND (aranet_trh_data_1.\"timestamp\" <= batch_events_7.event_time))",
"Rows Removed by Join Filter": 505224,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 45.23,
"Total Cost": 45.67,
"Plan Rows": 176,
"Plan Width": 24,
"Actual Startup Time": 5.866,
"Actual Total Time": 5.884,
"Actual Rows": 191,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"batch_events_6.event_time",
"batch_events_7.event_time",
"closests_trh_sensors.sensor_id"
],
"Sort Key": [
"closests_trh_sensors.sensor_id"
],
"Sort Method": "quicksort",
"Sort Space Used": 38,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 22.53,
"Total Cost": 38.67,
"Plan Rows": 176,
"Plan Width": 24,
"Actual Startup Time": 5.719,
"Actual Total Time": 5.834,
"Actual Rows": 191,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"batch_events_6.event_time",
"batch_events_7.event_time",
"closests_trh_sensors.sensor_id"
],
"Inner Unique": false,
"Hash Cond": "(batch_events_6.location_id = closests_trh_sensors.location_id)",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 22.5,
"Total Cost": 37.96,
"Plan Rows": 176,
"Plan Width": 24,
"Actual Startup Time": 0.306,
"Actual Total Time": 0.395,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"batch_events_6.location_id",
"batch_events_6.event_time",
"batch_events_7.event_time"
],
"Inner Unique": false,
"Hash Cond": "(batch_events_7.batch_id = batches_2.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_7",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 113,
"Plan Width": 12,
"Actual Startup Time": 0.01,
"Actual Total Time": 0.064,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": [
"batch_events_7.id",
"batch_events_7.batch_id",
"batch_events_7.location_id",
"batch_events_7.growapp_id",
"batch_events_7.event_type",
"batch_events_7.event_time",
"batch_events_7.next_action_time"
],
"Filter": "(batch_events_7.event_type = 'harvest'::eventtype)",
"Rows Removed by Filter": 537
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 20.3,
"Total Cost": 20.3,
"Plan Rows": 176,
"Plan Width": 16,
"Actual Startup Time": 0.285,
"Actual Total Time": 0.286,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"batch_events_6.location_id",
"batch_events_6.event_time"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 17,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 5.96,
"Total Cost": 20.3,
"Plan Rows": 176,
"Plan Width": 16,
"Actual Startup Time": 0.168,
"Actual Total Time": 0.267,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_2.id",
"batch_events_6.location_id",
"batch_events_6.event_time"
],
"Inner Unique": true,
"Hash Cond": "(batch_events_6.batch_id = batches_2.id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batch_events",
"Schema": "public",
"Alias": "batch_events_6",
"Startup Cost": 0,
"Total Cost": 13.98,
"Plan Rows": 133,
"Plan Width": 16,
"Actual Startup Time": 0.018,
"Actual Total Time": 0.073,
"Actual Rows": 141,
"Actual Loops": 1,
"Output": [
"batch_events_6.id",
"batch_events_6.batch_id",
"batch_events_6.location_id",
"batch_events_6.growapp_id",
"batch_events_6.event_type",
"batch_events_6.event_time",
"batch_events_6.next_action_time"
],
"Filter": "(batch_events_6.event_type = 'transfer'::eventtype)",
"Rows Removed by Filter": 513
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 3.76,
"Total Cost": 3.76,
"Plan Rows": 176,
"Plan Width": 4,
"Actual Startup Time": 0.082,
"Actual Total Time": 0.082,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_2.id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 15,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "batches",
"Schema": "public",
"Alias": "batches_2",
"Startup Cost": 0,
"Total Cost": 3.76,
"Plan Rows": 176,
"Plan Width": 4,
"Actual Startup Time": 0.045,
"Actual Total Time": 0.057,
"Actual Rows": 190,
"Actual Loops": 1,
"Output": [
"batches_2.id"
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.02,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 5.4,
"Actual Total Time": 5.4,
"Actual Rows": 83,
"Actual Loops": 1,
"Output": [
"closests_trh_sensors.location_id",
"closests_trh_sensors.sensor_id"
],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 12,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "closests_trh_sensors",
"Alias": "closests_trh_sensors",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 4.661,
"Actual Total Time": 5.384,
"Actual Rows": 83,
"Actual Loops": 1,
"Output": [
"closests_trh_sensors.location_id",
"closests_trh_sensors.sensor_id"
]
}
]
}
]
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 174124.07,
"Total Cost": 179674.74,
"Plan Rows": 1110134,
"Plan Width": 28,
"Actual Startup Time": 2395.795,
"Actual Total Time": 2730.099,
"Actual Rows": 1657343,
"Actual Loops": 1,
"Output": [
"aranet_trh_data_1.temperature",
"aranet_trh_data_1.humidity",
"aranet_trh_data_1.\"timestamp\"",
"aranet_trh_data_1.sensor_id"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 174124.07,
"Total Cost": 176899.41,
"Plan Rows": 1110134,
"Plan Width": 28,
"Actual Startup Time": 2395.792,
"Actual Total Time": 2628.499,
"Actual Rows": 1145838,
"Actual Loops": 1,
"Output": [
"aranet_trh_data_1.temperature",
"aranet_trh_data_1.humidity",
"aranet_trh_data_1.\"timestamp\"",
"aranet_trh_data_1.sensor_id"
],
"Sort Key": [
"aranet_trh_data_1.sensor_id"
],
"Sort Method": "external merge",
"Sort Space Used": 42640,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "aranet_trh_data",
"Schema": "public",
"Alias": "aranet_trh_data_1",
"Startup Cost": 0,
"Total Cost": 36092.34,
"Plan Rows": 1110134,
"Plan Width": 28,
"Actual Startup Time": 320.683,
"Actual Total Time": 593.188,
"Actual Rows": 1145838,
"Actual Loops": 1,
"Output": [
"aranet_trh_data_1.temperature",
"aranet_trh_data_1.humidity",
"aranet_trh_data_1.\"timestamp\"",
"aranet_trh_data_1.sensor_id"
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
"Planning Time": 28.554,
"Triggers": [],
"Execution Time": 13227.233
}
] WITH latest_trh_locations AS (
SELECT ss.sensor_id, ss.location_id, ss.installation_date
FROM (
SELECT
sensor_id,
location_id,
installation_date,
max(installation_date) OVER (PARTITION BY sensor_id) AS max_date
FROM sensor_location
) AS ss
JOIN sensors
ON (sensors.id = ss.sensor_id)
JOIN sensor_types
ON (sensors.type_id = sensor_types.id)
WHERE ss.installation_date = ss.max_date
AND sensor_types.sensor_type = 'Aranet T&RH'
),
location_distances AS (
SELECT
l1.id AS id1,
l2.id AS id2,
(
(CASE WHEN l1.zone = l2.zone THEN 0 ELSE NULL END)
+ (CASE WHEN l1.aisle = l2.aisle THEN 0 ELSE 1 END)
+ abs(l1.column - l2.column)
+ abs(l1.shelf - l2.shelf)
) AS distance
FROM locations l1, locations l2
),
sensor_distances AS (
SELECT
location_distances.id1 AS location_id,
latest_trh_locations.sensor_id,
location_distances.distance
FROM location_distances
JOIN latest_trh_locations
ON latest_trh_locations.location_id = location_distances.id2
),
closests_trh_sensors AS (
SELECT ss.location_id, ss.sensor_id
FROM (
SELECT
location_id,
sensor_id,
distance,
min(distance) OVER (PARTITION BY location_id) AS min_distance
FROM sensor_distances
WHERE sensor_id IS NOT NULL
) AS ss
WHERE ss.distance = ss.min_distance
),
first_event_time AS (
SELECT min(event_time) as min_time
FROM batch_events
)
SELECT
batches.id AS batch_id,
crop_types.name AS crop_type_name,
batches.tray_size,
batches.number_of_trays,
weigh_events.event_time AS weigh_time,
propagate_events.event_time AS propagate_time,
transfer_events.event_time AS transfer_time,
locations.zone,
locations.aisle,
locations.column,
locations.shelf,
harvest_events.event_time AS harvest_time,
harvests.crop_yield,
harvests.waste_disease,
harvests.waste_defect,
harvests.over_production,
harvest_events.event_time - transfer_events.event_time AS grow_time,
grow_trh.avg_temp AS avg_grow_temperature,
grow_trh.avg_rh AS avg_grow_humidity,
grow_trh.avg_vpd AS avg_grow_vpd,
propagate_trh.avg_temp AS avg_propagation_temperature,
propagate_trh.avg_rh AS avg_propagation_humidity,
propagate_trh.avg_vpd AS avg_propagation_vpd
FROM batches
INNER JOIN
crop_types
ON (batches.crop_type_id = crop_types.id)
INNER JOIN (
SELECT batch_id, event_time
FROM batch_events
WHERE batch_events.event_type = 'weigh'
)
AS weigh_events
ON (batches.id = weigh_events.batch_id)
LEFT OUTER JOIN (
SELECT batch_id, event_time
FROM batch_events
WHERE batch_events.event_type = 'propagate'
)
AS propagate_events
ON (batches.id = propagate_events.batch_id)
LEFT OUTER JOIN (
SELECT batch_id, event_time, location_id
FROM batch_events
WHERE batch_events.event_type = 'transfer'
)
AS transfer_events
ON (batches.id = transfer_events.batch_id)
LEFT OUTER JOIN (
SELECT id, batch_id, event_time
FROM batch_events
WHERE batch_events.event_type = 'harvest'
)
AS harvest_events
ON (batches.id = harvest_events.batch_id)
LEFT OUTER JOIN
locations ON (locations.id = transfer_events.location_id)
LEFT OUTER JOIN
harvests ON (harvests.batch_event_id = harvest_events.id)
LEFT OUTER JOIN (
SELECT
batches.id AS batch_id,
avg(trh.temperature) AS avg_temp,
avg(trh.humidity) AS avg_rh,
avg(trh.vpd) AS avg_vpd
FROM batches
LEFT OUTER JOIN (
SELECT batch_id, event_time, location_id
FROM batch_events
WHERE batch_events.event_type = 'transfer'
)
AS transfer_events
ON (batches.id = transfer_events.batch_id)
LEFT OUTER JOIN (
SELECT id, batch_id, event_time
FROM batch_events
WHERE batch_events.event_type = 'harvest'
)
AS harvest_events
ON (batches.id = harvest_events.batch_id)
LEFT OUTER JOIN
closests_trh_sensors
ON transfer_events.location_id = closests_trh_sensors.location_id
LEFT OUTER JOIN (
SELECT
sensor_id,
temperature,
humidity,
(
610.78
* exp(17.2694 * temperature / (temperature + 237.3))
* (1.0 - humidity / 100.0)
) AS vpd,
timestamp
FROM aranet_trh_data
)
AS trh
ON (
trh.timestamp BETWEEN transfer_events.event_time
AND harvest_events.event_time
AND closests_trh_sensors.sensor_id = trh.sensor_id
)
GROUP BY batches.id
) AS grow_trh
ON (grow_trh.batch_id = batches.id)
LEFT OUTER JOIN (
WITH propagation_trh_sensors AS (
SELECT sensors.id
FROM sensors
JOIN latest_trh_locations
ON latest_trh_locations.sensor_id = sensors.id
JOIN locations
ON locations.id = latest_trh_locations.location_id
WHERE (locations.zone = 'Propagation')
)
SELECT
batches.id AS batch_id,
avg(trh.temperature) AS avg_temp,
avg(trh.humidity) AS avg_rh,
avg(trh.vpd) AS avg_vpd
FROM batches
LEFT OUTER JOIN (
SELECT batch_id, event_time
FROM batch_events
WHERE batch_events.event_type = 'propagate'
) AS propagate_events
ON (batches.id = propagate_events.batch_id)
LEFT OUTER JOIN (
SELECT id, batch_id, event_time
FROM batch_events
WHERE batch_events.event_type = 'transfer'
) AS transfer_events
ON (batches.id = transfer_events.batch_id)
LEFT OUTER JOIN (
SELECT
sensor_id,
temperature,
humidity,
(
610.78
* exp(17.2694 * temperature / (temperature + 237.3))
* (1.0 - humidity / 100.0)
) AS vpd,
timestamp
FROM aranet_trh_data
WHERE (sensor_id IN (SELECT id FROM propagation_trh_sensors))
) AS trh
ON (
trh.timestamp
BETWEEN propagate_events.event_time
AND transfer_events.event_time
)
GROUP BY batches.id
) AS propagate_trh
ON (propagate_trh.batch_id = batches.id)
;| Table | Count | Time | |
|---|---|---|---|
| aranet_trh_data | 2 | 628ms | 5% |
| #84 Seq Scan | 593ms | 4% | |
| #65 Bitmap Heap Scan | 34.8ms | 0% | |
| batch_events | 8 | 0.556ms | 0% |
| #60 Seq Scan | 0.091ms | 0% | |
| #56 Seq Scan | 0.074ms | 0% | |
| #77 Seq Scan | 0.073ms | 0% | |
| #41 Seq Scan | 0.073ms | 0% | |
| #74 Seq Scan | 0.064ms | 0% | |
| #38 Seq Scan | 0.063ms | 0% | |
| #30 Seq Scan | 0.059ms | 0% | |
| #26 Seq Scan | 0.059ms | 0% | |
| locations | 4 | 0.27ms | 0% |
| #51 Seq Scan | 0.21ms | 0% | |
| #11 Seq Scan | 0.022ms | 0% | |
| #32 Seq Scan | 0.022ms | 0% | |
| #13 Seq Scan | 0.016ms | 0% | |
| batches | 3 | 0.113ms | 0% |
| #79 Seq Scan | 0.057ms | 0% | |
| #43 Seq Scan | 0.031ms | 0% | |
| #58 Seq Scan | 0.025ms | 0% | |
| sensors | 2 | 0.079ms | 0% |
| #8 Index Scan | 0.058ms | 0% | |
| #50 Index Only Scan | 0.021ms | 0% | |
| sensor_types | 1 | 0.058ms | 0% |
| #9 Index Scan | 0.058ms | 0% | |
| sensor_location | 1 | 0.026ms | 0% |
| #7 Seq Scan | 0.026ms | 0% | |
| crop_types | 1 | 0.02ms | 0% |
| #45 Seq Scan | 0.02ms | 0% | |
| harvests | 1 | 0.018ms | 0% |
| #36 Seq Scan | 0.018ms | 0% | |
| Node Type | Count | Time | |
|---|---|---|---|
| Materialize | 3 | 7s 652ms | 58% |
| #61 Materialize | 7s 550ms | 57% | |
| #82 Materialize | 102ms | 1% | |
| #12 Materialize | 0.242ms | 0% | |
| Nested Loop | 7 | 2s 449ms | 19% |
| #52 Nested Loop | 2s 429ms | 18% | |
| #62 Nested Loop | 19.1ms | 0% | |
| #10 Nested Loop | 1.31ms | 0% | |
| #47 Nested Loop | 0.137ms | 0% | |
| #3 Nested Loop | 0.048ms | 0% | |
| #2 Nested Loop | 0.042ms | 0% | |
| #48 Nested Loop | 0.014ms | 0% | |
| Sort | 7 | 2s 36.2ms | 15% |
| #83 Sort | 2s 35.3ms | 15% | |
| #20 Sort | 0.305ms | 0% | |
| #23 Sort | 0.198ms | 0% | |
| #54 Sort | 0.173ms | 0% | |
| #59 Sort | 0.11ms | 0% | |
| #71 Sort | 0.05ms | 0% | |
| #6 Sort | 0.022ms | 0% | |
| Seq Scan | 19 | 594ms | 4% |
| #84 Seq Scan | 593ms | 4% | |
| #51 Seq Scan | 0.21ms | 0% | |
| #60 Seq Scan | 0.091ms | 0% | |
| #56 Seq Scan | 0.074ms | 0% | |
| #77 Seq Scan | 0.073ms | 0% | |
| #41 Seq Scan | 0.073ms | 0% | |
| #74 Seq Scan | 0.064ms | 0% | |
| #38 Seq Scan | 0.063ms | 0% | |
| #30 Seq Scan | 0.059ms | 0% | |
| #26 Seq Scan | 0.059ms | 0% | |
| #79 Seq Scan | 0.057ms | 0% | |
| #43 Seq Scan | 0.031ms | 0% | |
| #7 Seq Scan | 0.026ms | 0% | |
| #58 Seq Scan | 0.025ms | 0% | |
| #11 Seq Scan | 0.022ms | 0% | |
| #32 Seq Scan | 0.022ms | 0% | |
| #45 Seq Scan | 0.02ms | 0% | |
| #36 Seq Scan | 0.018ms | 0% | |
| #13 Seq Scan | 0.016ms | 0% | |
| Merge Join | 3 | 172ms | 1% |
| #70 Merge Join | 171ms | 1% | |
| #22 Merge Join | 0.555ms | 0% | |
| #53 Merge Join | 0.468ms | 0% | |
| GroupAggregate | 1 | 71.1ms | 1% |
| #46 GroupAggregate | 71.1ms | 1% | |
| Bitmap Heap Scan | 1 | 34.8ms | 0% |
| #65 Bitmap Heap Scan | 34.8ms | 0% | |
| CTE Scan | 6 | 14.1ms | 0% |
| #81 CTE Scan | 5.38ms | 0% | |
| #21 CTE Scan | 4.39ms | 0% | |
| #15 CTE Scan | 3.2ms | 0% | |
| #64 CTE Scan | 0.737ms | 0% | |
| #49 CTE Scan | 0.354ms | 0% | |
| #17 CTE Scan | 0.006ms | 0% | |
| Bitmap Index Scan | 1 | 10.9ms | 0% |
| #66 Bitmap Index Scan | 10.9ms | 0% | |
| HashAggregate | 2 | 10.1ms | 0% |
| #69 HashAggregate | 10.1ms | 0% | |
| #63 HashAggregate | 0.006ms | 0% | |
| Hash Join | 13 | 1.88ms | 0% |
| #14 Hash Join | 0.743ms | 0% | |
| #1 Hash Join | 0.599ms | 0% | |
| #76 Hash Join | 0.112ms | 0% | |
| #25 Hash Join | 0.066ms | 0% | |
| #55 Hash Join | 0.05ms | 0% | |
| #28 Hash Join | 0.048ms | 0% | |
| #73 Hash Join | 0.045ms | 0% | |
| #34 Hash Join | 0.04ms | 0% | |
| #72 Hash Join | 0.039ms | 0% | |
| #24 Hash Join | 0.038ms | 0% | |
| #40 Hash Join | 0.037ms | 0% | |
| #35 Hash Join | 0.032ms | 0% | |
| #29 Hash Join | 0.032ms | 0% | |
| WindowAgg | 2 | 0.684ms | 0% |
| #19 WindowAgg | 0.596ms | 0% | |
| #5 WindowAgg | 0.088ms | 0% | |
| Hash | 13 | 0.301ms | 0% |
| #27 Hash | 0.042ms | 0% | |
| #33 Hash | 0.04ms | 0% | |
| #67 Hash | 0.036ms | 0% | |
| #39 Hash | 0.031ms | 0% | |
| #42 Hash | 0.026ms | 0% | |
| #78 Hash | 0.025ms | 0% | |
| #57 Hash | 0.021ms | 0% | |
| #75 Hash | 0.019ms | 0% | |
| #80 Hash | 0.016ms | 0% | |
| #44 Hash | 0.014ms | 0% | |
| #37 Hash | 0.014ms | 0% | |
| #31 Hash | 0.014ms | 0% | |
| #16 Hash | 0.003ms | 0% | |
| Index Scan | 2 | 0.116ms | 0% |
| #9 Index Scan | 0.058ms | 0% | |
| #8 Index Scan | 0.058ms | 0% | |
| Subquery Scan | 3 | 0.1ms | 0% |
| #18 Subquery Scan | 0.073ms | 0% | |
| #68 Subquery Scan | 0.02ms | 0% | |
| #4 Subquery Scan | 0.007ms | 0% | |
| Index Only Scan | 1 | 0.021ms | 0% |
| #50 Index Only Scan | 0.021ms | 0% | |