WITH valid_bins AS (
SELECT a_1.intersection_uid,
a_1.class_type_id,
a_1.dt + b_1.b::time without time zone AS datetime_bin,
a_1.period_type
FROM volumes.report_dates_view a_1
CROSS JOIN (SELECT generate_series('2017-01-01 00:00:00'::timestamp without time zone, '2017-01-01 23:45:00'::timestamp without time zone, '00:15:00'::interval)::TIME) b_1(b)
), int_avg AS (
SELECT volumes_15min_by_class.intersection_uid,
class_type,
class_type_id,
volumes_15min_by_class.dir,
volumes_15min_by_class.leg,
volumes_15min_by_class.period_type,
volumes_15min_by_class.datetime_bin::time without time zone AS time_bin,
avg(volumes_15min_by_class.total_volume) AS avg_volume
FROM volumes.volumes_15min_by_class
INNER JOIN volumes.class_types USING (class_type_id)
GROUP BY intersection_uid, class_type, class_type_id, period_type, dir, leg, time_bin
)
, volumes_15 AS (
SELECT a.intersection_uid,
a.period_type,
a.datetime_bin,
class_type,
b.dir,
b.leg,
COALESCE(c.total_volume, b.avg_volume) AS volume
FROM valid_bins a
JOIN int_avg b USING (intersection_uid, class_type_id, period_type)
LEFT JOIN volumes.volumes_15min_by_class c USING (datetime_bin, intersection_uid, class_type_id, dir, leg, period_type)
WHERE b.time_bin = a.datetime_bin::time without time zone
ORDER BY a.intersection_uid, a.period_type, a.datetime_bin, class_type, b.dir, b.leg
),
intersection_days AS (
SELECT intersection_uid, datetime_bin::date AS dt, COUNT(DISTINCT datetime_bin) AS num_daily_observations
FROM volumes_15
GROUP BY intersection_uid, dt
)
, daily AS(
SELECT a.intersection_uid,
c.intersection_name,
c.street_main,
c.street_cross,
a.class_type,
a.dir,
a.period_type,
a.datetime_bin::date AS dt,
b.period_name ||
CASE WHEN period_id = 7 THEN '' --24 hour
ELSE --Append period range
(((' ('::text || to_char(lower(b.period_range)::interval, 'HH24:MM'::text)) || '-'::text) || to_char(upper(b.period_range)::interval, 'HH24:MM'::text)) || ')'::text
END
AS "period_name",
sum(a.volume) AS total_volume
FROM volumes_15 a
CROSS JOIN volumes.periods b
JOIN volumes.intersections c USING (intersection_uid)
JOIN intersection_days d ON d.intersection_uid = a.intersection_uid AND a.datetime_bin::date = dt
WHERE a.datetime_bin::time without time zone <@ b.period_range AND report_flag AND (a.dir = ANY (ARRAY['EB'::text, 'WB'::text]))
AND (period_id != 7 OR num_daily_observations > 23 * 4) --Make sure 24hour counts have data for most of the 24-hour period
AND (c.street_cross = ANY (ARRAY['Bathurst'::text, 'Spadina'::text, 'Bay'::text, 'Jarvis'::text])) AND (c.street_cross = 'Bathurst'::text AND (a.leg = ANY (ARRAY['E'::text, 'S'::text, 'N'::text])) OR c.street_cross = 'Jarvis'::text AND (a.leg = ANY (ARRAY['W'::text, 'S'::text, 'N'::text])) OR (c.street_cross <> ALL (ARRAY['Bathurst'::text, 'Jarvis'::text])) AND (a.dir = 'EB'::text AND (a.leg = ANY (ARRAY['W'::text, 'N'::text, 'S'::text])) OR a.dir = 'WB'::text AND (a.leg = ANY (ARRAY['E'::text, 'N'::text, 'S'::text])))) AND NOT ((a.class_type = ANY (ARRAY['Vehicles'::text, 'Cyclists'::text])) AND (a.dir = 'EB'::text AND (c.street_main = ANY (ARRAY['Wellington'::text, 'Richmond'::text])) OR a.dir = 'WB'::text AND c.street_main = 'Adelaide'::text))
GROUP BY a.intersection_uid, c.intersection_name, c.street_main, c.street_cross, a.period_type, a.class_type, a.dir, (a.datetime_bin::date), b.period_name, b.period_range, period_id
)
SELECT period_type as aggregation_period, intersections.int_id,
centreline_intersection.intersec5 AS intersection_name, px,
CASE
WHEN class_type = 'Buses'::text THEN 'Buses and streetcars'::text
ELSE class_type
END AS classification,
dir, period_name, round(AVG(total_volume), -1)::int as volume
FROM daily
JOIN volumes.intersections USING (intersection_uid)
JOIN gis.centreline_intersection USING (int_id)
GROUP BY period_type, int_id, class_type, intersec5, px, dir, period_name;[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Startup Cost": 2561227.12,
"Total Cost": 2561227.21,
"Plan Rows": 2,
"Plan Width": 236,
"Actual Startup Time": 111723.66,
"Actual Total Time": 111770.631,
"Actual Rows": 7364,
"Actual Loops": 1,
"Group Key": [
"daily.period_type",
"intersections.int_id",
"daily.class_type",
"centreline_intersection.intersec5",
"intersections.px",
"daily.dir",
"daily.period_name"
],
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE valid_bins",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0,
"Total Cost": 72304.65,
"Plan Rows": 4814000,
"Plan Width": 25,
"Actual Startup Time": 0.037,
"Actual Total Time": 384.556,
"Actual Rows": 422208,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "report_dates_view",
"Alias": "a_1",
"Startup Cost": 0,
"Total Cost": 77.14,
"Plan Rows": 4814,
"Plan Width": 21,
"Actual Startup Time": 0.013,
"Actual Total Time": 2.506,
"Actual Rows": 4398,
"Actual Loops": 1
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0,
"Total Cost": 20.01,
"Plan Rows": 1000,
"Plan Width": 8,
"Actual Startup Time": 0,
"Actual Total Time": 0.028,
"Actual Rows": 96,
"Actual Loops": 4398,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0,
"Total Cost": 5.01,
"Plan Rows": 1000,
"Plan Width": 8,
"Actual Startup Time": 0.014,
"Actual Total Time": 0.054,
"Actual Rows": 96,
"Actual Loops": 1
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE int_avg",
"Parallel Aware": false,
"Startup Cost": 577039.96,
"Total Cost": 667548.52,
"Plan Rows": 2585959,
"Plan Width": 112,
"Actual Startup Time": 11665.269,
"Actual Total Time": 14723.325,
"Actual Rows": 589800,
"Actual Loops": 1,
"Group Key": [
"volumes_15min_by_class.intersection_uid",
"class_types.class_type_id",
"volumes_15min_by_class.period_type",
"volumes_15min_by_class.dir",
"volumes_15min_by_class.leg",
"((volumes_15min_by_class.datetime_bin)::time without time zone)"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 577039.96,
"Total Cost": 583504.85,
"Plan Rows": 2585959,
"Plan Width": 66,
"Actual Startup Time": 11665.243,
"Actual Total Time": 12840.225,
"Actual Rows": 2585959,
"Actual Loops": 1,
"Sort Key": [
"volumes_15min_by_class.intersection_uid",
"class_types.class_type_id",
"volumes_15min_by_class.period_type",
"volumes_15min_by_class.dir",
"volumes_15min_by_class.leg",
"((volumes_15min_by_class.datetime_bin)::time without time zone)"
],
"Sort Method": "external merge",
"Sort Space Used": 146824,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 38.58,
"Total Cost": 89471,
"Plan Rows": 2585959,
"Plan Width": 66,
"Actual Startup Time": 0.084,
"Actual Total Time": 3036.375,
"Actual Rows": 2585959,
"Actual Loops": 1,
"Hash Cond": "(volumes_15min_by_class.class_type_id = class_types.class_type_id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "volumes_15min_by_class",
"Alias": "volumes_15min_by_class",
"Startup Cost": 0,
"Total Cost": 47410.59,
"Plan Rows": 2585959,
"Plan Width": 32,
"Actual Startup Time": 0.013,
"Actual Total Time": 925.844,
"Actual Rows": 2585959,
"Actual Loops": 1
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 22.7,
"Total Cost": 22.7,
"Plan Rows": 1270,
"Plan Width": 36,
"Actual Startup Time": 0.016,
"Actual Total Time": 0.016,
"Actual Rows": 3,
"Actual Loops": 1,
"Hash Buckets": 2048,
"Original Hash Buckets": 2048,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 17,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "class_types",
"Alias": "class_types",
"Startup Cost": 0,
"Total Cost": 22.7,
"Plan Rows": 1270,
"Plan Width": 36,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.008,
"Actual Rows": 3,
"Actual Loops": 1
}
]
}
]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE volumes_15",
"Parallel Aware": false,
"Startup Cost": 1818023.77,
"Total Cost": 1818043.23,
"Plan Rows": 7781,
"Plan Width": 172,
"Actual Startup Time": 50259.145,
"Actual Total Time": 52145.605,
"Actual Rows": 3224216,
"Actual Loops": 1,
"Sort Key": [
"a.intersection_uid",
"a.period_type",
"a.datetime_bin",
"b.class_type",
"b.dir",
"b.leg"
],
"Sort Method": "external merge",
"Sort Space Used": 167616,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 1695873.8,
"Total Cost": 1817520.9,
"Plan Rows": 7781,
"Plan Width": 172,
"Actual Startup Time": 20497.646,
"Actual Total Time": 40203.912,
"Actual Rows": 3224216,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Merge Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1695873.37,
"Total Cost": 1813415.46,
"Plan Rows": 7781,
"Plan Width": 176,
"Actual Startup Time": 20497.573,
"Actual Total Time": 24404.679,
"Actual Rows": 3224216,
"Actual Loops": 1,
"Merge Cond": "((a.intersection_uid = b.intersection_uid) AND (a.class_type_id = b.class_type_id) AND (a.period_type = b.period_type) AND (((a.datetime_bin)::time without time zone) = b.time_bin))",
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 926782.23,
"Total Cost": 938817.23,
"Plan Rows": 4814000,
"Plan Width": 48,
"Actual Startup Time": 2122.595,
"Actual Total Time": 2287.545,
"Actual Rows": 422201,
"Actual Loops": 1,
"Sort Key": [
"a.intersection_uid",
"a.class_type_id",
"a.period_type",
"((a.datetime_bin)::time without time zone)"
],
"Sort Method": "external sort",
"Sort Space Used": 20640,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "valid_bins",
"Alias": "a",
"Startup Cost": 0,
"Total Cost": 96280,
"Plan Rows": 4814000,
"Plan Width": 48,
"Actual Startup Time": 0.041,
"Actual Total Time": 778.676,
"Actual Rows": 422208,
"Actual Loops": 1
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 769091.14,
"Total Cost": 782020.93,
"Plan Rows": 2585959,
"Plan Width": 176,
"Actual Startup Time": 18374.969,
"Actual Total Time": 19638.129,
"Actual Rows": 3224216,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 769091.14,
"Total Cost": 775556.04,
"Plan Rows": 2585959,
"Plan Width": 176,
"Actual Startup Time": 18374.964,
"Actual Total Time": 18627.244,
"Actual Rows": 589800,
"Actual Loops": 1,
"Sort Key": [
"b.intersection_uid",
"b.class_type_id",
"b.period_type",
"b.time_bin"
],
"Sort Method": "external sort",
"Sort Space Used": 35880,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "int_avg",
"Alias": "b",
"Startup Cost": 0,
"Total Cost": 51719.18,
"Plan Rows": 2585959,
"Plan Width": 176,
"Actual Startup Time": 11665.274,
"Actual Total Time": 15414.759,
"Actual Rows": 589800,
"Actual Loops": 1
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "volumes_15min_by_class_datetime_bin_intersection_uid_class__idx",
"Relation Name": "volumes_15min_by_class",
"Alias": "c",
"Startup Cost": 0.43,
"Total Cost": 0.52,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.004,
"Actual Rows": 1,
"Actual Loops": 3224216,
"Index Cond": "((a.datetime_bin = datetime_bin) AND (a.intersection_uid = intersection_uid) AND (a.class_type_id = class_type_id) AND (b.dir = dir) AND (b.leg = leg) AND (a.period_type = period_type))",
"Rows Removed by Index Recheck": 0
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE intersection_days",
"Parallel Aware": false,
"Startup Cost": 677.95,
"Total Cost": 765.48,
"Plan Rows": 778,
"Plan Width": 16,
"Actual Startup Time": 58313.412,
"Actual Total Time": 60587.315,
"Actual Rows": 1466,
"Actual Loops": 1,
"Group Key": [
"volumes_15.intersection_uid",
"((volumes_15.datetime_bin)::date)"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 677.95,
"Total Cost": 697.4,
"Plan Rows": 7781,
"Plan Width": 16,
"Actual Startup Time": 58311.396,
"Actual Total Time": 59316.434,
"Actual Rows": 3224216,
"Actual Loops": 1,
"Sort Key": [
"volumes_15.intersection_uid",
"((volumes_15.datetime_bin)::date)"
],
"Sort Method": "external sort",
"Sort Space Used": 81952,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "volumes_15",
"Alias": "volumes_15",
"Startup Cost": 0,
"Total Cost": 175.07,
"Plan Rows": 7781,
"Plan Width": 16,
"Actual Startup Time": 50259.157,
"Actual Total Time": 54724.473,
"Actual Rows": 3224216,
"Actual Loops": 1
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE daily",
"Parallel Aware": false,
"Startup Cost": 376.02,
"Total Cost": 376.1,
"Plan Rows": 1,
"Plan Width": 332,
"Actual Startup Time": 104282.447,
"Actual Total Time": 111196.089,
"Actual Rows": 39498,
"Actual Loops": 1,
"Group Key": [
"a_2.intersection_uid",
"c_1.intersection_name",
"c_1.street_main",
"c_1.street_cross",
"a_2.period_type",
"a_2.class_type",
"a_2.dir",
"((a_2.datetime_bin)::date)",
"b_1.period_name",
"b_1.period_range",
"b_1.period_id"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 376.02,
"Total Cost": 376.02,
"Plan Rows": 1,
"Plan Width": 300,
"Actual Startup Time": 104281.558,
"Actual Total Time": 109340.91,
"Actual Rows": 2124224,
"Actual Loops": 1,
"Sort Key": [
"a_2.intersection_uid",
"c_1.intersection_name",
"c_1.street_main",
"c_1.street_cross",
"a_2.period_type",
"a_2.class_type",
"a_2.dir",
"((a_2.datetime_bin)::date)",
"b_1.period_name",
"b_1.period_range",
"b_1.period_id"
],
"Sort Method": "external merge",
"Sort Space Used": 234336,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 330.87,
"Total Cost": 376.01,
"Plan Rows": 1,
"Plan Width": 300,
"Actual Startup Time": 60659.586,
"Actual Total Time": 90256.361,
"Actual Rows": 2124224,
"Actual Loops": 1,
"Join Filter": "((a_2.intersection_uid = c_1.intersection_uid) AND ((a_2.class_type <> ALL ('{Vehicles,Cyclists}'::text[])) OR (((a_2.dir <> 'EB'::text) OR (c_1.street_main <> ALL ('{Wellington,Richmond}'::text[]))) AND ((a_2.dir <> 'WB'::text) OR (c_1.street_main <> 'Adelaide'::text)))) AND (((c_1.street_cross = 'Bathurst'::text) AND (a_2.leg = ANY ('{E,S,N}'::text[]))) OR ((c_1.street_cross = 'Jarvis'::text) AND (a_2.leg = ANY ('{W,S,N}'::text[]))) OR ((c_1.street_cross <> ALL ('{Bathurst,Jarvis}'::text[])) AND (((a_2.dir = 'EB'::text) AND (a_2.leg = ANY ('{W,N,S}'::text[]))) OR ((a_2.dir = 'WB'::text) AND (a_2.leg = ANY ('{E,N,S}'::text[])))))))",
"Rows Removed by Join Filter": 1062548,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 330.72,
"Total Cost": 375.7,
"Plan Rows": 1,
"Plan Width": 244,
"Actual Startup Time": 60659.547,
"Actual Total Time": 78432.053,
"Actual Rows": 4408132,
"Actual Loops": 1,
"Join Filter": "(((a_2.datetime_bin)::time without time zone <@ b_1.period_range) AND ((b_1.period_id <> 7) OR (d.num_daily_observations > 92)))",
"Rows Removed by Join Filter": 8343036,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 330.72,
"Total Cost": 353.1,
"Plan Rows": 1,
"Plan Width": 184,
"Actual Startup Time": 60659.511,
"Actual Total Time": 64049.505,
"Actual Rows": 1593896,
"Actual Loops": 1,
"Hash Cond": "((d.intersection_uid = a_2.intersection_uid) AND (d.dt = (a_2.datetime_bin)::date))",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "intersection_days",
"Alias": "d",
"Startup Cost": 0,
"Total Cost": 15.56,
"Plan Rows": 778,
"Plan Width": 16,
"Actual Startup Time": 58313.414,
"Actual Total Time": 60589.055,
"Actual Rows": 1466,
"Actual Loops": 1
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 330.69,
"Total Cost": 330.69,
"Plan Rows": 2,
"Plan Width": 172,
"Actual Startup Time": 2247.704,
"Actual Total Time": 2247.704,
"Actual Rows": 1593896,
"Actual Loops": 1,
"Hash Buckets": 65536,
"Original Hash Buckets": 1024,
"Hash Batches": 64,
"Original Hash Batches": 1,
"Peak Memory Usage": 3585,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "volumes_15",
"Alias": "a_2",
"Startup Cost": 0,
"Total Cost": 330.69,
"Plan Rows": 2,
"Plan Width": 172,
"Actual Startup Time": 0.028,
"Actual Total Time": 1316.475,
"Actual Rows": 1593896,
"Actual Loops": 1,
"Filter": "((dir = ANY ('{EB,WB}'::text[])) AND ((leg = ANY ('{E,S,N}'::text[])) OR (leg = ANY ('{W,S,N}'::text[])) OR ((dir = 'EB'::text) AND (leg = ANY ('{W,N,S}'::text[]))) OR ((dir = 'WB'::text) AND (leg = ANY ('{E,N,S}'::text[])))))",
"Rows Removed by Filter": 1630320
}
]
}
]
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "periods",
"Alias": "b_1",
"Startup Cost": 0,
"Total Cost": 16.3,
"Plan Rows": 315,
"Plan Width": 68,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.004,
"Actual Rows": 8,
"Actual Loops": 1593896,
"Filter": "report_flag",
"Rows Removed by Filter": 2
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "intersections_intersection_uid_idx",
"Relation Name": "intersections",
"Alias": "c_1",
"Startup Cost": 0.15,
"Total Cost": 0.25,
"Plan Rows": 1,
"Plan Width": 100,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 4408132,
"Index Cond": "(intersection_uid = d.intersection_uid)",
"Rows Removed by Index Recheck": 0,
"Filter": "(street_cross = ANY ('{Bathurst,Spadina,Bay,Jarvis}'::text[]))",
"Rows Removed by Filter": 0
}
]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 2189.14,
"Total Cost": 2189.15,
"Plan Rows": 2,
"Plan Width": 198,
"Actual Startup Time": 111723.626,
"Actual Total Time": 111739.057,
"Actual Rows": 39498,
"Actual Loops": 1,
"Sort Key": [
"daily.period_type",
"intersections.int_id",
"daily.class_type",
"centreline_intersection.intersec5",
"intersections.px",
"daily.dir",
"daily.period_name"
],
"Sort Method": "external merge",
"Sort Space Used": 4088,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 9.57,
"Total Cost": 2189.13,
"Plan Rows": 2,
"Plan Width": 198,
"Actual Startup Time": 111472.902,
"Actual Total Time": 111521.997,
"Actual Rows": 39498,
"Actual Loops": 1,
"Hash Cond": "(centreline_intersection.int_id = intersections.int_id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "centreline_intersection",
"Alias": "centreline_intersection",
"Startup Cost": 0,
"Total Cost": 1978.94,
"Plan Rows": 53494,
"Plan Width": 30,
"Actual Startup Time": 0.01,
"Actual Total Time": 21.861,
"Actual Rows": 53494,
"Actual Loops": 1
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 9.54,
"Total Cost": 9.54,
"Plan Rows": 2,
"Plan Width": 172,
"Actual Startup Time": 111446.126,
"Actual Total Time": 111446.126,
"Actual Rows": 39498,
"Actual Loops": 1,
"Hash Buckets": 65536,
"Original Hash Buckets": 1024,
"Hash Batches": 2,
"Original Hash Batches": 1,
"Peak Memory Usage": 3585,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 4.16,
"Total Cost": 9.54,
"Plan Rows": 2,
"Plan Width": 172,
"Actual Startup Time": 104282.48,
"Actual Total Time": 111415.645,
"Actual Rows": 39498,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "daily",
"Alias": "daily",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 164,
"Actual Startup Time": 104282.453,
"Actual Total Time": 111264.266,
"Actual Rows": 39498,
"Actual Loops": 1
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "intersections",
"Alias": "intersections",
"Startup Cost": 4.16,
"Total Cost": 9.5,
"Plan Rows": 2,
"Plan Width": 16,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 39498,
"Recheck Cond": "(intersection_uid = daily.intersection_uid)",
"Rows Removed by Index Recheck": 0,
"Exact Heap Blocks": 39498,
"Lossy Heap Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "intersections_intersection_uid_idx",
"Startup Cost": 0,
"Total Cost": 4.16,
"Plan Rows": 2,
"Plan Width": 0,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 39498,
"Index Cond": "(intersection_uid = daily.intersection_uid)"
}
]
}
]
}
]
}
]
}
]
}
]
},
"Planning Time": 2.475,
"Triggers": [],
"Execution Time": 111923.894
}
]We added a few extra joins and an extra filter in the daily CTE.
WITH valid_bins AS (
SELECT a_1.intersection_uid,
a_1.class_type_id,
a_1.dt + b_1.b AS datetime_bin,
a_1.period_type
FROM volumes.report_dates_view a_1
CROSS JOIN ( SELECT generate_series('2017-01-01 00:00:00'::timestamp without time zone, '2017-01-01 23:45:00'::timestamp without time zone, '00:15:00'::interval)::time without time zone AS generate_series) b_1(b)
LEFT JOIN volumes.exceptions c ON a_1.intersection_uid = c.intersection_uid AND a_1.class_type_id = c.class_type_id AND (a_1.dt + b_1.b) <@ c.excluded_datetime
WHERE c.exceptions_uid IS NULL
), int_avg AS (
SELECT volumes_15min_by_class.intersection_uid,
volumes_15min_by_class.class_type_id,
volumes_15min_by_class.dir,
volumes_15min_by_class.leg,
volumes_15min_by_class.period_type,
volumes_15min_by_class.datetime_bin::time without time zone AS time_bin,
avg(volumes_15min_by_class.total_volume) AS avg_volume
FROM volumes.volumes_15min_by_class
GROUP BY volumes_15min_by_class.intersection_uid, volumes_15min_by_class.class_type_id, volumes_15min_by_class.period_type, volumes_15min_by_class.dir, volumes_15min_by_class.leg, (volumes_15min_by_class.datetime_bin::time without time zone)
), volumes_15 AS (
SELECT a.intersection_uid,
a.period_type,
a.datetime_bin,
a.class_type_id,
b.dir,
b.leg,
COALESCE(c.total_volume, b.avg_volume) AS volume
FROM valid_bins a
JOIN int_avg b USING (intersection_uid, class_type_id, period_type)
LEFT JOIN volumes.volumes_15min_by_class c USING (datetime_bin, intersection_uid, class_type_id, dir, leg, period_type)
WHERE b.time_bin = a.datetime_bin::time without time zone
), intersection_days AS (
SELECT volumes_15.intersection_uid,
volumes_15.datetime_bin::date AS dt,
count(DISTINCT volumes_15.datetime_bin) AS num_daily_observations
FROM volumes_15
GROUP BY volumes_15.intersection_uid, (volumes_15.datetime_bin::date)
), daily AS (
SELECT a.intersection_uid,
c.int_id,
c.px,
c.intersection_name,
c.street_main,
c.street_cross,
class_types.class_type,
a.dir,
a.period_type,
a.datetime_bin::date AS dt2,
b.period_name ||
CASE
WHEN b.period_id = 7 THEN ''::text
ELSE (((' ('::text || to_char(lower(b.period_range)::interval, 'HH24:MM'::text)) || '-'::text) || to_char(upper(b.period_range)::interval, 'HH24:MM'::text)) || ')'::text
END AS period_name,
sum(a.volume) AS total_volume
FROM volumes_15 a
CROSS JOIN volumes.periods b
JOIN volumes.intersections c USING (intersection_uid)
JOIN intersection_days d ON d.intersection_uid = a.intersection_uid AND a.datetime_bin >= d.dt AND a.datetime_bin < (d.dt + '1 day'::interval)
JOIN volumes.class_types USING (class_type_id)
WHERE a.datetime_bin::time without time zone <@ b.period_range AND b.report_flag AND (a.dir = ANY (ARRAY['EB'::text, 'WB'::text])) AND ((b.period_id <> ALL (ARRAY[4, 8])) OR b.period_id = 8 AND d.num_daily_observations > (23 * 4) OR b.period_id = 4 AND d.num_daily_observations > (13 * 4)) AND (c.street_cross = ANY (ARRAY['Bathurst'::text, 'Spadina'::text, 'Bay'::text, 'Jarvis'::text])) AND (c.street_cross = 'Bathurst'::text AND (a.leg = ANY (ARRAY['E'::text, 'S'::text, 'N'::text])) OR c.street_cross = 'Jarvis'::text AND (a.leg = ANY (ARRAY['W'::text, 'S'::text, 'N'::text])) OR (c.street_cross <> ALL (ARRAY['Bathurst'::text, 'Jarvis'::text])) AND (a.dir = 'EB'::text AND (a.leg = ANY (ARRAY['W'::text, 'N'::text, 'S'::text])) OR a.dir = 'WB'::text AND (a.leg = ANY (ARRAY['E'::text, 'N'::text, 'S'::text])))) AND NOT ((class_types.class_type = ANY (ARRAY['Vehicles'::text, 'Cyclists'::text])) AND (a.dir = 'EB'::text AND (c.street_main = ANY (ARRAY['Wellington'::text, 'Richmond'::text])) OR a.dir = 'WB'::text AND c.street_main = 'Adelaide'::text))
GROUP BY a.intersection_uid, c.int_id, c.px, c.intersection_name, c.street_main, c.street_cross, a.period_type, class_types.class_type, a.dir, (a.datetime_bin::date), b.period_name, b.period_range, b.period_id
)
SELECT daily.period_type AS aggregation_period,
daily.int_id,
centreline_intersection.intersec5 AS intersection_name,
daily.px,
CASE
WHEN daily.class_type = 'Buses'::text THEN 'Buses and streetcars'::text
ELSE daily.class_type
END AS classification,
daily.dir,
daily.period_name,
avg(daily.total_volume)::integer AS volume
FROM daily
JOIN gis.centreline_intersection USING (int_id)
GROUP BY daily.period_type, daily.int_id, daily.class_type, centreline_intersection.intersec5, daily.px, daily.dir, daily.period_name;[
{
"Plan": {
"Node Type": "Subquery Scan",
"Parallel Aware": false,
"Alias": "ksp_miovision_summary",
"Startup Cost": 818773.88,
"Total Cost": 818773.93,
"Plan Rows": 1,
"Plan Width": 170,
"Actual Startup Time": 20032901.73,
"Actual Total Time": 20032939.83,
"Actual Rows": 6956,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 818773.88,
"Total Cost": 818773.92,
"Plan Rows": 1,
"Plan Width": 228,
"Actual Startup Time": 20032901.729,
"Actual Total Time": 20032936.985,
"Actual Rows": 6956,
"Actual Loops": 1,
"Group Key": [
"daily.period_type",
"daily.int_id",
"daily.class_type",
"centreline_intersection.intersec5",
"daily.px",
"daily.dir",
"daily.period_name"
],
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE valid_bins",
"Parallel Aware": false,
"Join Type": "Right",
"Startup Cost": 150377.78,
"Total Cost": 302344.27,
"Plan Rows": 1,
"Plan Width": 25,
"Actual Startup Time": 434.037,
"Actual Total Time": 727.44,
"Actual Rows": 402132,
"Actual Loops": 1,
"Hash Cond": "((c.intersection_uid = a_1.intersection_uid) AND (c.class_type_id = a_1.class_type_id))",
"Join Filter": "((a_1.dt + ((generate_series('2017-01-01 00:00:00'::timestamp without time zone, '2017-01-01 23:45:00'::timestamp without time zone, '00:15:00'::interval))::time without time zone)) <@ c.excluded_datetime)",
"Rows Removed by Join Filter": 468936,
"Filter": "(c.exceptions_uid IS NULL)",
"Rows Removed by Filter": 27000,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "exceptions",
"Alias": "c",
"Startup Cost": 0,
"Total Cost": 2.99,
"Plan Rows": 99,
"Plan Width": 34,
"Actual Startup Time": 0.015,
"Actual Total Time": 0.063,
"Actual Rows": 99,
"Actual Loops": 1
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 54801.78,
"Total Cost": 54801.78,
"Plan Rows": 4377000,
"Plan Width": 29,
"Actual Startup Time": 429.327,
"Actual Total Time": 429.327,
"Actual Rows": 420192,
"Actual Loops": 1,
"Hash Buckets": 65536,
"Original Hash Buckets": 65536,
"Hash Batches": 128,
"Original Hash Batches": 128,
"Peak Memory Usage": 852,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0,
"Total Cost": 54801.78,
"Plan Rows": 4377000,
"Plan Width": 29,
"Actual Startup Time": 0.038,
"Actual Total Time": 257.539,
"Actual Rows": 420192,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "report_dates_view",
"Alias": "a_1",
"Startup Cost": 0,
"Total Cost": 71.77,
"Plan Rows": 4377,
"Plan Width": 21,
"Actual Startup Time": 0.018,
"Actual Total Time": 1.652,
"Actual Rows": 4377,
"Actual Loops": 1
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0,
"Total Cost": 20.01,
"Plan Rows": 1000,
"Plan Width": 8,
"Actual Startup Time": 0,
"Actual Total Time": 0.019,
"Actual Rows": 96,
"Actual Loops": 4377,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0,
"Total Cost": 5.01,
"Plan Rows": 1000,
"Plan Width": 8,
"Actual Startup Time": 0.015,
"Actual Total Time": 0.041,
"Actual Rows": 96,
"Actual Loops": 1
}
]
}
]
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE int_avg",
"Parallel Aware": false,
"Startup Cost": 449693.34,
"Total Cost": 504895.18,
"Plan Rows": 256753,
"Plan Width": 60,
"Actual Startup Time": 13479.235,
"Actual Total Time": 16124.335,
"Actual Rows": 586728,
"Actual Loops": 1,
"Group Key": [
"volumes_15min_by_class.intersection_uid",
"volumes_15min_by_class.class_type_id",
"volumes_15min_by_class.period_type",
"volumes_15min_by_class.dir",
"volumes_15min_by_class.leg",
"((volumes_15min_by_class.datetime_bin)::time without time zone)"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 449693.34,
"Total Cost": 456112.16,
"Plan Rows": 2567527,
"Plan Width": 32,
"Actual Startup Time": 13479.212,
"Actual Total Time": 14497.599,
"Actual Rows": 2567527,
"Actual Loops": 1,
"Sort Key": [
"volumes_15min_by_class.intersection_uid",
"volumes_15min_by_class.class_type_id",
"volumes_15min_by_class.period_type",
"volumes_15min_by_class.dir",
"volumes_15min_by_class.leg",
"((volumes_15min_by_class.datetime_bin)::time without time zone)"
],
"Sort Method": "external merge",
"Sort Space Used": 116264,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "volumes_15min_by_class",
"Alias": "volumes_15min_by_class",
"Startup Cost": 0,
"Total Cost": 53491.09,
"Plan Rows": 2567527,
"Plan Width": 32,
"Actual Startup Time": 0.011,
"Actual Total Time": 1122.932,
"Actual Rows": 2567527,
"Actual Loops": 1
}
]
}
]
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE volumes_15",
"Parallel Aware": false,
"Join Type": "Left",
"Startup Cost": 0.47,
"Total Cost": 9315.82,
"Plan Rows": 1,
"Plan Width": 144,
"Actual Startup Time": 14681.194,
"Actual Total Time": 35488.395,
"Actual Rows": 2988824,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.04,
"Total Cost": 9307.35,
"Plan Rows": 1,
"Plan Width": 144,
"Actual Startup Time": 14681.133,
"Actual Total Time": 19951.372,
"Actual Rows": 2988824,
"Actual Loops": 1,
"Hash Cond": "((b.intersection_uid = a.intersection_uid) AND (b.class_type_id = a.class_type_id) AND (b.period_type = a.period_type) AND (b.time_bin = (a.datetime_bin)::time without time zone))",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "int_avg",
"Alias": "b",
"Startup Cost": 0,
"Total Cost": 5135.06,
"Plan Rows": 256753,
"Plan Width": 142,
"Actual Startup Time": 13479.238,
"Actual Total Time": 16598.955,
"Actual Rows": 586728,
"Actual Loops": 1
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.02,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 48,
"Actual Startup Time": 1201.785,
"Actual Total Time": 1201.785,
"Actual Rows": 402132,
"Actual Loops": 1,
"Hash Buckets": 65536,
"Original Hash Buckets": 1024,
"Hash Batches": 8,
"Original Hash Batches": 1,
"Peak Memory Usage": 3585,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "valid_bins",
"Alias": "a",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 48,
"Actual Startup Time": 434.042,
"Actual Total Time": 1006.986,
"Actual Rows": 402132,
"Actual Loops": 1
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "volumes_15min_by_class_datetime_bin_intersection_uid_class__idx",
"Relation Name": "volumes_15min_by_class",
"Alias": "c_1",
"Startup Cost": 0.43,
"Total Cost": 8.46,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.004,
"Actual Rows": 1,
"Actual Loops": 2988824,
"Index Cond": "((a.datetime_bin = datetime_bin) AND (a.intersection_uid = intersection_uid) AND (a.class_type_id = class_type_id) AND (b.dir = dir) AND (b.leg = leg) AND (a.period_type = period_type))",
"Rows Removed by Index Recheck": 0
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE intersection_days",
"Parallel Aware": false,
"Startup Cost": 0.03,
"Total Cost": 0.06,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 26568.811,
"Actual Total Time": 28778.646,
"Actual Rows": 1458,
"Actual Loops": 1,
"Group Key": [
"volumes_15.intersection_uid",
"((volumes_15.datetime_bin)::date)"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 0.03,
"Total Cost": 0.04,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 26568.286,
"Actual Total Time": 27721.586,
"Actual Rows": 2988824,
"Actual Loops": 1,
"Sort Key": [
"volumes_15.intersection_uid",
"((volumes_15.datetime_bin)::date)"
],
"Sort Method": "external merge",
"Sort Space Used": 76000,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "volumes_15",
"Alias": "volumes_15",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.004,
"Actual Total Time": 23874.424,
"Actual Rows": 2988824,
"Actual Loops": 1
}
]
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE daily",
"Parallel Aware": false,
"Startup Cost": 38.88,
"Total Cost": 38.96,
"Plan Rows": 1,
"Plan Width": 344,
"Actual Startup Time": 20026460.124,
"Actual Total Time": 20032588.365,
"Actual Rows": 34866,
"Actual Loops": 1,
"Group Key": [
"a_2.intersection_uid",
"c_2.int_id",
"c_2.px",
"c_2.intersection_name",
"c_2.street_main",
"c_2.street_cross",
"a_2.period_type",
"class_types.class_type",
"a_2.dir",
"((a_2.datetime_bin)::date)",
"b_1.period_name",
"b_1.period_range",
"b_1.period_id"
],
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 38.88,
"Total Cost": 38.88,
"Plan Rows": 1,
"Plan Width": 312,
"Actual Startup Time": 20026459.494,
"Actual Total Time": 20031359.598,
"Actual Rows": 1457328,
"Actual Loops": 1,
"Sort Key": [
"a_2.intersection_uid",
"c_2.int_id",
"c_2.px",
"c_2.intersection_name",
"c_2.street_main",
"c_2.street_cross",
"a_2.period_type",
"class_types.class_type",
"a_2.dir",
"((a_2.datetime_bin)::date)",
"b_1.period_name",
"b_1.period_range",
"b_1.period_id"
],
"Sort Method": "external merge",
"Sort Space Used": 183960,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 4.32,
"Total Cost": 38.87,
"Plan Rows": 1,
"Plan Width": 312,
"Actual Startup Time": 43511.139,
"Actual Total Time": 20009091.008,
"Actual Rows": 1457328,
"Actual Loops": 1,
"Join Filter": "((class_types.class_type <> ALL ('{Vehicles,Cyclists}'::text[])) OR (((a_2.dir <> 'EB'::text) OR (c_2.street_main <> ALL ('{Wellington,Richmond}'::text[]))) AND ((a_2.dir <> 'WB'::text) OR (c_2.street_main <> 'Adelaide'::text))))",
"Rows Removed by Join Filter": 185900,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 4.16,
"Total Cost": 30.67,
"Plan Rows": 1,
"Plan Width": 288,
"Actual Startup Time": 43511.114,
"Actual Total Time": 20000007.775,
"Actual Rows": 1643228,
"Actual Loops": 1,
"Join Filter": "((a_2.datetime_bin >= d.dt) AND (a_2.intersection_uid = c_2.intersection_uid) AND (a_2.datetime_bin < (d.dt + '1 day'::interval)) AND ((b_1.period_id <> ALL ('{4,8}'::integer[])) OR ((b_1.period_id = 8) AND (d.num_daily_observations > 92)) OR ((b_1.period_id = 4) AND (d.num_daily_observations > 52))) AND (((c_2.street_cross = 'Bathurst'::text) AND (a_2.leg = ANY ('{E,S,N}'::text[]))) OR ((c_2.street_cross = 'Jarvis'::text) AND (a_2.leg = ANY ('{W,S,N}'::text[]))) OR ((c_2.street_cross <> ALL ('{Bathurst,Jarvis}'::text[])) AND (((a_2.dir = 'EB'::text) AND (a_2.leg = ANY ('{W,N,S}'::text[]))) OR ((a_2.dir = 'WB'::text) AND (a_2.leg = ANY ('{E,N,S}'::text[])))))))",
"Rows Removed by Join Filter": 4796975944,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0,
"Total Cost": 21.07,
"Plan Rows": 1,
"Plan Width": 212,
"Actual Startup Time": 14681.234,
"Actual Total Time": 38090.606,
"Actual Rows": 4334796,
"Actual Loops": 1,
"Join Filter": "((a_2.datetime_bin)::time without time zone <@ b_1.period_range)",
"Rows Removed by Join Filter": 7474804,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "volumes_15",
"Alias": "a_2",
"Startup Cost": 0,
"Total Cost": 0.04,
"Plan Rows": 1,
"Plan Width": 144,
"Actual Startup Time": 14681.202,
"Actual Total Time": 18416.123,
"Actual Rows": 1476200,
"Actual Loops": 1,
"Filter": "((dir = ANY ('{EB,WB}'::text[])) AND ((leg = ANY ('{E,S,N}'::text[])) OR (leg = ANY ('{W,S,N}'::text[])) OR ((dir = 'EB'::text) AND (leg = ANY ('{W,N,S}'::text[]))) OR ((dir = 'WB'::text) AND (leg = ANY ('{E,N,S}'::text[])))))",
"Rows Removed by Filter": 1512624
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "periods",
"Alias": "b_1",
"Startup Cost": 0,
"Total Cost": 16.3,
"Plan Rows": 315,
"Plan Width": 68,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.006,
"Actual Rows": 8,
"Actual Loops": 1476200,
"Filter": "report_flag",
"Rows Removed by Filter": 2
}
]
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 4.16,
"Total Cost": 9.54,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 0.009,
"Actual Total Time": 4.303,
"Actual Rows": 1107,
"Actual Loops": 4334796,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "intersection_days",
"Alias": "d",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.368,
"Actual Rows": 1458,
"Actual Loops": 4334796
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "intersections",
"Alias": "c_2",
"Startup Cost": 4.16,
"Total Cost": 9.51,
"Plan Rows": 1,
"Plan Width": 112,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 6320132568,
"Recheck Cond": "(intersection_uid = d.intersection_uid)",
"Rows Removed by Index Recheck": 0,
"Filter": "(street_cross = ANY ('{Bathurst,Spadina,Bay,Jarvis}'::text[]))",
"Rows Removed by Filter": 0,
"Exact Heap Blocks": 6320132568,
"Lossy Heap Blocks": 0,
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "intersections_intersection_uid_idx",
"Startup Cost": 0,
"Total Cost": 4.16,
"Plan Rows": 2,
"Plan Width": 0,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 6320132568,
"Index Cond": "(intersection_uid = d.intersection_uid)"
}
]
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "class_types_pkey",
"Relation Name": "class_types",
"Alias": "class_types",
"Startup Cost": 0.15,
"Total Cost": 8.17,
"Plan Rows": 1,
"Plan Width": 36,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 1643228,
"Index Cond": "(class_type_id = a_2.class_type_id)",
"Rows Removed by Index Recheck": 0
}
]
}
]
}
]
},
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 2179.6,
"Total Cost": 2179.6,
"Plan Rows": 1,
"Plan Width": 198,
"Actual Startup Time": 20032901.691,
"Actual Total Time": 20032912.782,
"Actual Rows": 34866,
"Actual Loops": 1,
"Sort Key": [
"daily.period_type",
"daily.int_id",
"daily.class_type",
"centreline_intersection.intersec5",
"daily.px",
"daily.dir",
"daily.period_name"
],
"Sort Method": "external merge",
"Sort Space Used": 3544,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.03,
"Total Cost": 2179.59,
"Plan Rows": 1,
"Plan Width": 198,
"Actual Startup Time": 20032688.124,
"Actual Total Time": 20032717.775,
"Actual Rows": 34866,
"Actual Loops": 1,
"Hash Cond": "(centreline_intersection.int_id = daily.int_id)",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "centreline_intersection",
"Alias": "centreline_intersection",
"Startup Cost": 0,
"Total Cost": 1978.94,
"Plan Rows": 53494,
"Plan Width": 30,
"Actual Startup Time": 0.006,
"Actual Total Time": 18.954,
"Actual Rows": 53494,
"Actual Loops": 1
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.02,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 172,
"Actual Startup Time": 20032669.655,
"Actual Total Time": 20032669.655,
"Actual Rows": 34866,
"Actual Loops": 1,
"Hash Buckets": 65536,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 3935,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "daily",
"Alias": "daily",
"Startup Cost": 0,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 172,
"Actual Startup Time": 20026460.13,
"Actual Total Time": 20032648.092,
"Actual Rows": 34866,
"Actual Loops": 1
}
]
}
]
}
]
}
]
}
]
},
"Planning Time": 1.539,
"Triggers": [],
"Execution Time": 20033041.691
}
]