|
495 min
47%
|
29,718,334 ms
|
1
sunrise_user
|
WITH trh_map AS (
SELECT
trh.register_address,
COALESCE(NULLIF(trh.placement, $1), $2) AS placement
FROM trh_sensor trh
JOIN assets a ON a.id = trh.asset_id
-- WHERE a.host_name = 'fkaBRN003'
)
SELECT
(d.unix_time * $3) AS time_ms,
m.placement,
CASE
WHEN (d.data -> d.type ->> $4) ~ $5
THEN ROUND(((d.data -> d.type ->> $6)::double precision / $7)::numeric, $8)
ELSE $9
END AS temperature_c,
CASE
WHEN (d.data -> d.type ->> $10) ~ $11
THEN ROUND(((d.data -> d.type ->> $12)::double precision / $13)::numeric, $14)
ELSE $15
END AS humidity_pct
FROM trh_map m
JOIN LATERAL (
SELECT d.unix_time, d.type, d.data
FROM data_normalized d
JOIN corelink_node cn ON d.ip::inet = cn.ip
JOIN sites s ON s.id = cn.site_id
WHERE s.name = $16
AND d.acq_type = $17
AND d.type = m.register_address
ORDER BY d.unix_time DESC
LIMIT $18
) d ON $19
ORDER BY m.placement
|
|
129 min
12%
|
7,751,826 ms
|
1
as_ro_user
|
WITH trh_map AS (
SELECT
trh.register_address,
COALESCE(NULLIF(trh.placement, $1), $2) AS placement,
a.host_name
FROM public.trh_sensor trh
JOIN public.assets a ON a.id = trh.asset_id
WHERE a.site_id = $3
)
-- 1. UNPIVOT TEMPERATURE
SELECT
m.host_name,
date_trunc($4, to_timestamp(d.unix_time)) AS sync_time,
m.placement || $5 AS metric,
ROUND(((d.data -> d.type ->> $6)::double precision / $7)::numeric, $8) AS value,
$9 AS unit
FROM trh_map m
CROSS JOIN LATERAL (
SELECT dn.unix_time, dn.type, dn.data
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip = cn.ip::text -- optimized cast
WHERE cn.site_id = $10
AND dn.acq_type = $11
AND dn.type = m.register_address
ORDER BY dn.unix_time DESC
LIMIT $12
) d
WHERE (d.data -> d.type ->> $13) ~ $14
UNION ALL
-- 2. UNPIVOT HUMIDITY
SELECT
m.host_name,
date_trunc($15, to_timestamp(d.unix_time)) AS sync_time,
m.placement || $16 AS metric,
ROUND(((d.data -> d.type ->> $17)::double precision / $18)::numeric, $19) AS value,
$20 AS unit
FROM trh_map m
CROSS JOIN LATERAL (
SELECT dn.unix_time, dn.type, dn.data
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip = cn.ip::text
WHERE cn.site_id = $21
AND dn.acq_type = $22
AND dn.type = m.register_address
ORDER BY dn.unix_time DESC
LIMIT $23
) d
WHERE (d.data -> d.type ->> $24) ~ $25
ORDER BY host_name ASC, metric ASC
|
|
74 min
7%
|
1,941 ms
|
2,274
sunrise_user
|
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/
|
|
28 min
3%
|
7 ms
|
238,455
sunrise_user
|
SELECT
d.unix_time AS time,
d.type,
d.data
FROM data_normalized d
WHERE d.unix_time BETWEEN $1 AND $2
AND d.acq_type = $4
AND d.dsid = ANY($3)
ORDER BY d.unix_time ASC
|
|
25 min
2%
|
24,662 ms
|
61
sunrise_user
|
SELECT
time_bucket('1 hour', d.time) AS time,
AVG(
CASE
WHEN v.val ~ $1
THEN
v.val::float *
CASE
WHEN a.host_name = $2
AND v.key IN ($3 /*, ... */)
THEN $4
ELSE COALESCE(pndp.normalization_function, $5)
END
ELSE $6
END
) AS value,
a.host_name || $7 || v.key AS title_1
FROM data_json_ts d
JOIN assets a
ON a.id = d.asset_id
CROSS JOIN LATERAL (
SELECT
key,
jsonb_extract_path_text(d.data, key) AS val
FROM UNNEST(ARRAY[$8 /*, ... */]) AS key
WHERE d.data ? key
) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = d.part_number_id
AND pndp.data_type IN ($9 /*, ... */)
WHERE d.ip IS NOT NULL
AND d.ip IN ($10 /*, ... */)
AND d.time BETWEEN $11 AND $12
GROUP BY
time_bucket('1 hour', d.time),
a.host_name,
v.key
ORDER BY time, title_1
|
|
23 min
2%
|
6 ms
|
238,453
sunrise_user
|
SELECT
d.unix_time AS time,
d.type,
d.data,
trh.placement,
a.host_name
FROM data_normalized d
JOIN corelink_node cn ON d.ip::inet = cn.ip
JOIN sites s ON s.id = cn.site_id
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE s.id = $1
AND a.host_name = $2
AND d.acq_type = $5
AND d.unix_time BETWEEN $3 AND $4
ORDER BY d.unix_time ASC
|
|
18 min
2%
|
0 ms
|
10,569,127
sunrise_user
|
INSERT INTO data_snmp_202603 ( rtu_id, time, ip, port, status, firmware, processed, arrivaltime, acq_id, type, lock, unix_time, dsid, site_name, data) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7,
$8,
$9,
$10,
$11,
$12,
$13,
$14,
$15
)
|
|
17 min
2%
|
0 ms
|
9,842,293
sunrise_user
|
INSERT INTO data_normalized ( rtu_id, time, ip, port, status, firmware, processed, arrivaltime, acq_id, type, lock, unix_time, dsid, site_name, data, acq_type, data_pd_0, data_pd_1, data_pd_2, data_pd_3) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7,
$8,
$9,
$10,
$11,
$12,
$13,
$14,
$15,
$16,
$17,
$18,
$19,
$20
)
|
|
17 min
2%
|
9,230 ms
|
109
sunrise_user
|
SELECT
date_trunc($1, to_timestamp(d.unix_time)) AS time,
ct.host_name,
ct.monitored_point,
ct.ct_value
FROM data_snmp d
CROSS JOIN LATERAL (
SELECT trim(both $2 from (d.data->d.type->$3->$4)::text) AS dsid
) dn
JOIN tenant t ON t.dsid = dn.dsid
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $5
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $6
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $7
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $8
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $9
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $10
LEFT JOIN assets a1 ON a1.id = ac1.asset_id
LEFT JOIN assets a2 ON a2.id = ac2.asset_id
LEFT JOIN assets a3 ON a3.id = ac3.asset_id
LEFT JOIN assets a4 ON a4.id = ac4.asset_id
LEFT JOIN assets a5 ON a5.id = ac5.asset_id
LEFT JOIN assets a6 ON a6.id = ac6.asset_id
CROSS JOIN LATERAL (
VALUES
($11, CASE WHEN ac1.ct_manufacturer ILIKE $12 THEN ((COALESCE((d.data->d.type->$13->$14)::text::float,$15)+$16)/$17) * COALESCE(ac1.ct_factor,$18)
WHEN ac1.ct_manufacturer ILIKE $19 THEN ((COALESCE((d.data->d.type->$20->$21)::text::float,$22)+$23)*$24) * COALESCE(ac1.ct_rating,$25) * COALESCE(ac1.ct_factor,$26)
ELSE COALESCE((d.data->d.type->$27->$28)::text::float,$29) END, ac1.monitored_point, a1.host_name),
($30, CASE WHEN ac2.ct_manufacturer ILIKE $31 THEN ((COALESCE((d.data->d.type->$32->$33)::text::float,$34)+$35)/$36) * COALESCE(ac2.ct_factor,$37)
WHEN ac2.ct_manufacturer ILIKE $38 THEN ((COALESCE((d.data->d.type->$39->$40)::text::float,$41)+$42)*$43) * COALESCE(ac2.ct_rating,$44) * COALESCE(ac2.ct_factor,$45)
ELSE COALESCE((d.data->d.type->$46->$47)::text::float,$48) END, ac2.monitored_point, a2.host_name),
($49, CASE WHEN ac3.ct_manufacturer ILIKE $50 THEN ((COALESCE((d.data->d.type->$51->$52)::text::float,$53)+$54)/$55) * COALESCE(ac3.ct_factor,$56)
WHEN ac3.ct_manufacturer ILIKE $57 THEN ((COALESCE((d.data->d.type->$58->$59)::text::float,$60)+$61)*$62) * COALESCE(ac3.ct_rating,$63) * COALESCE(ac3.ct_factor,$64)
ELSE COALESCE((d.data->d.type->$65->$66)::text::float,$67) END, ac3.monitored_point, a3.host_name),
($68, CASE WHEN ac4.ct_manufacturer ILIKE $69 THEN ((COALESCE((d.data->d.type->$70->$71)::text::float,$72)+$73)/$74) * COALESCE(ac4.ct_factor,$75)
WHEN ac4.ct_manufacturer ILIKE $76 THEN ((COALESCE((d.data->d.type->$77->$78)::text::float,$79)+$80)*$81) * COALESCE(ac4.ct_rating,$82) * COALESCE(ac4.ct_factor,$83)
ELSE COALESCE((d.data->d.type->$84->$85)::text::float,$86) END, ac4.monitored_point, a4.host_name),
($87, CASE WHEN ac5.ct_manufacturer ILIKE $88 THEN ((COALESCE((d.data->d.type->$89->$90)::text::float,$91)+$92)/$93) * COALESCE(ac5.ct_factor,$94)
WHEN ac5.ct_manufacturer ILIKE $95 THEN ((COALESCE((d.data->d.type->$96->$97)::text::float,$98)+$99)*$100) * COALESCE(ac5.ct_rating,$101) * COALESCE(ac5.ct_factor,$102)
ELSE COALESCE((d.data->d.type->$103->$104)::text::float,$105) END, ac5.monitored_point, a5.host_name),
($106, CASE WHEN ac6.ct_manufacturer ILIKE $107 THEN ((COALESCE((d.data->d.type->$108->$109)::text::float,$110)+$111)/$112) * COALESCE(ac6.ct_factor,$113)
WHEN ac6.ct_manufacturer ILIKE $114 THEN ((COALESCE((d.data->d.type->$115->$116)::text::float,$117)+$118)*$119) * COALESCE(ac6.ct_rating,$120) * COALESCE(ac6.ct_factor,$121)
ELSE COALESCE((d.data->d.type->$122->$123)::text::float,$124) END, ac6.monitored_point, a6.host_name)
) AS ct(port, ct_value, monitored_point, host_name)
WHERE ct.host_name = $125
AND d.time >= $126 AND d.time < $127
|
|
15 min
1%
|
6,598 ms
|
133
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
sites.cluster_id,
sites.class,
assets.host_name,
sites.name AS site_name,
--assets.floor,
assets.room,
--assets.zone,
sites.address AS site_address,
cat_class.title AS category,
subcat_class.title AS subcategory,
part_numbers.name AS part_number_name,
data_json.ip,
data_json.time,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
--data_json.data,
--data_json.type,
-- 🧠 Conditional Link Field Based on subcategory_title
CASE
-- WHEN subcat_class.title = 'UPS' THEN
-- 'https://stagging-sunrise-bi.axamesh.net/d/45852ca0-6cc7-447a-b908-5fcb6a4cdafb/ups?orgId=1&timezone=Europe%2FZurich&var-ip_filter=' || data_json.ip
-- WHEN subcat_class.title = 'DC-Environment' THEN
-- 'https://stagging-sunrise-bi.axamesh.net/d/162aeeab-a31d-4734-83e9-ab6e5d9e4c96/rectifier-normalized?orgId=1&timezone=Europe%2FZurich&var-ip_filter=' || data_json.ip
-- WHEN subcat_class.title = 'Aircon' THEN
-- 'https://stagging-sunrise-bi.axamesh.net/d/692587ee-3f7d-451a-9b91-40a79b98851a/aircon-dynamic?orgId=1&var-ip_filter=' || data_json.ip
-- WHEN subcat_class.title = 'Main Smartmeter' THEN
-- 'https://stagging-sunrise-bi.axamesh.net/d/665c13f5-f0e4-4bf1-8252-67dde0a1c0f0/smartmeter?orgId=1&var-ip_filter=' || data_json.ip
-- ELSE NULL
WHEN subcat_class.title = $4 THEN
$5 || data_json.ip
WHEN subcat_class.title = $6 THEN
$7 || data_json.ip
WHEN subcat_class.title = $8 THEN
$9 || data_json.ip
WHEN subcat_class.title = $10 THEN
$11 || data_json.ip
ELSE $12
END AS link_url
FROM data_json data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
ORDER BY data_json.ip, data_json.time DESC
|
|
13 min
1%
|
3,717 ms
|
212
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
keys.key,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $5
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $6)
ELSE $7
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type IN ($8 /*, ... */)
WHERE jsonb_typeof(lp.data) = $9
AND keys.key IN ($10 /*, ... */)
),
imbalance_calculation AS (
SELECT
ip,
time,
status,
host_name,
MAX(CASE WHEN key = $11 THEN normalized_value END) AS ia,
MAX(CASE WHEN key = $12 THEN normalized_value END) AS ib,
MAX(CASE WHEN key = $13 THEN normalized_value END) AS ic
FROM normalized_values
GROUP BY ip, time, status, host_name
)
SELECT
ip,
time,
host_name,
status,
CASE
WHEN (ia + ib + ic) / $14 > $15
THEN ((GREATEST(ia, ib, ic) - LEAST(ia, ib, ic)) / ((ia + ib + ic) / $16)) * $17
ELSE $18
END AS current_imbalance_percent
FROM imbalance_calculation
ORDER BY ip, time DESC
|
|
13 min
1%
|
17,030 ms
|
46
sunrise_user
|
SELECT
date_trunc($1, to_timestamp(d.unix_time)) AS time,
ct.host_name,
ct.monitored_point,
ct.ct_value
FROM data_snmp_202601 d
CROSS JOIN LATERAL (
SELECT trim(both $2 from (d.data->d.type->$3->$4)::text) AS dsid
) dn
JOIN tenant t ON t.dsid = dn.dsid
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $5
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $6
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $7
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $8
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $9
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $10
LEFT JOIN assets a1 ON a1.id = ac1.asset_id
LEFT JOIN assets a2 ON a2.id = ac2.asset_id
LEFT JOIN assets a3 ON a3.id = ac3.asset_id
LEFT JOIN assets a4 ON a4.id = ac4.asset_id
LEFT JOIN assets a5 ON a5.id = ac5.asset_id
LEFT JOIN assets a6 ON a6.id = ac6.asset_id
CROSS JOIN LATERAL (
VALUES
($11, CASE WHEN ac1.ct_manufacturer ILIKE $12 THEN ((COALESCE((d.data->d.type->$13->$14)::text::float,$15)+$16)/$17) * COALESCE(ac1.ct_factor,$18)
WHEN ac1.ct_manufacturer ILIKE $19 THEN ((COALESCE((d.data->d.type->$20->$21)::text::float,$22)+$23)*$24) * COALESCE(ac1.ct_rating,$25) * COALESCE(ac1.ct_factor,$26)
ELSE COALESCE((d.data->d.type->$27->$28)::text::float,$29) END, ac1.monitored_point, a1.host_name),
($30, CASE WHEN ac2.ct_manufacturer ILIKE $31 THEN ((COALESCE((d.data->d.type->$32->$33)::text::float,$34)+$35)/$36) * COALESCE(ac2.ct_factor,$37)
WHEN ac2.ct_manufacturer ILIKE $38 THEN ((COALESCE((d.data->d.type->$39->$40)::text::float,$41)+$42)*$43) * COALESCE(ac2.ct_rating,$44) * COALESCE(ac2.ct_factor,$45)
ELSE COALESCE((d.data->d.type->$46->$47)::text::float,$48) END, ac2.monitored_point, a2.host_name),
($49, CASE WHEN ac3.ct_manufacturer ILIKE $50 THEN ((COALESCE((d.data->d.type->$51->$52)::text::float,$53)+$54)/$55) * COALESCE(ac3.ct_factor,$56)
WHEN ac3.ct_manufacturer ILIKE $57 THEN ((COALESCE((d.data->d.type->$58->$59)::text::float,$60)+$61)*$62) * COALESCE(ac3.ct_rating,$63) * COALESCE(ac3.ct_factor,$64)
ELSE COALESCE((d.data->d.type->$65->$66)::text::float,$67) END, ac3.monitored_point, a3.host_name),
($68, CASE WHEN ac4.ct_manufacturer ILIKE $69 THEN ((COALESCE((d.data->d.type->$70->$71)::text::float,$72)+$73)/$74) * COALESCE(ac4.ct_factor,$75)
WHEN ac4.ct_manufacturer ILIKE $76 THEN ((COALESCE((d.data->d.type->$77->$78)::text::float,$79)+$80)*$81) * COALESCE(ac4.ct_rating,$82) * COALESCE(ac4.ct_factor,$83)
ELSE COALESCE((d.data->d.type->$84->$85)::text::float,$86) END, ac4.monitored_point, a4.host_name),
($87, CASE WHEN ac5.ct_manufacturer ILIKE $88 THEN ((COALESCE((d.data->d.type->$89->$90)::text::float,$91)+$92)/$93) * COALESCE(ac5.ct_factor,$94)
WHEN ac5.ct_manufacturer ILIKE $95 THEN ((COALESCE((d.data->d.type->$96->$97)::text::float,$98)+$99)*$100) * COALESCE(ac5.ct_rating,$101) * COALESCE(ac5.ct_factor,$102)
ELSE COALESCE((d.data->d.type->$103->$104)::text::float,$105) END, ac5.monitored_point, a5.host_name),
($106, CASE WHEN ac6.ct_manufacturer ILIKE $107 THEN ((COALESCE((d.data->d.type->$108->$109)::text::float,$110)+$111)/$112) * COALESCE(ac6.ct_factor,$113)
WHEN ac6.ct_manufacturer ILIKE $114 THEN ((COALESCE((d.data->d.type->$115->$116)::text::float,$117)+$118)*$119) * COALESCE(ac6.ct_rating,$120) * COALESCE(ac6.ct_factor,$121)
ELSE COALESCE((d.data->d.type->$122->$123)::text::float,$124) END, ac6.monitored_point, a6.host_name)
) AS ct(port, ct_value, monitored_point, host_name)
WHERE ct.host_name = $125
AND d.time >= $126 AND d.time < $127
|
|
11 min
1%
|
32,341 ms
|
21
sunrise_user
|
SELECT
d.time,
a.host_name,
v.key AS metric,
CASE
WHEN v.value ~ $1
THEN v.value::float * COALESCE(pndp.normalization_function, $2)
ELSE $3
END AS normalized_value
FROM data_json_ts d
JOIN assets a ON a.id = d.asset_id
JOIN sites s ON a.site_id = s.id
CROSS JOIN LATERAL jsonb_each_text(d.data) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = a.part_number_id
WHERE s.name = $4
AND d.time >= $5 AND d.time < $6
AND v.value IS NOT NULL
|
|
11 min
1%
|
1 ms
|
1,305,342
sunrise_user
|
INSERT INTO data_json_ts ( asset_id, part_number_id, ip, time, time_unix, type, data) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7
)
|
|
9 min
0.8%
|
4,139 ms
|
129
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
--AND sites.name = 'BRNX01'
AND subcat_class.title = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
keys.key,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $5
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $6)
ELSE $7
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type = $8
WHERE jsonb_typeof(lp.data) = $9
AND keys.key IN ($10 /*, ... */)
),
power_calculations AS (
SELECT
ip,
time,
status,
host_name,
MAX(CASE WHEN key = $11 THEN normalized_value END) *
MAX(CASE WHEN key = $12 THEN normalized_value END) AS input_power_phase_a,
MAX(CASE WHEN key = $13 THEN normalized_value END) *
MAX(CASE WHEN key = $14 THEN normalized_value END) AS input_power_phase_b,
MAX(CASE WHEN key = $15 THEN normalized_value END) *
MAX(CASE WHEN key = $16 THEN normalized_value END) AS input_power_phase_c,
MAX(CASE WHEN key = $17 THEN normalized_value END) *
MAX(CASE WHEN key = $18 THEN normalized_value END) AS output_power_phase_a,
MAX(CASE WHEN key = $19 THEN normalized_value END) *
MAX(CASE WHEN key = $20 THEN normalized_value END) AS output_power_phase_b,
MAX(CASE WHEN key = $21 THEN normalized_value END) *
MAX(CASE WHEN key = $22 THEN normalized_value END) AS output_power_phase_c
FROM normalized_values
GROUP BY ip, time, status, host_name
)
SELECT
ip,
time,
host_name,
status,
CASE
WHEN (COALESCE(input_power_phase_a, $23) + COALESCE(input_power_phase_b, $24) + COALESCE(input_power_phase_c, $25)) > $26
THEN (
(COALESCE(output_power_phase_a, $27) + COALESCE(output_power_phase_b, $28) + COALESCE(output_power_phase_c, $29)) /
(COALESCE(input_power_phase_a, $30) + COALESCE(input_power_phase_b, $31) + COALESCE(input_power_phase_c, $32))
) * $33
ELSE $34
END AS efficiency
FROM power_calculations
ORDER BY ip, time DESC
|
|
6 min
0.6%
|
3,625 ms
|
106
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
keys.key,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $5
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $6)
ELSE $7
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type IN ($8 /*, ... */)
WHERE jsonb_typeof(lp.data) = $9
AND keys.key IN ($10 /*, ... */)
),
utilization_calculation AS (
SELECT
ip,
time,
status,
host_name,
MAX(CASE WHEN key = $11 THEN normalized_value END) AS total_real_power,
MAX(CASE WHEN key = $12 THEN normalized_value END) AS total_apparent_power
FROM normalized_values
GROUP BY ip, time, status, host_name
)
SELECT
ip,
time,
host_name,
status,
CASE
WHEN total_apparent_power > $13
THEN (total_real_power / total_apparent_power) * $14
ELSE $15
END AS apparent_power_utilization_percent
FROM utilization_calculation
ORDER BY ip, time DESC
|
|
6 min
0.6%
|
155 ms
|
2,240
sunrise_user
|
SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/
|
|
5 min
0.5%
|
8,838 ms
|
35
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
keys.key,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $5
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $6)
ELSE $7
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type IN ($8 /*, ... */)
WHERE jsonb_typeof(lp.data) = $9
AND keys.key IN ($10 /*, ... */)
),
imbalance_calculation AS (
SELECT
ip,
time,
status,
host_name,
MAX(CASE WHEN key = $11 THEN normalized_value END) AS va,
MAX(CASE WHEN key = $12 THEN normalized_value END) AS vb,
MAX(CASE WHEN key = $13 THEN normalized_value END) AS vc
FROM normalized_values
GROUP BY ip, time, status, host_name
)
SELECT
ip,
time,
host_name,
status,
CASE
WHEN (va + vb + vc) / $14 > $15
THEN ((GREATEST(va, vb, vc) - LEAST(va, vb, vc)) / ((va + vb + vc) / $16)) * $17
ELSE $18
END AS voltage_imbalance_percent
FROM imbalance_calculation
ORDER BY ip, time DESC
|
|
5 min
0.4%
|
0 ms
|
1,197,585
sunrise_user
|
INSERT INTO data_json_202603 ( asset_id, part_number_id, ip, time, time_unix, type, data) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7
)
|
|
5 min
0.4%
|
137,193 ms
|
2
as_ro_user
|
WITH date_range AS (
SELECT
$1::timestamptz as start_ts,
$2::timestamptz as end_ts,
EXTRACT($3 FROM $4::timestamptz)::bigint as start_unix,
EXTRACT($5 FROM $6::timestamptz)::bigint as end_unix
),
snmp_counts AS (
-- Count SNMP Temperature data points per asset for the full day
SELECT
dj.asset_id,
COUNT(*) as snmp_telemetry_count,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $7) as avg_snmp_temp
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
CROSS JOIN date_range dr
WHERE dj.time >= dr.start_ts AND dj.time < dr.end_ts
AND pndp.name ILIKE $8
GROUP BY dj.asset_id
),
an10_counts AS (
-- Count AN10 Physical Sensor data points per asset for the full day
-- Joins corelink_node to ensure we only count data from verified site gateways
SELECT
trh.asset_id,
COUNT(dn.unix_time) as an10_telemetry_count,
ROUND(AVG(((dn.data -> dn.type ->> $9)::double precision / $10))::numeric, $11) as avg_an10_temp
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
JOIN public.trh_sensor trh ON trh.register_address = dn.type
CROSS JOIN date_range dr
WHERE dn.acq_type = $12
AND dn.unix_time >= dr.start_unix AND dn.unix_time < dr.end_unix
GROUP BY trh.asset_id
)
SELECT
s.id as site_id,
s.name as site_name,
a.host_name,
a.ip as asset_ip,
COALESCE(sc.snmp_telemetry_count, $13) as snmp_readings,
COALESCE(sc.avg_snmp_temp, $14) as snmp_avg_temp,
COALESCE(ac.an10_telemetry_count, $15) as an10_readings,
COALESCE(ac.avg_an10_temp, $16) as an10_avg_temp,
-- Performance Assessment
CASE
WHEN COALESCE(sc.snmp_telemetry_count, $17) > $18 AND COALESCE(ac.an10_telemetry_count, $19) > $20 THEN $21
WHEN COALESCE(sc.snmp_telemetry_count, $22) > $23 AND COALESCE(ac.an10_telemetry_count, $24) = $25 THEN $26
WHEN COALESCE(sc.snmp_telemetry_count, $27) = $28 AND COALESCE(ac.an10_telemetry_count, $29) > $30 THEN $31
WHEN COALESCE(sc.snmp_telemetry_count, $32) BETWEEN $33 AND $34 THEN $35
ELSE $36
END as status_assessment
FROM public.assets a
JOIN public.sites s ON s.id = a.site_id
LEFT JOIN snmp_counts sc ON a.id = sc.asset_id
LEFT JOIN an10_counts ac ON a.id = ac.asset_id
ORDER BY s.id ASC, a.host_name ASC
|
|
4 min
0.4%
|
0 ms
|
1,632,277
sunrise_user
|
INSERT INTO data_normalized ( rtu_id, time, ip, port, status, firmware, processed, arrivaltime, acq_id, type, lock, unix_time, dsid, site_name, data, acq_type, data_pd_0, data_pd_1, data_pd_2, data_pd_3, data_pd_4, data_pd_5, data_pd_6, data_pd_7, data_pd_8, data_pd_9, data_pd_10, data_pd_11) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7,
$8,
$9,
$10,
$11,
$12,
$13,
$14,
$15,
$16,
$17,
$18,
$19,
$20,
$21,
$22,
$23,
$24,
$25,
$26,
$27,
$28
)
|
|
4 min
0.4%
|
58 ms
|
4,582
sunrise_user
|
select
d.type,
d.data,
d.time
from "data_snmp_202509" as "d" where "d"."ip" = $1 order by "d"."time" desc limit $2
Details
CREATE INDEX CONCURRENTLY ON data_snmp_202509 (ip, time)
Rows: 773779
Row progression: 773779, 45516, 0
Row estimates
- ip (=): 45516
- time (sort): 7
Existing indexes
- data_seq PRIMARY
- rtu_id, type, time
|
|
4 min
0.4%
|
128,338 ms
|
2
as_ro_user
|
WITH time_window AS (
SELECT
$1::timestamptz as start_ts,
$2::timestamptz as end_ts,
EXTRACT($3 FROM $4::timestamptz)::bigint as start_unix,
EXTRACT($5 FROM $6::timestamptz)::bigint as end_unix
),
site_assets AS (
-- Anchor to Site 4 (BRNX01)
SELECT id, host_name, ip, part_number_id
FROM public.assets
WHERE site_id = $7
),
snmp_counts AS (
-- Count SNMP Temperature and RH% data points
SELECT
dj.asset_id,
COUNT(CASE WHEN pndp.name ILIKE $8 THEN $9 END) as snmp_temp_count,
COUNT(CASE WHEN pndp.name ILIKE $10 OR pndp.name ILIKE $11 THEN $12 END) as snmp_rh_count,
ROUND(AVG(CASE WHEN pndp.name ILIKE $13 THEN (jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function) END)::numeric, $14) as avg_snmp_temp
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
CROSS JOIN time_window tw
WHERE dj.time >= tw.start_ts AND dj.time < tw.end_ts
GROUP BY dj.asset_id
),
an10_counts AS (
-- Count AN10 Physical Sensor data points and extract TRH ID
SELECT
trh.asset_id,
substring(trh.register_address from $15)::int as trh_id,
COUNT(dn.unix_time) as an10_count,
ROUND(AVG(((dn.data -> dn.type ->> $16)::double precision / $17))::numeric, $18) as avg_an10_temp,
ROUND(AVG(((dn.data -> dn.type ->> $19)::double precision / $20))::numeric, $21) as avg_an10_rh
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
JOIN public.trh_sensor trh ON trh.register_address = dn.type
CROSS JOIN time_window tw
WHERE cn.site_id = $22
AND dn.acq_type = $23
AND dn.unix_time >= tw.start_unix AND dn.unix_time < tw.end_unix
GROUP BY trh.asset_id, trh.register_address
)
SELECT
$24 as site_id,
ac.trh_id,
sa.host_name,
sa.ip as asset_ip,
COALESCE(sc.snmp_temp_count, $25) as snmp_temp_readings,
COALESCE(sc.snmp_rh_count, $26) as snmp_rh_readings,
COALESCE(ac.an10_count, $27) as an10_readings,
COALESCE(ac.avg_an10_temp, $28) as an10_avg_temp,
COALESCE(ac.avg_an10_rh, $29) as an10_avg_rh,
-- Assessment Logic
CASE
WHEN (COALESCE(sc.snmp_temp_count, $30) > $31 OR COALESCE(sc.snmp_rh_count, $32) > $33)
AND COALESCE(ac.an10_count, $34) > $35 THEN $36
WHEN (COALESCE(sc.snmp_temp_count, $37) > $38 OR COALESCE(sc.snmp_rh_count, $39) > $40) THEN $41
WHEN COALESCE(ac.an10_count, $42) > $43 THEN $44
ELSE $45
END as assessment
FROM site_assets sa
LEFT JOIN snmp_counts sc ON sa.id = sc.asset_id
LEFT JOIN an10_counts ac ON sa.id = ac.asset_id
ORDER BY sa.host_name ASC, ac.trh_id ASC
|
|
4 min
0.4%
|
249,619 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS window_start
),
-- ==========================================
-- 1. THE SNMP ENGINE (Filtered for fkaBRN003)
-- ==========================================
snmp_data AS (
SELECT
dm.host_name,
date_trunc($3, dj.time) as sync_time,
pndp.name as metric,
(jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function) as value,
pndp.unit
FROM public.data_json_ts dj
JOIN public.assets dm ON dj.asset_id = dm.id
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dm.host_name = $4 -- TARGET ASSET
AND dj.time >= NOW() - INTERVAL $5
AND pndp.data_type IN ($6 /*, ... */)
),
-- ==========================================
-- 2. THE CT AMPS ENGINE (Filtered for fkaBRN003)
-- ==========================================
ct_amps AS (
SELECT
a.host_name,
date_trunc($7, to_timestamp(d.unix_time)) as sync_time,
COALESCE(ac.monitored_point, $8 || ac.tenant_port) as metric,
CASE
WHEN ac.ct_manufacturer ILIKE $9 THEN (((COALESCE((d.data->d.type->$10->>(ac.tenant_port + $11))::float, $12)+$13) / $14) * ac.ct_factor)
WHEN ac.ct_manufacturer ILIKE $15 THEN (((COALESCE((d.data->d.type->$16->>(ac.tenant_port + $17))::float, $18)+$19) * $20) * COALESCE(ac.ct_rating, $21)) * ac.ct_factor
ELSE COALESCE((d.data->d.type->$22->>(ac.tenant_port + $23))::float, $24)
END as value,
$25 as unit
FROM public.ac_ct ac
JOIN public.tenant t ON t.id = ac.parent_tenant_id
JOIN public.assets a ON a.id = ac.asset_id
CROSS JOIN LATERAL (
SELECT unix_time, type, data FROM public.data_normalized
WHERE dsid = t.dsid AND acq_type = $26
AND unix_time >= (SELECT window_start FROM time_window)
ORDER BY unix_time DESC LIMIT $27
) d
WHERE a.host_name = $28 -- TARGET ASSET
),
-- ==========================================
-- 3. THE TRH ENGINE (Filtered for fkaBRN003)
-- ==========================================
trh_data AS (
SELECT
a.host_name,
date_trunc($29, to_timestamp(d.unix_time)) as sync_time,
trh.placement || $30 || (CASE WHEN m_idx.i = $31 THEN $32 ELSE $33 END) as metric,
(CASE
WHEN m_idx.i = $34 THEN ROUND(((d.data -> d.type ->> $35)::double precision / $36)::numeric, $37)
ELSE ROUND(((d.data -> d.type ->> $38)::double precision / $39)::numeric, $40)
END)::float as value,
(CASE WHEN m_idx.i = $41 THEN $42 ELSE $43 END) as unit
FROM public.trh_sensor trh
JOIN public.assets a ON a.id = trh.asset_id
CROSS JOIN LATERAL (SELECT i FROM generate_series($44,$45) i) m_idx -- Unpivots Temp and Hum in one go
CROSS JOIN LATERAL (
SELECT dn.unix_time, dn.type, dn.data FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
WHERE cn.site_id = $46 AND dn.acq_type = $47 AND dn.type = trh.register_address
ORDER BY dn.unix_time DESC LIMIT $48
) d
WHERE a.host_name = $49 -- TARGET ASSET
AND (d.data -> d.type ->> m_idx.i) ~ $50
)
-- ==========================================
-- FINAL UNIFIED OUTPUT
-- ==========================================
SELECT * FROM snmp_data
UNION ALL
SELECT * FROM ct_amps
UNION ALL
SELECT * FROM trh_data
ORDER BY metric ASC
|
|
4 min
0.4%
|
1,843 ms
|
130
sunrise_user
|
WITH data_packets AS (
SELECT
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time AS time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.time >= CURRENT_TIMESTAMP - INTERVAL $5
),
normalized_values AS (
SELECT
dp.ip,
dp.time,
dp.status,
dp.host_name,
keys.key,
jsonb_extract_path_text(dp.data, keys.key) AS raw_value,
CASE
WHEN jsonb_extract_path_text(dp.data, keys.key) ~ $6
THEN jsonb_extract_path_text(dp.data, keys.key)::float * COALESCE(pndp.normalization_function, $7)
ELSE $8
END AS normalized_value,
COALESCE(pndp.normalization_function, $9) AS normalization
FROM data_packets dp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(dp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = dp.part_number_id
AND pndp.data_type IN ($10 /*, ... */)
WHERE jsonb_typeof(dp.data) = $11
AND keys.key = $12
),
consumption_calculation AS (
SELECT DISTINCT ON (t1.ip)
t1.ip,
t1.time,
t1.status,
t1.host_name,
t1.raw_value AS raw_energy_t1,
t1.normalized_value AS energy_t1,
t2.raw_value AS raw_energy_t2,
t2.normalized_value AS energy_t2,
EXTRACT($13 FROM (t2.time - t1.time)) AS time_diff_seconds,
t1.normalization
FROM normalized_values t1
JOIN normalized_values t2 ON t1.ip = t2.ip AND t2.time > t1.time
WHERE t2.time = (
SELECT MIN(time)
FROM normalized_values t2
WHERE t2.time > t1.time
AND t2.ip = t1.ip
AND t2.time <= t1.time + INTERVAL $14
)
AND t1.normalized_value IS NOT NULL
AND t2.normalized_value IS NOT NULL
ORDER BY t1.ip, t1.time DESC
)
SELECT
ip,
-- time,
host_name,
-- status,
CASE
WHEN time_diff_seconds > $15 AND energy_t2 >= energy_t1
THEN (energy_t2 - energy_t1) / time_diff_seconds * $16
ELSE $17
END AS energy_consumption_rate_kwh_per_hour
FROM consumption_calculation
WHERE time_diff_seconds IS NOT NULL
ORDER BY ip
|
|
4 min
0.3%
|
12,298 ms
|
18
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
--AND sites.name = 'BRNX01'
AND subcat_class.title = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
keys.key,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $5
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $6)
ELSE $7
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type = $8
WHERE jsonb_typeof(lp.data) = $9
AND keys.key IN ($10 /*, ... */)
),
power_calculations AS (
SELECT
ip,
time,
status,
host_name,
MAX(CASE WHEN key = $11 THEN normalized_value END) *
MAX(CASE WHEN key = $12 THEN normalized_value END) AS input_power_phase_a,
MAX(CASE WHEN key = $13 THEN normalized_value END) *
MAX(CASE WHEN key = $14 THEN normalized_value END) AS input_power_phase_b,
MAX(CASE WHEN key = $15 THEN normalized_value END) *
MAX(CASE WHEN key = $16 THEN normalized_value END) AS input_power_phase_c,
MAX(CASE WHEN key = $17 THEN normalized_value END) *
MAX(CASE WHEN key = $18 THEN normalized_value END) AS output_power_phase_a,
MAX(CASE WHEN key = $19 THEN normalized_value END) *
MAX(CASE WHEN key = $20 THEN normalized_value END) AS output_power_phase_b,
MAX(CASE WHEN key = $21 THEN normalized_value END) *
MAX(CASE WHEN key = $22 THEN normalized_value END) AS output_power_phase_c
FROM normalized_values
GROUP BY ip, time, status, host_name
)
SELECT
ip,
time,
host_name,
status,
CASE
WHEN (COALESCE(input_power_phase_a, $23) + COALESCE(input_power_phase_b, $24) + COALESCE(input_power_phase_c, $25)) > $26
THEN (
(COALESCE(output_power_phase_a, $27) + COALESCE(output_power_phase_b, $28) + COALESCE(output_power_phase_c, $29)) /
(COALESCE(input_power_phase_a, $30) + COALESCE(input_power_phase_b, $31) + COALESCE(input_power_phase_c, $32))
) * $33
ELSE $34
END AS efficiency
FROM power_calculations
ORDER BY ip, time DESC
|
|
4 min
0.3%
|
12,981 ms
|
17
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
sites.cluster_id,
sites.class,
assets.host_name,
sites.name AS site_name,
--assets.floor,
assets.room,
--assets.zone,
sites.address AS site_address,
cat_class.title AS category,
subcat_class.title AS subcategory,
part_numbers.name AS part_number_name,
data_json.ip,
data_json.time,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
--data_json.data,
--data_json.type,
-- 🧠 Conditional Link Field Based on subcategory_title
CASE
WHEN subcat_class.title = $4 THEN
$5 || data_json.ip
WHEN subcat_class.title = $6 THEN
$7 || data_json.ip
WHEN subcat_class.title = $8 THEN
$9 || data_json.ip
WHEN subcat_class.title = $10 THEN
$11 || data_json.ip
ELSE $12
END AS link_url
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
ORDER BY data_json.ip, data_json.time DESC
|
|
4 min
0.3%
|
10,972 ms
|
20
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.time_unix,
data_json.data,
data_json.type,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
assets.floor,
assets.room,
assets.zone,
sites.name AS site_name,
sites.address AS site_address,
sites.cluster_id,
part_numbers.name AS part_number_name,
part_numbers.short_name AS part_number_short_name,
part_numbers.description AS part_number_description,
cat_class.title AS category_title,
cat_class.short_name AS category_short_name,
subcat_class.title AS subcategory_title,
subcat_class.short_name AS subcategory_short_name
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications cat_class ON part_numbers.category_id = cat_class.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
keys.key,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $5
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $6)
ELSE $7
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type IN ($8 /*, ... */)
WHERE jsonb_typeof(lp.data) = $9
AND keys.key IN ($10 /*, ... */)
),
utilization_calculation AS (
SELECT
ip,
time,
status,
host_name,
MAX(CASE WHEN key = $11 THEN normalized_value END) AS total_real_power,
MAX(CASE WHEN key = $12 THEN normalized_value END) AS total_apparent_power
FROM normalized_values
GROUP BY ip, time, status, host_name
)
SELECT
ip,
time,
host_name,
status,
CASE
WHEN total_apparent_power > $13
THEN (total_real_power / total_apparent_power) * $14
ELSE $15
END AS apparent_power_utilization_percent
FROM utilization_calculation
ORDER BY ip, time DESC
|
|
4 min
0.3%
|
108,371 ms
|
2
sunrise_user
|
WITH site_nodes AS (
SELECT cn.ip
FROM corelink_node cn
JOIN sites s ON s.id = cn.site_id
WHERE s.name = $1
),
-- Your “TRH type list” source (site-scoped)
trh_types AS (
SELECT DISTINCT ds.type
FROM data_snmp ds
WHERE ds.rtu_id IN (
SELECT REPLACE(host(sn.ip), $2, $3)::integer
FROM site_nodes sn
)
AND ds.type ~ $4
),
-- Latest row per type from Timescale table (still restricted to site IPs to avoid cross-site T1 collisions)
latest_per_type AS (
SELECT DISTINCT ON (d.type)
d.type,
d.unix_time,
d.data
FROM data_normalized d
JOIN site_nodes sn ON d.ip::inet = sn.ip
JOIN trh_types tt ON tt.type = d.type
WHERE d.acq_type = $5
ORDER BY d.type, d.unix_time DESC
)
SELECT
l.unix_time * $6 AS time_ms,
COALESCE(a.host_name, $7) AS host_name,
COALESCE(NULLIF(trh.placement, $8), $9) AS placement,
l.type AS register_address,
CASE
WHEN (l.data -> l.type ->> $10) ~ $11
THEN ROUND(((l.data -> l.type ->> $12)::double precision / $13)::numeric, $14)
ELSE $15
END AS temperature_c,
CASE
WHEN (l.data -> l.type ->> $16) ~ $17
THEN ROUND(((l.data -> l.type ->> $18)::double precision / $19)::numeric, $20)
ELSE $21
END AS humidity_pct
FROM latest_per_type l
LEFT JOIN trh_sensor trh
ON trh.register_address = l.type
LEFT JOIN assets a
ON a.id = trh.asset_id
ORDER BY
substring(l.type from $22)::int,
host_name,
placement
|
|
3 min
0.3%
|
1 ms
|
336,988
sunrise_user
|
SELECT
d.time_unix AS time,
d.part_number_id,
d.data
FROM data_json_ts d
WHERE d.ip = $1::inet
AND d.time_unix BETWEEN $2 AND $3
ORDER BY d.time_unix ASC
|
|
3 min
0.3%
|
308 ms
|
620
sunrise_user
|
WITH bounds AS (
SELECT
date_trunc($1, $2::timestamptz) AS day_start,
date_trunc($3, $4::timestamptz) + interval $5 AS day_end
),
base AS (
SELECT
dh.start_hour_time,
date_trunc($6, dh.start_hour_time) AS day,
dh.asset_id,
dh.ac_kwh_ct,
dh.ac_kwh_grid,
dh.input_power
FROM data_hour dh
JOIN bounds b
ON dh.start_hour_time >= b.day_start
AND dh.start_hour_time < b.day_end
WHERE dh.site_id = (SELECT id FROM sites WHERE name = $7)
),
energy_by_type AS (
SELECT
b.day,
pc.title,
CASE
WHEN pc.title = $8 THEN SUM(b.ac_kwh_grid)
WHEN pc.title = $9 THEN SUM(b.input_power) / $10
ELSE SUM(b.ac_kwh_ct)
END AS kwh
FROM base b
JOIN assets a ON a.id = b.asset_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
-- ⛔ NO ac_ct JOIN HERE
WHERE pc.title IN ($11 /*, ... */)
GROUP BY
b.day,
pc.title
)
SELECT
e.day::date AS day,
SUM(CASE WHEN e.title = $12 THEN e.kwh ELSE $13 END) AS ahu_kwh,
SUM(CASE WHEN e.title = $14 THEN e.kwh ELSE $15 END) AS drycooler_kwh,
SUM(CASE WHEN e.title = $16 THEN e.kwh ELSE $17 END) AS ups_kwh,
SUM(CASE WHEN e.title = $18 THEN e.kwh ELSE $19 END) AS grid_kwh,
SUM(CASE WHEN e.title = $20 THEN e.kwh ELSE $21 END) AS rectifier_kwh
FROM energy_by_type e
GROUP BY e.day
ORDER BY e.day
|
|
3 min
0.3%
|
166,556 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT
$1::timestamptz as start_ts,
$2::timestamptz as end_ts,
$3::bigint as start_unix,
$4::bigint as end_unix
),
site_assets AS (
SELECT id, host_name, ip, part_number_id FROM public.assets WHERE site_id = $5
),
snmp_temp AS (
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $6) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= (SELECT start_ts FROM time_window) AND dj.time < (SELECT end_ts FROM time_window)
AND pndp.name ILIKE $7
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS (
SELECT
trh.asset_id,
substring(trh.register_address from $8)::int as trh_id,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $9)::double precision / $10))::numeric, $11) as avg_val
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
JOIN public.trh_sensor trh ON trh.register_address = dn.type
WHERE cn.site_id = $12 AND dn.acq_type = $13
AND dn.unix_time >= (SELECT start_unix FROM time_window) AND dn.unix_time < (SELECT end_unix FROM time_window)
GROUP BY trh.asset_id, trh.register_address
)
SELECT $14 as site_id, ac.trh_id, sa.host_name, sa.ip as asset_ip,
COALESCE(sc.readings, $15) as snmp_temp_readings, COALESCE(ac.readings, $16) as an10_readings,
COALESCE(sc.avg_val, $17) as snmp_avg_temp, COALESCE(ac.avg_val, $18) as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $19) > $20 AND COALESCE(ac.readings, $21) > $22 THEN $23
WHEN COALESCE(sc.readings, $24) > $25 THEN $26
WHEN COALESCE(ac.readings, $27) > $28 THEN $29
ELSE $30
END as assessment
FROM site_assets sa
LEFT JOIN snmp_temp sc ON sa.id = sc.asset_id
LEFT JOIN an10_temp ac ON sa.id = ac.asset_id
ORDER BY sa.host_name ASC, ac.trh_id ASC
|
|
3 min
0.3%
|
163,477 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT
$1::timestamptz as start_ts,
$2::timestamptz as end_ts,
$3::bigint as start_unix,
$4::bigint as end_unix
),
site_assets AS (
-- Filters specifically for BRNX01 assets
SELECT id, host_name, ip, part_number_id FROM public.assets WHERE site_id = $5
),
snmp_temp AS (
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $6) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= (SELECT start_ts FROM time_window) AND dj.time < (SELECT end_ts FROM time_window)
AND pndp.name ILIKE $7
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS (
SELECT
trh.asset_id,
substring(trh.register_address from $8)::int as trh_id,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $9)::double precision / $10))::numeric, $11) as avg_val
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
JOIN public.trh_sensor trh ON trh.register_address = dn.type
WHERE cn.site_id = $12 AND dn.acq_type = $13
AND dn.unix_time >= (SELECT start_unix FROM time_window) AND dn.unix_time < (SELECT end_unix FROM time_window)
GROUP BY trh.asset_id, trh.register_address
)
SELECT
$14 as site_name,
ac.trh_id,
sa.host_name,
sa.ip as asset_ip,
COALESCE(sc.readings, $15) as snmp_temp_readings,
COALESCE(ac.readings, $16) as an10_readings,
COALESCE(sc.avg_val, $17) as snmp_avg_temp,
COALESCE(ac.avg_val, $18) as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $19) > $20 AND COALESCE(ac.readings, $21) > $22 THEN $23
WHEN COALESCE(sc.readings, $24) > $25 THEN $26
WHEN COALESCE(ac.readings, $27) > $28 THEN $29
ELSE $30
END as assessment
FROM site_assets sa
LEFT JOIN snmp_temp sc ON sa.id = sc.asset_id
LEFT JOIN an10_temp ac ON sa.id = ac.asset_id
ORDER BY sa.host_name ASC, ac.trh_id ASC
|
|
2 min
0.2%
|
133,123 ms
|
1
as_ro_user
|
WITH date_range AS (
SELECT
$1::timestamptz as start_ts,
$2::timestamptz as end_ts,
EXTRACT($3 FROM $4::timestamptz)::bigint as start_unix,
EXTRACT($5 FROM $6::timestamptz)::bigint as end_unix
),
snmp_counts AS (
-- Count SNMP Temperature data points for the 10-minute window
SELECT
dj.asset_id,
COUNT(*) as snmp_telemetry_count,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $7) as avg_snmp_temp
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
CROSS JOIN date_range dr
WHERE dj.time >= dr.start_ts AND dj.time < dr.end_ts
AND pndp.name ILIKE $8
GROUP BY dj.asset_id
),
an10_counts AS (
-- Count AN10 Physical Sensor data points for the 10-minute window
SELECT
trh.asset_id,
COUNT(dn.unix_time) as an10_telemetry_count,
ROUND(AVG(((dn.data -> dn.type ->> $9)::double precision / $10))::numeric, $11) as avg_an10_temp
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
JOIN public.trh_sensor trh ON trh.register_address = dn.type
CROSS JOIN date_range dr
WHERE dn.acq_type = $12
AND dn.unix_time >= dr.start_unix AND dn.unix_time < dr.end_unix
GROUP BY trh.asset_id
)
SELECT
s.id as site_id,
s.name as site_name,
a.host_name,
a.ip as asset_ip,
COALESCE(sc.snmp_telemetry_count, $13) as snmp_readings,
COALESCE(sc.avg_snmp_temp, $14) as snmp_avg_temp,
COALESCE(ac.an10_telemetry_count, $15) as an10_readings,
COALESCE(ac.avg_an10_temp, $16) as an10_avg_temp,
-- Performance Assessment for 10-min window
CASE
WHEN COALESCE(sc.snmp_telemetry_count, $17) > $18 AND COALESCE(ac.an10_telemetry_count, $19) > $20 THEN $21
WHEN COALESCE(sc.snmp_telemetry_count, $22) > $23 THEN $24
WHEN COALESCE(ac.an10_telemetry_count, $25) > $26 THEN $27
ELSE $28
END as status_assessment
FROM public.assets a
JOIN public.sites s ON s.id = a.site_id
LEFT JOIN snmp_counts sc ON a.id = sc.asset_id
LEFT JOIN an10_counts ac ON a.id = ac.asset_id
ORDER BY s.id ASC, a.host_name ASC
|
|
2 min
0.2%
|
132,128 ms
|
1
as_ro_user
|
WITH date_range AS (SELECT $1::timestamptz as s_ts, $2::timestamptz as e_ts, $3::bigint as s_u, $4::bigint as e_u),
snmp AS (SELECT dj.asset_id, COUNT(*) as c, ROUND(AVG((jsonb_extract_path_text(dj.data, p.name)::float * p.normalization_function))::numeric, $5) as v FROM public.data_json_ts dj JOIN public.part_number_data_points p ON dj.part_number_id = p.part_number_id CROSS JOIN date_range dr WHERE dj.time >= dr.s_ts AND dj.time < dr.e_ts AND p.name ILIKE $6 GROUP BY dj.asset_id),
an10 AS (SELECT trh.asset_id, COUNT(dn.unix_time) as c, ROUND(AVG(((dn.data -> dn.type ->> $7)::double precision / $8))::numeric, $9) as v FROM public.data_normalized dn JOIN public.corelink_node cn ON dn.ip::inet = cn.ip JOIN public.trh_sensor trh ON trh.register_address = dn.type CROSS JOIN date_range dr WHERE cn.site_id = $10 AND dn.acq_type = $11 AND dn.unix_time >= dr.s_u AND dn.unix_time < dr.e_u GROUP BY trh.asset_id)
SELECT $12 as site_id, $13 as site_name, a.host_name, COALESCE(sc.c, $14) as snmp_readings, COALESCE(sc.v, $15) as snmp_avg, COALESCE(ac.c, $16) as an10_readings, COALESCE(ac.v, $17) as an10_avg FROM public.assets a LEFT JOIN snmp sc ON a.id = sc.asset_id LEFT JOIN an10 ac ON a.id = ac.asset_id WHERE a.site_id = $18 ORDER BY a.host_name
|
|
2 min
0.2%
|
40,069 ms
|
3
as_ro_user
|
WITH site_assets AS (
-- Anchor to Site 4 (BRNX01)
SELECT id, host_name, ip, part_number_id
FROM public.assets
WHERE site_id = $1
),
snmp_counts AS (
-- Count SNMP Temperature data points for March 1st
SELECT
dj.asset_id,
COUNT(*) as snmp_telemetry_count,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $2) as avg_snmp_temp
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $3
AND dj.time < $4
AND pndp.name ILIKE $5
GROUP BY dj.asset_id
),
an10_counts AS (
-- Count AN10 Physical Sensor data points for March 1st
-- Anchor to Gateway IPs via corelink_node to ensure Site 4 isolation
SELECT
trh.asset_id,
COUNT(dn.unix_time) as an10_telemetry_count,
ROUND(AVG(((dn.data -> dn.type ->> $6)::double precision / $7))::numeric, $8) as avg_an10_temp
FROM public.data_normalized dn
JOIN public.corelink_node cn ON dn.ip::inet = cn.ip
JOIN public.trh_sensor trh ON trh.register_address = dn.type
WHERE cn.site_id = $9
AND dn.acq_type = $10
AND dn.unix_time >= EXTRACT($11 FROM $12::timestamptz)::bigint
AND dn.unix_time < EXTRACT($13 FROM $14::timestamptz)::bigint
GROUP BY trh.asset_id
)
SELECT
sa.host_name,
sa.ip as asset_ip,
COALESCE(sc.snmp_telemetry_count, $15) as snmp_readings,
COALESCE(sc.avg_snmp_temp, $16) as snmp_avg_temp,
COALESCE(ac.an10_telemetry_count, $17) as an10_readings,
COALESCE(ac.avg_an10_temp, $18) as an10_avg_temp,
-- Assessment Logic
CASE
WHEN COALESCE(sc.snmp_telemetry_count, $19) > $20 AND COALESCE(ac.an10_telemetry_count, $21) > $22 THEN $23
WHEN COALESCE(sc.snmp_telemetry_count, $24) > $25 THEN $26
WHEN COALESCE(ac.an10_telemetry_count, $27) > $28 THEN $29
ELSE $30
END as assessment
FROM site_assets sa
LEFT JOIN snmp_counts sc ON sa.id = sc.asset_id
LEFT JOIN an10_counts ac ON sa.id = ac.asset_id
ORDER BY sa.host_name ASC
|
|
2 min
0.2%
|
191 ms
|
625
sunrise_user
|
SELECT
dh.start_hour_time AS time,
SUM(dh.ac_kwh_ct) AS cooling
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $1 AND $2
AND s.name = $3
AND pc.title IN ($4 /*, ... */)
GROUP BY dh.start_hour_time
|
|
2 min
0.2%
|
19,454 ms
|
6
sunrise_user
|
select * from data_json_ts where asset_id = $1 AND time BETWEEN $2 AND $3
|
|
2 min
0.2%
|
106,662 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT EXTRACT($1 FROM (NOW() - INTERVAL $2))::bigint AS window_start
),
valid_ips AS (
-- Pre-cast the IP to a string safely BEFORE joining the hypertable
SELECT host(ip) AS gateway_ip
FROM public.corelink_node
WHERE site_id = $3
),
trh_map AS (
SELECT
trh.register_address,
COALESCE(NULLIF(trh.placement, $4), $5) AS placement,
a.host_name
FROM public.trh_sensor trh
JOIN public.assets a ON a.id = trh.asset_id
WHERE a.site_id = $6
),
recent_data AS (
-- TimescaleDB loves this: a dead-simple time filter with no sorting
SELECT unix_time, type, data, ip
FROM public.data_normalized
WHERE unix_time >= (SELECT window_start FROM time_window)
AND acq_type = $7
),
matched_latest AS (
-- Let RAM handle the sorting using ROW_NUMBER
SELECT
m.host_name,
m.placement,
r.unix_time,
r.type,
r.data,
ROW_NUMBER() OVER(PARTITION BY m.host_name, m.placement ORDER BY r.unix_time DESC) as rn
FROM recent_data r
JOIN trh_map m ON r.type = m.register_address
JOIN valid_ips v ON r.ip = v.gateway_ip
)
-- UNPIVOT TEMPERATURE
SELECT
host_name,
date_trunc($8, to_timestamp(unix_time)) AS sync_time,
placement || $9 AS metric,
ROUND(((data -> type ->> $10)::double precision / $11)::numeric, $12) AS value,
$13 AS unit
FROM matched_latest
WHERE rn = $14 AND (data -> type ->> $15) ~ $16
UNION ALL
-- UNPIVOT HUMIDITY
SELECT
host_name,
date_trunc($17, to_timestamp(unix_time)) AS sync_time,
placement || $18 AS metric,
ROUND(((data -> type ->> $19)::double precision / $20)::numeric, $21) AS value,
$22 AS unit
FROM matched_latest
WHERE rn = $23 AND (data -> type ->> $24) ~ $25
ORDER BY host_name, metric
|
|
2 min
0.2%
|
106,446 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS window_start
),
valid_ips AS MATERIALIZED (
-- 1. PRE-CAST IPs to text to prevent index bypassing!
SELECT ip::text AS ip_text
FROM public.corelink_node
WHERE site_id = $3
),
trh_mapping AS MATERIALIZED (
SELECT trh.register_address, COALESCE(NULLIF(trh.placement, $4), $5) AS placement, a.host_name
FROM public.trh_sensor trh
JOIN public.assets a ON a.id = trh.asset_id
WHERE a.site_id = $6
),
trh_latest_raw AS MATERIALIZED (
-- 2. Grab the 15 min chunk using strict IN filters instead of slow LATERAL loops
SELECT DISTINCT ON (d.type)
d.unix_time, d.type, d.data
FROM public.data_normalized d
WHERE d.unix_time >= (SELECT window_start FROM time_window)
AND d.acq_type = $7
AND d.ip IN (SELECT ip_text FROM valid_ips)
AND d.type IN (SELECT register_address FROM trh_mapping)
ORDER BY d.type, d.unix_time DESC
)
-- 3. UNPIVOT (Testing Temperature Only to check speed)
SELECT
m.host_name,
date_trunc($8, to_timestamp(r.unix_time)) AS sync_time,
m.placement || $9 AS metric,
ROUND(((r.data -> r.type ->> $10)::double precision / $11)::numeric, $12) AS value,
$13 AS unit
FROM trh_latest_raw r
JOIN trh_mapping m ON r.type = m.register_address
WHERE (r.data -> r.type ->> $14) ~ $15
|
|
2 min
0.2%
|
98,803 ms
|
1
sunrise_user
|
WITH site_nodes AS (
SELECT cn.ip
FROM corelink_node cn
JOIN sites s ON s.id = cn.site_id
WHERE s.name = $1
),
latest_per_type AS (
SELECT DISTINCT ON (d.type)
d.type,
d.unix_time,
d.data
FROM data_normalized d
JOIN site_nodes sn ON d.ip::inet = sn.ip
WHERE d.acq_type = $2
ORDER BY d.type, d.unix_time DESC
)
SELECT
l.unix_time * $3 AS time_ms,
COALESCE(a.host_name, $4) AS host_name,
COALESCE(NULLIF(trh.placement, $5), $6) AS placement,
l.type AS register_address,
CASE
WHEN (l.data -> l.type ->> $7) ~ $8
THEN ROUND(((l.data -> l.type ->> $9)::double precision / $10)::numeric, $11)
ELSE $12
END AS temperature_c,
CASE
WHEN (l.data -> l.type ->> $13) ~ $14
THEN ROUND(((l.data -> l.type ->> $15)::double precision / $16)::numeric, $17)
ELSE $18
END AS humidity_pct
FROM latest_per_type l
LEFT JOIN trh_sensor trh
ON trh.register_address = l.type
LEFT JOIN assets a
ON a.id = trh.asset_id
ORDER BY host_name, placement, register_address
|
|
2 min
0.1%
|
31,505 ms
|
3
sunrise_user
|
INSERT INTO dashboard_cache
SELECT dm.host_name, date_trunc($1, dj.time), pndp.name,
(jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function), pndp.unit
FROM public.data_json_ts dj
JOIN public.assets dm ON dj.asset_id = dm.id
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dm.site_id = $2 AND dm.ip != $3
AND dj.time >= NOW() - INTERVAL $4
AND pndp.data_type IN ($5 /*, ... */)
|
|
2 min
0.1%
|
45,749 ms
|
2
as_ro_user
|
SELECT
time,
data->>$1 AS snmp_system_voltage,
data->>$2 AS snmp_load_current
FROM public.data_json_ts
WHERE asset_id = $3 -- fngBRN001's Asset ID
AND time > NOW() - INTERVAL $4
ORDER BY time DESC
LIMIT $5
|
|
1 min
0.1%
|
88,376 ms
|
1
sunrise_user
|
SELECT
d.time,
d.type,
d.data,
trh.placement,
a.host_name
FROM sites s
JOIN corelink_node cn ON cn.site_id = s.id
JOIN data_normalized d ON d.ip::inet = cn.ip
JOIN trh_sensor trh ON trh.register_address = d.type
JOIN assets a ON a.id = trh.asset_id
WHERE s.name = $1
AND a.host_name = $2
AND d.acq_type = $3
AND d.time >= $4
ORDER BY d.unix_time ASC
|
|
1 min
0.1%
|
0 ms
|
905,443
sunrise_user
|
INSERT INTO data_snmp_202604 ( rtu_id, time, ip, port, status, firmware, processed, arrivaltime, acq_id, type, lock, unix_time, dsid, site_name, data) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7,
$8,
$9,
$10,
$11,
$12,
$13,
$14,
$15
)
|
|
1 min
0.1%
|
86,958 ms
|
1
sunrise_user
|
SELECT
d.time,
d.type,
d.data,
trh.placement,
a.host_name
FROM data_normalized d
JOIN corelink_node cn ON d.ip::inet = cn.ip
JOIN sites s ON s.id = cn.site_id
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE s.name = $1
AND a.host_name = $2
AND d.acq_type = $3
AND d.time >= $4
ORDER BY d.unix_time ASC
|
|
1 min
0.1%
|
85,356 ms
|
1
sunrise_user
|
WITH trh_map AS (
SELECT
trh.register_address,
COALESCE(NULLIF(trh.placement, $1), $2) AS placement
FROM trh_sensor trh
JOIN assets a ON a.id = trh.asset_id
WHERE a.host_name = $3
),
latest AS (
SELECT DISTINCT ON (m.placement)
d.unix_time,
d.time,
m.placement,
d.type,
d.data
FROM trh_map m
JOIN data_normalized d
ON d.type = m.register_address
JOIN corelink_node cn
ON d.ip::inet = cn.ip
JOIN sites s
ON s.id = cn.site_id
WHERE s.name = $4
AND d.acq_type = $5
ORDER BY m.placement, d.unix_time DESC
)
SELECT
unix_time * $6 AS time_ms,
placement,
-- normalized temperature
CASE
WHEN (data -> type ->> $7) ~ $8
THEN ROUND(((data -> type ->> $9)::double precision / $10)::numeric, $11)
ELSE $12
END AS temperature_c,
-- normalized humidity
CASE
WHEN (data -> type ->> $13) ~ $14
THEN ROUND(((data -> type ->> $15)::double precision / $16)::numeric, $17)
ELSE $18
END AS humidity_pct
FROM latest
ORDER BY placement
|
|
1 min
0.1%
|
216 ms
|
383
sunrise_user
|
SELECT
a.host_name || $1 || pc.title || $2 AS title_1,
dh.start_hour_time AS time,
SUM(dh.ac_kwh_ct) AS kwh
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $3 AND $4
AND s.name = $5
AND pc.title IN ($6 /*, ... */)
AND a.host_name NOT IN ($7 /*, ... */)
GROUP BY a.host_name, pc.title, dh.start_hour_time
|
|
1 min
0.1%
|
39,984 ms
|
2
sunrise_user
|
SELECT
d.time,
d.time_unix,
a.host_name,
v.key AS metric,
-- Apply normalization function to raw values
CASE
WHEN v.value ~ $1
THEN v.value::float * COALESCE(pndp.normalization_function, $2)
ELSE $3
END AS normalized_value,
pndp.unit,
s.name AS site_name
FROM data_json_ts d
JOIN assets a ON a.id = d.asset_id
JOIN sites s ON a.site_id = s.id
-- This expands ALL keys within the data jsonb column
CROSS JOIN LATERAL jsonb_each_text(d.data) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = a.part_number_id
AND pndp.data_type IN ($4 /*, ... */)
WHERE s.name = $5 -- Filtered by site for your export script
AND d.time >= $6 AND d.time < $7
ORDER BY d.time ASC, a.host_name, v.key LIMIT $8
|
|
1 min
0.1%
|
39,713 ms
|
2
sunrise_user
|
WITH target_assets AS MATERIALIZED (
-- 1. Isolate the specific Zurich assets to keep the query ultra-fast
SELECT id, host_name
FROM public.assets
WHERE site_id = $1 AND host_name IN ($2 /*, ... */)
),
raw_telemetry AS MATERIALIZED (
-- 2. Extract February data ONLY for the problematic streams
SELECT
dj.time,
ta.host_name,
pndp.name as stream_name,
(jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function)::numeric as val
FROM public.data_json_ts dj
JOIN target_assets ta ON dj.asset_id = ta.id
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $3 AND dj.time < $4
AND pndp.name IN ($5 /*, ... */)
),
calc_median AS MATERIALIZED (
-- 3. THE BASELINE: Find the exact middle value (Median) for the month
SELECT
host_name,
stream_name,
PERCENTILE_CONT($6) WITHIN GROUP (ORDER BY val) as median_val
FROM raw_telemetry
GROUP BY host_name, stream_name
),
calc_mad AS MATERIALIZED (
-- 4. THE TOLERANCE: Calculate the Median Absolute Deviation (MAD)
SELECT
r.host_name,
r.stream_name,
c.median_val,
PERCENTILE_CONT($7) WITHIN GROUP (ORDER BY ABS(r.val - c.median_val)) as mad_val
FROM raw_telemetry r
JOIN calc_median c ON r.host_name = c.host_name AND r.stream_name = c.stream_name
GROUP BY r.host_name, r.stream_name, c.median_val
)
-- 5. THE TRAP: Flag only the points that violate the allowed tolerance
SELECT
r.time,
r.host_name,
r.stream_name,
r.val as rogue_value,
m.median_val as safe_baseline,
m.mad_val as normal_fluctuation,
ABS(r.val - m.median_val) as deviation_amount,
$8 as status
FROM raw_telemetry r
JOIN calc_mad m ON r.host_name = m.host_name AND r.stream_name = m.stream_name
WHERE
-- The threshold: We flag anything that spikes more than 5x the normal noise.
-- We include a fallback (50) in case the MAD is perfectly 0 (no noise at all).
ABS(r.val - m.median_val) > CASE WHEN m.mad_val = $9 THEN $10 ELSE ($11 * m.mad_val) END
ORDER BY
r.host_name ASC,
r.time ASC
|
|
1 min
0.1%
|
39,284 ms
|
2
as_ro_user
|
WITH site_nodes AS MATERIALIZED (
-- Identifying Gateway IPs for BADY01 (Site 2)
SELECT cn.ip as inet_ip FROM public.corelink_node cn WHERE cn.site_id = $1
),
site_assets AS MATERIALIZED (
-- Pulling notes and equipment names for BADY01
SELECT id, host_name, notes, equipment_name, ip FROM public.assets WHERE site_id = $2
),
snmp_rh AS MATERIALIZED (
-- Hardware Humidity Telemetry (RH%)
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $3) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $4 AND dj.time < $5
AND (pndp.name ILIKE $6 OR pndp.name ILIKE $7)
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_rh AS MATERIALIZED (
-- Sensor Humidity Telemetry (Index 1 in the data array)
SELECT
dn.type as reg_addr,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $8)::double precision / $9))::numeric, $10) as avg_val
FROM public.data_normalized dn
JOIN site_nodes sn ON dn.ip::inet = sn.inet_ip
WHERE dn.unix_time >= $11 AND dn.unix_time < $12
AND dn.acq_type = $13
GROUP BY dn.type
),
site_trh AS MATERIALIZED (
-- Metadata mapping for sensors
SELECT
asset_id,
register_address,
placement,
substring(register_address from $14)::int as trh_id
FROM public.trh_sensor
WHERE asset_id IN (SELECT id FROM site_assets)
OR register_address IN (SELECT reg_addr FROM an10_rh)
),
final_data AS MATERIALIZED (
SELECT
$15::text as site_name,
COALESCE(trh.trh_id, substring(ac.reg_addr from $16)::int) as trh_id,
sa.host_name::text as host_name,
COALESCE(sa.notes, sa.equipment_name)::text as asset_info,
trh.placement::text as sensor_placement,
COALESCE(sc.readings, $17)::bigint as snmp_rh_readings,
COALESCE(ac.readings, $18)::bigint as an10_readings,
COALESCE(sc.avg_val, $19)::numeric as snmp_avg_rh,
COALESCE(ac.avg_val, $20)::numeric as an10_avg_rh,
CASE
WHEN COALESCE(sc.readings, $21) > $22 AND COALESCE(ac.readings, $23) > $24 THEN $25
WHEN COALESCE(sc.readings, $26) > $27 THEN $28
WHEN COALESCE(ac.readings, $29) > $30 THEN $31
ELSE $32
END::text as assessment
FROM site_assets sa
FULL OUTER JOIN site_trh trh ON trh.asset_id = sa.id
FULL OUTER JOIN an10_rh ac ON ac.reg_addr = trh.register_address
LEFT JOIN snmp_rh sc ON sc.asset_id = sa.id
)
-- 1. Main Output
SELECT
$33 as sort_order,
site_name, trh_id, host_name, asset_info, sensor_placement,
snmp_rh_readings, an10_readings, snmp_avg_rh, an10_avg_rh, assessment
FROM final_data
UNION ALL
-- 2. Summary Totals
SELECT
$34 as sort_order,
$35::text as site_name,
$36::int as trh_id,
$37::text as host_name,
$38::text as asset_info,
($39 || assessment || $40 || COUNT(*))::text as sensor_placement,
$41::bigint as snmp_rh_readings,
$42::bigint as an10_readings,
$43::numeric as snmp_avg_rh,
$44::numeric as an10_avg_rh,
$45::text as assessment
FROM final_data
GROUP BY assessment
ORDER BY
sort_order ASC,
host_name ASC NULLS LAST,
trh_id ASC
|
|
1 min
0.1%
|
38,687 ms
|
2
as_ro_user
|
WITH site_nodes AS MATERIALIZED (
-- Get BRNX01 Gateways for physical sensor anchoring
SELECT cn.ip as inet_ip FROM public.corelink_node cn WHERE cn.site_id = $1
),
site_assets AS MATERIALIZED (
-- Get BRNX01 Hostnames for SNMP anchoring
SELECT id, host_name, ip FROM public.assets WHERE site_id = $2
),
snmp_temp AS MATERIALIZED (
-- Hardware Telemetry
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $3) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $4 AND dj.time < $5
AND pndp.name ILIKE $6
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS MATERIALIZED (
-- Sensor Telemetry (Uses gateway IPs so standalone sensors are included)
SELECT
dn.type as reg_addr,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $7)::double precision / $8))::numeric, $9) as avg_val
FROM public.data_normalized dn
JOIN site_nodes sn ON dn.ip::inet = sn.inet_ip
WHERE dn.unix_time >= $10 AND dn.unix_time < $11
AND dn.acq_type = $12
GROUP BY dn.type
),
site_trh AS MATERIALIZED (
-- PRE-LOAD METADATA: Catch sensors linked to assets OR actively reporting right now
SELECT
asset_id,
register_address,
placement,
substring(register_address from $13)::int as trh_id
FROM public.trh_sensor
WHERE asset_id IN (SELECT id FROM site_assets)
OR register_address IN (SELECT reg_addr FROM an10_temp)
)
SELECT
$14 as site_name,
COALESCE(trh.trh_id, substring(ac.reg_addr from $15)::int) as trh_id,
COALESCE(sa.host_name, $16) as host_name,
COALESCE(trh.placement, $17) as description,
COALESCE(sc.readings, $18) as snmp_temp_readings,
COALESCE(ac.readings, $19) as an10_readings,
COALESCE(sc.avg_val, $20) as snmp_avg_temp,
COALESCE(ac.avg_val, $21) as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $22) > $23 AND COALESCE(ac.readings, $24) > $25 THEN $26
WHEN COALESCE(sc.readings, $27) > $28 THEN $29
WHEN COALESCE(ac.readings, $30) > $31 THEN $32
ELSE $33
END as assessment
FROM site_assets sa
-- FULL OUTER JOIN ensures standalone room sensors and unassigned assets both survive
FULL OUTER JOIN site_trh trh ON trh.asset_id = sa.id
FULL OUTER JOIN an10_temp ac ON ac.reg_addr = trh.register_address
LEFT JOIN snmp_temp sc ON sc.asset_id = sa.id
ORDER BY host_name ASC, trh_id ASC
|
|
1 min
0.1%
|
7,194 ms
|
10
sunrise_user
|
SELECT
COALESCE(tlm.label, $1) || $2 || type || $3 || dsid AS __text,
type AS __value
FROM (
SELECT DISTINCT ON (d.rtu_id, d.type)
d.type,
trim(both $4 from (d.data->d.type->$5->>$6)) AS dsid,
d.time,
d.data_seq
FROM data_snmp d
WHERE d.rtu_id = $7
AND d.type ~ $8
ORDER BY d.rtu_id, d.type, d.time DESC, d.data_seq DESC
) latest
LEFT JOIN telemetrylink_module tlm
ON latest.dsid IN (
tlm.tenant1_dsid,
tlm.tenant2_dsid,
tlm.tenant3_dsid
)
ORDER BY substring(type from $9)::int
|
|
1 min
0.1%
|
35,276 ms
|
2
as_ro_user
|
INSERT INTO dashboard_cache
SELECT dm.host_name, date_trunc($1, dj.time), pndp.name,
(jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function), pndp.unit
FROM public.data_json_ts dj
JOIN public.assets dm ON dj.asset_id = dm.id
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dm.site_id = $2 AND dm.ip != $3
AND dj.time >= NOW() - INTERVAL $4
AND pndp.data_type IN ($5 /*, ... */)
|
|
1 min
< 0.1%
|
1,366 ms
|
46
sunrise_user
|
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY type ORDER BY time DESC, data_seq DESC) AS rn
FROM data_snmp
WHERE rtu_id = $1 AND type IN ($2)
)
SELECT
time,
-- type,
-- data_seq,
((data->type->$3)::text::float)/$4 AS temperature,
((data->type->$5)::text::float)/$6 AS humidity
FROM ranked
WHERE rn = $7
|
|
1 min
< 0.1%
|
943 ms
|
65
sunrise_user
|
WITH ranked AS (
SELECT d.*,
ROW_NUMBER() OVER (PARTITION BY d.type ORDER BY d.time DESC, d.data_seq DESC) AS rn,
trim(both $1 from (d.data->d.type->$2->$3)::text) AS snmp_dsid
FROM data_snmp d
WHERE d.rtu_id = $4
AND d.type IN ($5)
)
SELECT DISTINCT ON (r.time)
r.time,
r.type,
r.snmp_dsid AS dsid,
COALESCE((r.data->r.type->$6->$7)::text::integer, $8) AS temperature,
COALESCE((r.data->r.type->$9->$10)::numeric, $11) AS supply_voltage,
-- r.data->r.type->'PD'->0 AS supply_voltage,
-- ========== AC CT 1 ==========
CASE
WHEN ac1.ct_manufacturer ILIKE $12
THEN (((COALESCE((r.data->r.type->$13->$14)::text::float, $15)+$16) / $17) * $18) * ac1.ct_factor
WHEN ac1.ct_manufacturer ILIKE $19
THEN (((COALESCE((r.data->r.type->$20->$21)::text::float, $22)+$23) * $24) * COALESCE(ac1.ct_rating, $25)) * ac1.ct_factor
ELSE COALESCE((r.data->r.type->$26->$27)::text::float, $28)
END AS ac_ct_1,
-- ========== AC CT 2 ==========
CASE
WHEN ac2.ct_manufacturer ILIKE $29
THEN (((COALESCE((r.data->r.type->$30->$31)::text::float, $32)+$33) / $34) * $35) * ac2.ct_factor
WHEN ac2.ct_manufacturer ILIKE $36
THEN (((COALESCE((r.data->r.type->$37->$38)::text::float, $39)+$40) * $41) * COALESCE(ac2.ct_rating, $42)) * ac2.ct_factor
ELSE COALESCE((r.data->r.type->$43->$44)::text::float, $45)
END AS ac_ct_2,
-- ========== AC CT 3 ==========
CASE
WHEN ac3.ct_manufacturer ILIKE $46
THEN (((COALESCE((r.data->r.type->$47->$48)::text::float, $49)+$50) / $51) * $52) * ac3.ct_factor
WHEN ac3.ct_manufacturer ILIKE $53
THEN (((COALESCE((r.data->r.type->$54->$55)::text::float, $56)+$57) * $58) * COALESCE(ac3.ct_rating, $59)) * ac3.ct_factor
ELSE COALESCE((r.data->r.type->$60->$61)::text::float, $62)
END AS ac_ct_3,
-- ========== AC CT 4 ==========
CASE
WHEN ac4.ct_manufacturer ILIKE $63
THEN (((COALESCE((r.data->r.type->$64->$65)::text::float, $66)+$67) / $68) * $69) * ac4.ct_factor
WHEN ac4.ct_manufacturer ILIKE $70
THEN (((COALESCE((r.data->r.type->$71->$72)::text::float, $73)+$74) * $75) * COALESCE(ac4.ct_rating, $76)) * ac4.ct_factor
ELSE COALESCE((r.data->r.type->$77->$78)::text::float, $79)
END AS ac_ct_4,
-- ========== AC CT 5 ==========
CASE
WHEN ac5.ct_manufacturer ILIKE $80
THEN (((COALESCE((r.data->r.type->$81->$82)::text::float, $83)+$84) / $85) * $86) * ac5.ct_factor
WHEN ac5.ct_manufacturer ILIKE $87
THEN (((COALESCE((r.data->r.type->$88->$89)::text::float, $90)+$91) * $92) * COALESCE(ac5.ct_rating, $93)) * ac5.ct_factor
ELSE COALESCE((r.data->r.type->$94->$95)::text::float, $96)
END AS ac_ct_5,
-- ========== AC CT 6 ==========
CASE
WHEN ac6.ct_manufacturer ILIKE $97
THEN (((COALESCE((r.data->r.type->$98->$99)::text::float, $100)+$101) / $102) * $103) * ac6.ct_factor
WHEN ac6.ct_manufacturer ILIKE $104
THEN (((COALESCE((r.data->r.type->$105->$106)::text::float, $107)+$108) * $109) * COALESCE(ac6.ct_rating, $110)) * ac6.ct_factor
ELSE COALESCE((r.data->r.type->$111->$112)::text::float, $113)
END AS ac_ct_6
FROM ranked r
JOIN tenant t
ON t.dsid = r.snmp_dsid
-- Join CT ratings by tenant_port
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $114
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $115
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $116
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $117
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $118
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $119
WHERE r.rn = $120
|
|
1 min
< 0.1%
|
83 ms
|
722
sunrise_user
|
SELECT
dh.start_hour_time,
SUM(dh.ac_kwh_ct) AS kwh
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $1 AND $2
AND s.name = $3
AND pc.title = $4
-- AND a.host_name = 'fmmBRN001'
GROUP BY dh.start_hour_time
|
|
1 min
< 0.1%
|
1,169 ms
|
50
sunrise_user
|
SELECT
time_bucket('1 hour', d.time) AS time,
AVG(
CASE
WHEN v.val ~ $1
THEN
v.val::float *
CASE
WHEN a.host_name = $2
AND v.key IN ($3 /*, ... */)
THEN $4
ELSE COALESCE(pndp.normalization_function, $5)
END
ELSE $6
END
) AS value,
a.host_name || $7 || v.key AS title_1
FROM data_json_ts d
JOIN assets a
ON a.id = d.asset_id
CROSS JOIN LATERAL (
SELECT
key,
jsonb_extract_path_text(d.data, key) AS val
FROM UNNEST(ARRAY[$8 /*, ... */]) AS key
WHERE d.data ? key
) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = d.part_number_id
AND pndp.data_type IN ($9 /*, ... */)
WHERE d.ip IS NOT NULL
AND d.ip IN ($10)
AND d.time BETWEEN $11 AND $12
GROUP BY
time_bucket('1 hour', d.time),
a.host_name,
v.key
ORDER BY time, title_1
|
|
1 min
< 0.1%
|
3,199 ms
|
17
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.data,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
sites.name AS site_name,
part_numbers.name AS part_number_name
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.time >= CURRENT_TIMESTAMP - INTERVAL $5
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
lp.site_name,
lp.part_number_name,
keys.key,
jsonb_extract_path_text(lp.data, keys.key) AS raw_value,
COALESCE(pndp.normalization_function, $6) AS normalization_function,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $7
AND jsonb_extract_path_text(lp.data, keys.key)::float NOT IN ($8 /*, ... */)
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $9)
ELSE $10
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type IN ($11 /*, ... */)
WHERE jsonb_typeof(lp.data) = $12
AND keys.key IN ($13 /*, ... */)
),
power_calculations AS (
SELECT
ip,
time,
status,
host_name,
site_name,
part_number_name,
MAX(CASE WHEN key = $14 THEN normalized_value END) AS load_current,
MAX(CASE WHEN key = $15 THEN normalized_value END) AS system_voltage,
MAX(CASE WHEN key = $16 THEN normalized_value END) AS nominal_voltage,
MAX(CASE WHEN key = $17 THEN normalized_value END) AS input_voltage,
MAX(CASE WHEN key = $18 THEN normalized_value END) AS total_rect_current,
COALESCE(
MAX(CASE WHEN key = $19 THEN normalized_value END),
SUM(CASE WHEN key IN ($20 /*, ... */) THEN normalized_value END)
) AS effective_rect_current,
MAX(CASE WHEN key = $21 THEN normalization_function END) AS norm_load_current,
MAX(CASE WHEN key = $22 THEN normalization_function END) AS norm_system_voltage,
MAX(CASE WHEN key = $23 THEN normalization_function END) AS norm_nominal_voltage,
MAX(CASE WHEN key = $24 THEN normalization_function END) AS norm_input_voltage,
MAX(CASE WHEN key = $25 THEN normalization_function END) AS norm_total_rect_current
FROM normalized_values
GROUP BY ip, time, status, host_name, site_name, part_number_name
),
efficiency_calculations AS (
SELECT
ip,
time,
status,
host_name,
site_name,
part_number_name,
load_current,
COALESCE(
CASE WHEN system_voltage > $26 THEN system_voltage END,
CASE WHEN nominal_voltage > $27 THEN nominal_voltage END
) AS effective_voltage,
CASE WHEN input_voltage > $28 THEN input_voltage END AS effective_input_voltage,
effective_rect_current,
norm_load_current,
norm_system_voltage,
norm_nominal_voltage,
norm_input_voltage,
norm_total_rect_current,
CASE
WHEN load_current > $29
AND effective_rect_current > $30
AND COALESCE(
CASE WHEN system_voltage > $31 THEN system_voltage END,
CASE WHEN nominal_voltage > $32 THEN nominal_voltage END
) IS NOT NULL
AND input_voltage > $33
THEN load_current * COALESCE(
CASE WHEN system_voltage > $34 THEN system_voltage END,
CASE WHEN nominal_voltage > $35 THEN nominal_voltage END
)
ELSE $36
END AS output_power,
CASE
WHEN effective_rect_current > $37
AND input_voltage > $38
THEN input_voltage * effective_rect_current
ELSE $39
END AS input_power
FROM power_calculations
WHERE input_voltage > $40
AND (system_voltage > $41 OR nominal_voltage > $42)
)
SELECT
ip,
time,
host_name,
site_name,
part_number_name,
status,
load_current,
effective_voltage AS system_voltage,
effective_input_voltage AS input_voltage,
effective_rect_current AS total_rectifier_current,
output_power,
input_power,
CASE
WHEN input_power > $43 AND output_power <= input_power
THEN (output_power / input_power) * $44
ELSE $45
END AS efficiency
FROM efficiency_calculations
WHERE output_power IS NOT NULL AND input_power IS NOT NULL
ORDER BY part_number_name, site_name, host_name, time DESC
|
|
1 min
< 0.1%
|
345 ms
|
156
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
data_json.asset_id,
data_json.part_number_id,
data_json.data,
CASE
WHEN data_json.time >= CURRENT_TIMESTAMP - INTERVAL $1 THEN $2
ELSE $3
END AS status,
assets.host_name,
sites.name AS site_name
FROM data_json_202511 data_json
JOIN assets ON data_json.asset_id = assets.id
JOIN sites ON assets.site_id = sites.id
JOIN part_numbers ON assets.part_number_id = part_numbers.id
LEFT JOIN part_number_classifications subcat_class ON part_numbers.sub_category_id = subcat_class.id
WHERE data_json.ip IS NOT NULL
AND subcat_class.title = $4
AND data_json.time >= CURRENT_TIMESTAMP - INTERVAL $5
ORDER BY data_json.ip, data_json.time DESC
),
normalized_values AS (
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
lp.site_name,
keys.key,
jsonb_extract_path_text(lp.data, keys.key) AS raw_value,
COALESCE(pndp.normalization_function, $6) AS normalization_function,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $7
THEN jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $8)
ELSE $9
END AS normalized_value
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT key FROM jsonb_object_keys(lp.data) AS key) AS keys
LEFT JOIN part_number_data_points pndp ON keys.key = pndp.name
AND pndp.part_number_id = lp.part_number_id
AND pndp.data_type = $10
WHERE jsonb_typeof(lp.data) = $11
AND keys.key IN ($12 /*, ... */)
),
power_calculations AS (
SELECT
ip,
time,
status,
host_name,
site_name,
MAX(CASE WHEN key = $13 THEN normalized_value END) AS real_power,
MAX(CASE WHEN key = $14 THEN normalized_value END) AS apparent_power,
MAX(CASE WHEN key = $15 THEN normalization_function END) AS normalization_function_real_power,
MAX(CASE WHEN key = $16 THEN normalization_function END) AS normalization_function_apparent_power
FROM normalized_values
GROUP BY ip, time, status, host_name, site_name
)
SELECT
ip,
time,
host_name,
site_name,
status,
CASE
WHEN COALESCE(apparent_power, $17) > $18
THEN (COALESCE(real_power, $19) / COALESCE(apparent_power, $20)) * $21
ELSE $22
END AS efficiency
FROM power_calculations
WHERE real_power IS NOT NULL AND apparent_power IS NOT NULL
ORDER BY ip, time DESC
|
|
1 min
< 0.1%
|
82 ms
|
643
sunrise_user
|
SELECT
a.host_name,
pc.title,
SUM(dh.input_power)/$1 AS kwh,
(SUM(dh.input_power) / SUM(SUM(dh.input_power)) OVER ()) * $2 AS load_percentage
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $3 AND $4
AND s.name = $5
AND pc.title = $6
GROUP BY a.host_name, pc.title
ORDER BY load_percentage DESC
|
|
1 min
< 0.1%
|
77 ms
|
613
sunrise_user
|
WITH bounds AS (
SELECT
$1::timestamptz AS time_start,
$2::timestamptz AS time_end
),
base AS (
SELECT
dh.start_hour_time AS hour,
dh.asset_id,
dh.ac_kwh_ct,
dh.ac_kwh_grid,
dh.input_power
FROM data_hour dh
JOIN bounds b
ON dh.start_hour_time >= b.time_start
AND dh.start_hour_time <= b.time_end
WHERE dh.site_id = (SELECT id FROM sites WHERE name = $3)
),
grid_per_hour AS (
-- Grid counted once per hour (avoid duplication across assets)
SELECT
hour,
MAX(ac_kwh_grid) AS total_grid_kwh
FROM base
GROUP BY hour
),
energy_per_hour AS (
-- UPS + Rectifier per hour
SELECT
b.hour,
SUM(
CASE
WHEN pc.title = $4
THEN b.input_power / $5
ELSE $6
END
) AS ups_kwh,
SUM(
CASE
WHEN pc.title = $7
THEN b.ac_kwh_ct
ELSE $8
END
) AS rectifier_kwh
FROM base b
JOIN assets a ON a.id = b.asset_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
GROUP BY b.hour
)
SELECT
gph.hour AS time,
ROUND(
(
gph.total_grid_kwh
/ NULLIF(eph.rectifier_kwh + eph.ups_kwh, $9)
)::numeric,
$10
) AS pue
FROM grid_per_hour gph
JOIN energy_per_hour eph
ON eph.hour = gph.hour
ORDER BY gph.hour
|
|
1 min
< 0.1%
|
74 ms
|
624
sunrise_user
|
SELECT
dh.start_hour_time AS time,
SUM(dh.input_power)/$1 AS ups_kwh
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $2 AND $3
AND s.name = $4
-- AND a.host_name IN ('fngBRN701')
AND pc.title IN ($5)
GROUP BY dh.start_hour_time
|
|
1 min
< 0.1%
|
45,718 ms
|
1
as_ro_user
|
WITH device_map AS MATERIALIZED (
-- Pre-calculate and lock the assets in memory
SELECT id AS asset_id, host_name, ip
FROM public.assets
WHERE site_id = $1 AND ip IS NOT NULL AND ip != $2
),
-- ==========================================
-- 1. THE SNMP NORMALIZATION ENGINE
-- ==========================================
latest_packets AS MATERIALIZED (
-- CRITICAL FIX: Use the native 'time' column so TimescaleDB doesn't do a full table scan
SELECT DISTINCT ON (dj.asset_id)
dj.asset_id, dj.time, dj.part_number_id, dj.data
FROM public.data_json_ts dj
WHERE dj.time >= NOW() - INTERVAL $3
AND dj.asset_id IN (SELECT asset_id FROM device_map)
ORDER BY dj.asset_id, dj.time DESC
),
unrolled_metrics AS (
SELECT lp.*, keys.key AS metric_name, jsonb_extract_path_text(lp.data, keys.key) AS raw_value_text
FROM latest_packets lp
CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
),
snmp_metric_stack AS (
SELECT
dm.host_name,
date_trunc($4, um.time) AS sync_time,
um.metric_name AS metric,
(um.raw_value_text::float * pndp.normalization_function) AS value,
pndp.unit AS unit
FROM unrolled_metrics um
JOIN device_map dm ON um.asset_id = dm.asset_id
JOIN public.part_number_data_points pndp
ON um.part_number_id = pndp.part_number_id
AND um.metric_name = pndp.name
WHERE pndp.data_type IN ($5 /*, ... */)
),
-- ==========================================
-- 2. AN10 CT MATH VIA DATA_NORMALIZED
-- ==========================================
ct_mapping AS MATERIALIZED (
SELECT
t.dsid AS tenant_dsid,
ac.tenant_port,
a.host_name,
ac.ct_manufacturer,
ac.ct_rating,
ac.ct_factor,
ac.monitored_point
FROM public.ac_ct ac
JOIN public.tenant t ON t.id = ac.parent_tenant_id
JOIN public.assets a ON a.id = ac.asset_id
WHERE a.site_id = $6
),
an10_latest_raw AS MATERIALIZED (
-- Filter directly against the mapped DSIDs to drastically reduce scanned rows
SELECT DISTINCT ON (d.dsid)
d.unix_time, d.type, d.data, d.dsid
FROM public.data_normalized d
JOIN ct_mapping m ON d.dsid = m.tenant_dsid
WHERE d.unix_time >= EXTRACT($7 FROM (NOW() - INTERVAL $8))::bigint
AND d.acq_type = $9
ORDER BY d.dsid, d.unix_time DESC
),
an10_calculated_stack AS (
SELECT
m.host_name,
date_trunc($10, to_timestamp(r.unix_time)) AS sync_time,
COALESCE(m.monitored_point, $11 || m.tenant_port) AS metric,
CASE
WHEN m.ct_manufacturer ILIKE $12 THEN
(((COALESCE((r.data->r.type->$13->>(m.tenant_port + $14))::float, $15)+$16) / $17) * m.ct_factor)
WHEN m.ct_manufacturer ILIKE $18 THEN
(((COALESCE((r.data->r.type->$19->>(m.tenant_port + $20))::float, $21)+$22) * $23) * COALESCE(m.ct_rating, $24)) * m.ct_factor
ELSE COALESCE((r.data->r.type->$25->>(m.tenant_port + $26))::float, $27)
END AS value,
$28 AS unit
FROM an10_latest_raw r
JOIN ct_mapping m ON r.dsid = m.tenant_dsid
)
-- ==========================================
-- 3. MERGE THE TWO STACKS
-- ==========================================
SELECT * FROM snmp_metric_stack
UNION ALL
SELECT * FROM an10_calculated_stack
ORDER BY host_name ASC, metric ASC
|
|
1 min
< 0.1%
|
319 ms
|
142
sunrise_user
|
SELECT
a.host_name || $1 || pc.title || $2 AS title_1,
dh.start_hour_time AS time,
SUM(dh.ac_kwh_ct) AS kwh
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $3 AND $4
AND s.name = $5
AND pc.title IN ($6 /*, ... */)
-- AND a.host_name NOT IN ('fkbBRN006','fkdBRN001','fkdBRN003')
GROUP BY a.host_name, pc.title, dh.start_hour_time
|
|
1 min
< 0.1%
|
45,171 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS window_start
),
device_map AS (
SELECT id AS asset_id, host_name, ip FROM public.assets
WHERE site_id = $3 AND ip IS NOT NULL AND ip != $4
),
latest_packets AS (
SELECT DISTINCT ON (dj.asset_id) dj.asset_id, dj.time, dj.part_number_id, dj.data
FROM public.data_json_ts dj
WHERE dj.time >= NOW() - INTERVAL $5
AND dj.asset_id IN (SELECT asset_id FROM device_map)
ORDER BY dj.asset_id, dj.time DESC
),
unrolled_metrics AS (
SELECT lp.*, keys.key AS metric_name, jsonb_extract_path_text(lp.data, keys.key) AS raw_value_text
FROM latest_packets lp CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
)
SELECT dm.host_name, date_trunc($6, um.time) AS sync_time, um.metric_name AS metric,
(um.raw_value_text::float * pndp.normalization_function) AS value, pndp.unit AS unit
FROM unrolled_metrics um
JOIN device_map dm ON um.asset_id = dm.asset_id
JOIN public.part_number_data_points pndp ON um.part_number_id = pndp.part_number_id AND um.metric_name = pndp.name
WHERE pndp.data_type IN ($7 /*, ... */)
|
|
1 min
< 0.1%
|
43,820 ms
|
1
as_ro_user
|
WITH site_nodes AS MATERIALIZED (
SELECT cn.ip as inet_ip FROM public.corelink_node cn WHERE cn.site_id = $1
),
site_assets AS MATERIALIZED (
SELECT id, host_name, ip FROM public.assets WHERE site_id = $2
),
snmp_temp AS MATERIALIZED (
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $3) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $4 AND dj.time < $5
AND pndp.name ILIKE $6
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS MATERIALIZED (
SELECT
dn.type as reg_addr,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $7)::double precision / $8))::numeric, $9) as avg_val
FROM public.data_normalized dn
JOIN site_nodes sn ON dn.ip::inet = sn.inet_ip
WHERE dn.unix_time >= $10 AND dn.unix_time < $11
AND dn.acq_type = $12
GROUP BY dn.type
),
site_trh AS MATERIALIZED (
SELECT
asset_id,
register_address,
placement,
substring(register_address from $13)::int as trh_id
FROM public.trh_sensor
WHERE asset_id IN (SELECT id FROM site_assets)
OR register_address IN (SELECT reg_addr FROM an10_temp)
),
final_data AS MATERIALIZED (
-- Collect the main results exactly as they appear in the DB
SELECT
$14::text as site_name,
COALESCE(trh.trh_id, substring(ac.reg_addr from $15)::int) as trh_id,
sa.host_name::text as host_name,
trh.placement::text as description,
COALESCE(sc.readings, $16)::bigint as snmp_temp_readings,
COALESCE(ac.readings, $17)::bigint as an10_readings,
COALESCE(sc.avg_val, $18)::numeric as snmp_avg_temp,
COALESCE(ac.avg_val, $19)::numeric as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $20) > $21 AND COALESCE(ac.readings, $22) > $23 THEN $24
WHEN COALESCE(sc.readings, $25) > $26 THEN $27
WHEN COALESCE(ac.readings, $28) > $29 THEN $30
ELSE $31
END::text as assessment
FROM site_assets sa
FULL OUTER JOIN site_trh trh ON trh.asset_id = sa.id
FULL OUTER JOIN an10_temp ac ON ac.reg_addr = trh.register_address
LEFT JOIN snmp_temp sc ON sc.asset_id = sa.id
)
-- 1. Select the regular data (Assigned sort_order 1)
SELECT
$32 as sort_order,
site_name, trh_id, host_name, description,
snmp_temp_readings, an10_readings, snmp_avg_temp, an10_avg_temp, assessment
FROM final_data
UNION ALL
-- 2. Append the summary counts to the bottom (Assigned sort_order 2)
SELECT
$33 as sort_order,
$34::text as site_name,
$35::int as trh_id,
$36::text as host_name,
($37 || assessment || $38 || COUNT(*))::text as description,
$39::bigint as snmp_temp_readings,
$40::bigint as an10_readings,
$41::numeric as snmp_avg_temp,
$42::numeric as an10_avg_temp,
$43::text as assessment
FROM final_data
GROUP BY assessment
-- Sort logic: Uses the hidden sort_order to push summaries to the bottom safely
ORDER BY
sort_order ASC,
host_name ASC NULLS LAST,
trh_id ASC
|
|
1 min
< 0.1%
|
18 ms
|
2,240
sunrise_user
|
SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*pghero*/
|
|
1 min
< 0.1%
|
40,117 ms
|
1
as_ro_user
|
WITH site_nodes AS MATERIALIZED (
SELECT cn.ip as inet_ip FROM public.corelink_node cn WHERE cn.site_id = $1
),
site_assets AS MATERIALIZED (
-- Pulling notes, room, and zone for a complete hardware profile
SELECT id, host_name, notes, room, zone, ip FROM public.assets WHERE site_id = $2
),
snmp_temp AS MATERIALIZED (
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $3) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $4 AND dj.time < $5
AND pndp.name ILIKE $6
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS MATERIALIZED (
SELECT
dn.type as reg_addr,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $7)::double precision / $8))::numeric, $9) as avg_val
FROM public.data_normalized dn
JOIN site_nodes sn ON dn.ip::inet = sn.inet_ip
WHERE dn.unix_time >= $10 AND dn.unix_time < $11
AND dn.acq_type = $12
GROUP BY dn.type
),
site_trh AS MATERIALIZED (
SELECT
asset_id,
register_address,
placement,
substring(register_address from $13)::int as trh_id
FROM public.trh_sensor
WHERE asset_id IN (SELECT id FROM site_assets)
OR register_address IN (SELECT reg_addr FROM an10_temp)
),
final_data AS MATERIALIZED (
SELECT
$14::text as site_name,
COALESCE(trh.trh_id, substring(ac.reg_addr from $15)::int) as trh_id,
sa.host_name::text as host_name,
COALESCE(sa.notes, sa.room || $16 || sa.zone)::text as asset_info, -- Combines notes or Room/Zone
trh.placement::text as sensor_placement,
COALESCE(sc.readings, $17)::bigint as snmp_temp_readings,
COALESCE(ac.readings, $18)::bigint as an10_readings,
COALESCE(sc.avg_val, $19)::numeric as snmp_avg_temp,
COALESCE(ac.avg_val, $20)::numeric as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $21) > $22 AND COALESCE(ac.readings, $23) > $24 THEN $25
WHEN COALESCE(sc.readings, $26) > $27 THEN $28
WHEN COALESCE(ac.readings, $29) > $30 THEN $31
ELSE $32
END::text as assessment
FROM site_assets sa
FULL OUTER JOIN site_trh trh ON trh.asset_id = sa.id
FULL OUTER JOIN an10_temp ac ON ac.reg_addr = trh.register_address
LEFT JOIN snmp_temp sc ON sc.asset_id = sa.id
)
-- 1. Main Data
SELECT
$33 as sort_order,
site_name, trh_id, host_name, asset_info, sensor_placement,
snmp_temp_readings, an10_readings, snmp_avg_temp, an10_avg_temp, assessment
FROM final_data
UNION ALL
-- 2. Summary Totals
SELECT
$34 as sort_order,
$35::text as site_name,
$36::int as trh_id,
$37::text as host_name,
$38::text as asset_info,
($39 || assessment || $40 || COUNT(*))::text as sensor_placement,
$41::bigint as snmp_temp_readings,
$42::bigint as an10_readings,
$43::numeric as snmp_avg_temp,
$44::numeric as an10_avg_temp,
$45::text as assessment
FROM final_data
GROUP BY assessment
ORDER BY
sort_order ASC,
host_name ASC NULLS LAST,
trh_id ASC
|
|
1 min
< 0.1%
|
635 ms
|
62
sunrise_user
|
WITH latest AS (
SELECT
dh.start_hour_time,
-- Live efficiency %
(dh.ac_kwh_ct / $1) * $2 AS rectifier_efficiency,
-- Live load %
(dh.ac_kwh_ct / $3) * $4 AS load_percent
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
WHERE a.host_name = $5
AND a.site_id = $6
AND dh.start_hour_time = (
SELECT MAX(start_hour_time)
FROM data_hour
WHERE asset_id = dh.asset_id
)
)
SELECT
-- snap to nearest 5% bucket to match performance_curve
ROUND(load_percent / $7) * $8 AS output_power,
rectifier_efficiency AS "Live Rectifier Efficiency"
FROM latest
|
|
1 min
< 0.1%
|
63 ms
|
626
sunrise_user
|
WITH bounds AS (
SELECT
date_trunc($1, $2::timestamptz) AS day_start,
date_trunc($3, $4::timestamptz) + interval $5 AS day_end
),
base AS (
SELECT
date_trunc($6, dh.start_hour_time) AS day,
dh.start_hour_time,
dh.asset_id,
dh.ac_kwh_ct,
dh.ac_kwh_grid,
dh.input_power
FROM data_hour dh
JOIN bounds b
ON dh.start_hour_time >= b.day_start
AND dh.start_hour_time < b.day_end
WHERE dh.site_id = $7
),
-- Grid: counted once per hour, then summed per day
grid_per_day AS (
SELECT
day,
SUM(hour_grid_kwh) AS total_grid_kwh
FROM (
SELECT
day,
start_hour_time,
MAX(ac_kwh_grid) AS hour_grid_kwh
FROM base
GROUP BY day, start_hour_time
) g
GROUP BY day
),
-- UPS + Rectifier per day
energy_per_day AS (
SELECT
b.day,
SUM(
CASE
WHEN pc.title = $8
THEN b.input_power / $9
ELSE $10
END
) AS ups_kwh,
SUM(
CASE
WHEN pc.title = $11
THEN b.ac_kwh_ct
ELSE $12
END
) AS rectifier_kwh
FROM base b
JOIN assets a ON a.id = b.asset_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
GROUP BY b.day
)
SELECT
gd.day::date AS time,
ROUND(
(
gd.total_grid_kwh
/ NULLIF(ep.rectifier_kwh + ep.ups_kwh, $13)
)::numeric,
$14
) AS pue
FROM grid_per_day gd
JOIN energy_per_day ep
ON ep.day = gd.day
ORDER BY gd.day
|
|
1 min
< 0.1%
|
39,171 ms
|
1
as_ro_user
|
WITH site_nodes AS MATERIALIZED (
-- Safe IP Anchoring to prevent cross-site scanning and 502s
SELECT cn.ip
FROM public.corelink_node cn
WHERE cn.site_id = $1
),
site_assets AS MATERIALIZED (
-- Get all BRNX01 hostnames
SELECT id, host_name, ip FROM public.assets WHERE site_id = $2
),
site_trh AS MATERIALIZED (
-- PRE-LOAD METADATA: This ensures the TRH ID and Placement NEVER vanish,
-- even if the sensor goes offline for the 10-minute window.
SELECT
asset_id,
register_address,
placement,
substring(register_address from $3)::int as trh_id
FROM public.trh_sensor
WHERE asset_id IN (SELECT id FROM site_assets)
),
snmp_temp AS MATERIALIZED (
-- Hardware Telemetry
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $4) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $5 AND dj.time < $6
AND pndp.name ILIKE $7
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS MATERIALIZED (
-- Sensor Telemetry (Safely joined via the gateway IPs)
SELECT
dn.type as reg_addr,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $8)::double precision / $9))::numeric, $10) as avg_val
FROM public.data_normalized dn
JOIN site_nodes sn ON dn.ip::inet = sn.ip
WHERE dn.unix_time >= $11 AND dn.unix_time < $12
AND dn.acq_type = $13
AND dn.type IN (SELECT register_address FROM site_trh)
GROUP BY dn.type
)
SELECT
$14 as site_name,
trh.trh_id,
sa.host_name,
COALESCE(trh.placement, $15) as description,
COALESCE(sc.readings, $16) as snmp_temp_readings,
COALESCE(ac.readings, $17) as an10_readings,
COALESCE(sc.avg_val, $18) as snmp_avg_temp,
COALESCE(ac.avg_val, $19) as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $20) > $21 AND COALESCE(ac.readings, $22) > $23 THEN $24
WHEN COALESCE(sc.readings, $25) > $26 THEN $27
WHEN COALESCE(ac.readings, $28) > $29 THEN $30
ELSE $31
END as assessment
FROM site_assets sa
-- Join the metadata first so the ID always shows up
LEFT JOIN site_trh trh ON trh.asset_id = sa.id
-- Then attach the actual readings
LEFT JOIN snmp_temp sc ON sc.asset_id = sa.id
LEFT JOIN an10_temp ac ON ac.reg_addr = trh.register_address
ORDER BY sa.host_name ASC, trh.trh_id ASC
|
|
1 min
< 0.1%
|
39,036 ms
|
1
as_ro_user
|
WITH site_assets AS (
-- Pre-filter to only the assets at this site
SELECT id, host_name, ip FROM public.assets WHERE site_id = $1
),
snmp_temp AS (
-- Optimized hardware telemetry fetch
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $2) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $3 AND dj.time < $4
AND pndp.name ILIKE $5
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS (
-- Optimized physical sensor fetch with Metadata (Placement/ID)
SELECT
trh.asset_id,
trh.placement, -- The Room/Aisle description
substring(trh.register_address from $6)::int as trh_id,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $7)::double precision / $8))::numeric, $9) as avg_val
FROM public.data_normalized dn
JOIN public.trh_sensor trh ON trh.register_address = dn.type
WHERE dn.unix_time >= $10 AND dn.unix_time < $11
AND dn.acq_type = $12
AND trh.asset_id IN (SELECT id FROM site_assets)
GROUP BY trh.asset_id, trh.register_address, trh.placement
)
SELECT
$13 as site_name,
ac.trh_id,
sa.host_name,
COALESCE(ac.placement, $14) as description,
COALESCE(sc.readings, $15) as snmp_temp_readings,
COALESCE(ac.readings, $16) as an10_readings,
COALESCE(sc.avg_val, $17) as snmp_avg_temp,
COALESCE(ac.avg_val, $18) as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $19) > $20 AND COALESCE(ac.readings, $21) > $22 THEN $23
WHEN COALESCE(sc.readings, $24) > $25 THEN $26
WHEN COALESCE(ac.readings, $27) > $28 THEN $29
ELSE $30
END as assessment
FROM site_assets sa
LEFT JOIN snmp_temp sc ON sa.id = sc.asset_id
LEFT JOIN an10_temp ac ON sa.id = ac.asset_id
ORDER BY sa.host_name ASC, ac.trh_id ASC
|
|
1 min
< 0.1%
|
39,035 ms
|
1
as_ro_user
|
WITH site_assets AS (
-- Pre-filter assets to a small list
SELECT id, host_name, ip FROM public.assets WHERE site_id = $1
),
snmp_temp AS (
-- Direct index scan on time and asset_id
SELECT
dj.asset_id,
COUNT(*) as readings,
ROUND(AVG((jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function))::numeric, $2) as avg_val
FROM public.data_json_ts dj
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $3 AND dj.time < $4
AND pndp.name ILIKE $5
AND dj.asset_id IN (SELECT id FROM site_assets)
GROUP BY dj.asset_id
),
an10_temp AS (
-- Optimized: Removed corelink_node join, using site_assets for filtering
SELECT
trh.asset_id,
substring(trh.register_address from $6)::int as trh_id,
COUNT(dn.unix_time) as readings,
ROUND(AVG(((dn.data -> dn.type ->> $7)::double precision / $8))::numeric, $9) as avg_val
FROM public.data_normalized dn
JOIN public.trh_sensor trh ON trh.register_address = dn.type
WHERE dn.unix_time >= $10 AND dn.unix_time < $11
AND dn.acq_type = $12
AND trh.asset_id IN (SELECT id FROM site_assets) -- Filters by asset instead of site IP
GROUP BY trh.asset_id, trh.register_address
)
SELECT
$13 as site_name,
ac.trh_id,
sa.host_name,
COALESCE(sc.readings, $14) as snmp_temp_readings,
COALESCE(ac.readings, $15) as an10_readings,
COALESCE(sc.avg_val, $16) as snmp_avg_temp,
COALESCE(ac.avg_val, $17) as an10_avg_temp,
CASE
WHEN COALESCE(sc.readings, $18) > $19 AND COALESCE(ac.readings, $20) > $21 THEN $22
WHEN COALESCE(sc.readings, $23) > $24 THEN $25
WHEN COALESCE(ac.readings, $26) > $27 THEN $28
ELSE $29
END as assessment
FROM site_assets sa
LEFT JOIN snmp_temp sc ON sa.id = sc.asset_id
LEFT JOIN an10_temp ac ON sa.id = ac.asset_id
ORDER BY sa.host_name ASC
|
|
1 min
< 0.1%
|
4,817 ms
|
8
sunrise_user
|
SELECT
date_trunc($1, to_timestamp(unix_time)) AS time,
(d.data->d.type->>$2)::float / $3 AS value,
COALESCE(a.host_name, $4) || $5 || COALESCE(trh.placement, d.type) || $6 AS metric
FROM data_snmp d
LEFT JOIN corelink_node cn ON d.ip::inet = cn.ip
LEFT JOIN sites s ON s.id = cn.site_id
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE d.type ILIKE $7 AND d.type NOT ILIKE $8
AND s.name = $9
AND d.time >= $10 AND d.time < $11
UNION ALL
SELECT
date_trunc($12, to_timestamp(unix_time)) AS time,
(d.data->d.type->>$13)::float / $14 AS value,
COALESCE(a.host_name, $15) || $16 || COALESCE(trh.placement, d.type) || $17 AS metric
FROM data_snmp d
LEFT JOIN corelink_node cn ON d.ip::inet = cn.ip
LEFT JOIN sites s ON s.id = cn.site_id
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE d.type ILIKE $18 AND d.type NOT ILIKE $19
AND s.name = $20
AND d.time >= $21 AND d.time < $22
|
|
1 min
< 0.1%
|
38,391 ms
|
1
as_ro_user
|
WITH target_assets AS MATERIALIZED (
-- 1. Isolate the specific Zurich assets to keep the query ultra-fast
SELECT id, host_name
FROM public.assets
WHERE site_id = $1 AND host_name IN ($2 /*, ... */)
),
raw_telemetry AS MATERIALIZED (
-- 2. Extract February data ONLY for the problematic streams
SELECT
dj.time,
ta.host_name,
pndp.name as stream_name,
(jsonb_extract_path_text(dj.data, pndp.name)::float * pndp.normalization_function)::numeric as val
FROM public.data_json_ts dj
JOIN target_assets ta ON dj.asset_id = ta.id
JOIN public.part_number_data_points pndp ON dj.part_number_id = pndp.part_number_id
WHERE dj.time >= $3 AND dj.time < $4
AND pndp.name IN ($5 /*, ... */)
),
calc_median AS MATERIALIZED (
-- 3. THE BASELINE: Find the exact middle value (Median) for the month
SELECT
host_name,
stream_name,
PERCENTILE_CONT($6) WITHIN GROUP (ORDER BY val) as median_val
FROM raw_telemetry
GROUP BY host_name, stream_name
),
calc_mad AS MATERIALIZED (
-- 4. THE TOLERANCE: Calculate the Median Absolute Deviation (MAD)
SELECT
r.host_name,
r.stream_name,
c.median_val,
PERCENTILE_CONT($7) WITHIN GROUP (ORDER BY ABS(r.val - c.median_val)) as mad_val
FROM raw_telemetry r
JOIN calc_median c ON r.host_name = c.host_name AND r.stream_name = c.stream_name
GROUP BY r.host_name, r.stream_name, c.median_val
)
-- 5. THE TRAP: Flag only the points that violate the allowed tolerance
SELECT
r.time,
r.host_name,
r.stream_name,
r.val as rogue_value,
m.median_val as safe_baseline,
m.mad_val as normal_fluctuation,
ABS(r.val - m.median_val) as deviation_amount,
$8 as status
FROM raw_telemetry r
JOIN calc_mad m ON r.host_name = m.host_name AND r.stream_name = m.stream_name
WHERE
-- The threshold: We flag anything that spikes more than 5x the normal noise.
-- We include a fallback (50) in case the MAD is perfectly 0 (no noise at all).
ABS(r.val - m.median_val) > CASE WHEN m.mad_val = $9 THEN $10 ELSE ($11 * m.mad_val) END
ORDER BY
r.host_name ASC,
r.time ASC
|
|
1 min
< 0.1%
|
0 ms
|
238,447
sunrise_user
|
update "data_hour" set "ac_kwh_grid" = $1, "ac_kwh_ct" = $2, "dc_kwh" = $3, "temperature_top" = $4, "temperature_bottom" = $5, "temperature_delta" = $6, "humidity_top" = $7, "humidity_bottom" = $8, "humidity_delta" = $9, "dc_load_current" = $10, "ac_load_current_ct" = $11, "ac_voltage_grid" = $12, "ac_voltage_rectifier" = $13, "dc_voltage_rectifier" = $14, "input_power" = $15, "output_power" = $16, "efficiency" = $17, "rectifier_ac_apparent_power" = $18, "asset_outdoor_temperature" = $19, "asset_glykol_temperature" = $20, "voltage_l1" = $21, "voltage_l2" = $22, "voltage_l3" = $23, "current_l1" = $24, "current_l2" = $25, "current_l3" = $26 where "site_id" = $27 and "asset_id" = $28 and "start_hour_time" = $29
|
|
1 min
< 0.1%
|
315 ms
|
118
sunrise_user
|
SELECT DISTINCT ON (d.rtu_id, d.type)
d.time,
d.type,
d.ip,
(d.data -> d.type ->> $2)::float / $3 AS temperature,
(d.data -> d.type ->> $4)::float / $5 AS humidity,
trh.placement AS trh_location,
a.host_name AS asset_host_name,
c2.title AS sub_category_title,
c3.title AS category_title,
pn.name AS part_number_name
FROM data_normalized d
LEFT JOIN corelink_node cn
ON d.ip::inet = cn.ip -- ❗ no cast
LEFT JOIN sites s
ON s.id = cn.site_id
LEFT JOIN trh_sensor trh
ON trh.register_address = d.type
LEFT JOIN assets a
ON a.id = trh.asset_id
LEFT JOIN part_numbers pn
ON a.part_number_id = pn.id
LEFT JOIN part_number_classifications c2
ON c2.id = pn.sub_category_id
LEFT JOIN part_number_classifications c3
ON c3.id = pn.category_id
WHERE d.acq_type = $6
AND s.name = $1
AND d.unix_time >= EXTRACT($7 FROM (now() - INTERVAL $8))::bigint
ORDER BY
d.rtu_id,
d.type,
d.unix_time DESC,
d.data_seq DESC
|
|
1 min
< 0.1%
|
6,052 ms
|
6
sunrise_user
|
SELECT
date_trunc($1, to_timestamp(unix_time)) AS time,
(d.data->d.type->>$2)::float / $3 AS value,
COALESCE(a.host_name, $4) || $5 || COALESCE(trh.placement, d.type) || $6 AS metric
FROM data_snmp_202601 d
LEFT JOIN corelink_node cn ON d.ip::inet = cn.ip
LEFT JOIN sites s ON s.id = cn.site_id
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE d.type ILIKE $7 AND d.type NOT ILIKE $8
AND s.name = $9
AND d.time >= $10 AND d.time < $11
UNION ALL
SELECT
date_trunc($12, to_timestamp(unix_time)) AS time,
(d.data->d.type->>$13)::float / $14 AS value,
COALESCE(a.host_name, $15) || $16 || COALESCE(trh.placement, d.type) || $17 AS metric
FROM data_snmp_202601 d
LEFT JOIN corelink_node cn ON d.ip::inet = cn.ip
LEFT JOIN sites s ON s.id = cn.site_id
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE d.type ILIKE $18 AND d.type NOT ILIKE $19
AND s.name = $20
AND d.time >= $21 AND d.time < $22
|
|
1 min
< 0.1%
|
35,379 ms
|
1
sunrise_user
|
SELECT *
FROM "data_json_ts"
WHERE (data->type->$1)::integer = $2
|
|
1 min
< 0.1%
|
347 ms
|
100
sunrise_user
|
WITH bounds AS (
SELECT
date_trunc($1, $2::timestamptz) AS day_start,
date_trunc($3, $4::timestamptz) + interval $5 AS day_end
),
base AS (
SELECT
dh.start_hour_time,
date_trunc($6, dh.start_hour_time) AS day,
dh.asset_id,
dh.ac_kwh_ct,
dh.ac_kwh_grid,
dh.input_power
FROM data_hour dh
JOIN bounds b
ON dh.start_hour_time >= b.day_start
AND dh.start_hour_time < b.day_end
WHERE dh.site_id = (SELECT id FROM sites WHERE name = $7)
),
energy_by_type AS (
SELECT
b.day,
pc.title,
CASE
-- WHEN pc.title = 'Main Smartmeter' THEN SUM(b.ac_kwh_grid)
WHEN pc.title = $8 THEN SUM(b.input_power) / $9
ELSE SUM(b.ac_kwh_ct)
END AS kwh
FROM base b
JOIN assets a ON a.id = b.asset_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
-- ⛔ NO ac_ct JOIN HERE
WHERE pc.title IN ($10 /*, ... */)
GROUP BY
b.day,
pc.title
)
SELECT
e.day::date AS day,
SUM(CASE WHEN e.title = $11 THEN e.kwh ELSE $12 END) AS ahu_kwh,
SUM(CASE WHEN e.title = $13 THEN e.kwh ELSE $14 END) AS drycooler_kwh,
SUM(CASE WHEN e.title = $15 THEN e.kwh ELSE $16 END) AS ups_kwh,
SUM(CASE WHEN e.title = $17 THEN e.kwh ELSE $18 END) AS grid_kwh,
SUM(CASE WHEN e.title = $19 THEN e.kwh ELSE $20 END) AS rectifier_kwh
FROM energy_by_type e
GROUP BY e.day
ORDER BY e.day
|
|
1 min
< 0.1%
|
3 ms
|
11,280
sunrise_user
|
SELECT
d.unix_time AS time,
d.type,
d.data,
trh.asset_id,
trh.placement,
a.host_name
FROM data_normalized d
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
LEFT JOIN assets a ON a.id = trh.asset_id
WHERE d.rtu_id = $1
AND d.acq_type = $4
AND d.unix_time BETWEEN $2 AND $3
-- AND trh.asset_id IS NULL AND trh.placement IS NOT NULL
ORDER BY d.unix_time ASC
|
|
1 min
< 0.1%
|
79 ms
|
420
sunrise_user
|
SELECT
a.host_name,
pn.name,
pc.title,
SUM(dh.ac_kwh_ct) AS kwh,
(SUM(dh.ac_kwh_ct) / SUM(SUM(dh.ac_kwh_ct)) OVER ()) * $1 AS load_percentage
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $2 AND $3
AND s.name = $4
AND EXISTS (
SELECT $5
FROM ac_ct ac
JOIN tenant t ON t.id = ac.parent_tenant_id
WHERE ac.asset_id = a.id
)
GROUP BY a.host_name, pn.name, pc.title
ORDER BY kwh DESC
|
|
1 min
< 0.1%
|
31,323 ms
|
1
as_ro_user
|
WITH time_window AS (
-- 1. Control the scope to prevent database crashes
SELECT
EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS snmp_unix_start,
NOW() - INTERVAL $3 AS an10_timestamp_start
),
device_map AS (
-- 2. Map standard IPs to AN10 RTU_IDs for Site 4
SELECT id AS asset_id, host_name, ip, REPLACE(ip, $4, $5)::bigint AS rtu_id
FROM public.assets
WHERE site_id = $6 AND ip IS NOT NULL AND ip != $7
),
-- ==========================================
-- 3. THE SNMP NORMALIZATION ENGINE
-- ==========================================
latest_packets AS (
SELECT DISTINCT ON (dj.ip)
dj.ip, dj.time, dj.time_unix, dj.asset_id, dj.part_number_id, dj.data
FROM data_json_ts dj
JOIN device_map dm ON dj.asset_id = dm.asset_id
WHERE dj.time_unix >= (SELECT snmp_unix_start FROM time_window)
ORDER BY dj.ip, dj.time_unix DESC
),
unrolled_metrics AS (
SELECT lp.*, keys.key AS metric_name, jsonb_extract_path_text(lp.data, keys.key) AS raw_value_text
FROM latest_packets lp
CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
),
snmp_normalized AS (
SELECT
dm.host_name,
date_trunc($8, um.time) AS sync_time,
um.metric_name AS snmp_metric,
(um.raw_value_text::float * pndp.normalization_function) AS snmp_value,
pndp.unit AS snmp_unit
FROM unrolled_metrics um
JOIN device_map dm ON um.asset_id = dm.asset_id
INNER JOIN part_number_data_points pndp
ON um.part_number_id = pndp.part_number_id AND um.metric_name = pndp.name
WHERE pndp.data_type IN ($9 /*, ... */)
),
-- ==========================================
-- 4. AN10 TENANT AC MATH (RAW PAYLOAD)
-- ==========================================
an10_tenant_raw AS (
SELECT
d.time,
ac1.asset_id AS ac1_asset_id,
CASE WHEN ac1.ct_manufacturer ILIKE $10 THEN (((COALESCE((d.data->d.type->$11->$12)::text::float, $13)+$14) / $15) * $16) * ac1.ct_factor
WHEN ac1.ct_manufacturer ILIKE $17 THEN (((COALESCE((d.data->d.type->$18->$19)::text::float, $20)+$21) * $22) * COALESCE(ac1.ct_rating, $23)) * ac1.ct_factor
ELSE COALESCE((d.data->d.type->$24->$25)::text::float, $26) END AS ac_ct_1,
ac2.asset_id AS ac2_asset_id,
CASE WHEN ac2.ct_manufacturer ILIKE $27 THEN (((COALESCE((d.data->d.type->$28->$29)::text::float, $30)+$31) / $32) * $33) * ac2.ct_factor
WHEN ac2.ct_manufacturer ILIKE $34 THEN (((COALESCE((d.data->d.type->$35->$36)::text::float, $37)+$38) * $39) * COALESCE(ac2.ct_rating, $40)) * ac2.ct_factor
ELSE COALESCE((d.data->d.type->$41->$42)::text::float, $43) END AS ac_ct_2,
ac3.asset_id AS ac3_asset_id,
CASE WHEN ac3.ct_manufacturer ILIKE $44 THEN (((COALESCE((d.data->d.type->$45->$46)::text::float, $47)+$48) / $49) * $50) * ac3.ct_factor
WHEN ac3.ct_manufacturer ILIKE $51 THEN (((COALESCE((d.data->d.type->$52->$53)::text::float, $54)+$55) * $56) * COALESCE(ac3.ct_rating, $57)) * ac3.ct_factor
ELSE COALESCE((d.data->d.type->$58->$59)::text::float, $60) END AS ac_ct_3,
ac4.asset_id AS ac4_asset_id,
CASE WHEN ac4.ct_manufacturer ILIKE $61 THEN (((COALESCE((d.data->d.type->$62->$63)::text::float, $64)+$65) / $66) * $67) * ac4.ct_factor
WHEN ac4.ct_manufacturer ILIKE $68 THEN (((COALESCE((d.data->d.type->$69->$70)::text::float, $71)+$72) * $73) * COALESCE(ac4.ct_rating, $74)) * ac4.ct_factor
ELSE COALESCE((d.data->d.type->$75->$76)::text::float, $77) END AS ac_ct_4,
ac5.asset_id AS ac5_asset_id,
CASE WHEN ac5.ct_manufacturer ILIKE $78 THEN (((COALESCE((d.data->d.type->$79->$80)::text::float, $81)+$82) / $83) * $84) * ac5.ct_factor
WHEN ac5.ct_manufacturer ILIKE $85 THEN (((COALESCE((d.data->d.type->$86->$87)::text::float, $88)+$89) * $90) * COALESCE(ac5.ct_rating, $91)) * ac5.ct_factor
ELSE COALESCE((d.data->d.type->$92->$93)::text::float, $94) END AS ac_ct_5,
ac6.asset_id AS ac6_asset_id,
CASE WHEN ac6.ct_manufacturer ILIKE $95 THEN (((COALESCE((d.data->d.type->$96->$97)::text::float, $98)+$99) / $100) * $101) * ac6.ct_factor
WHEN ac6.ct_manufacturer ILIKE $102 THEN (((COALESCE((d.data->d.type->$103->$104)::text::float, $105)+$106) * $107) * COALESCE(ac6.ct_rating, $108)) * ac6.ct_factor
ELSE COALESCE((d.data->d.type->$109->$110)::text::float, $111) END AS ac_ct_6
FROM data_snmp d
CROSS JOIN LATERAL (SELECT trim(both $112 from (d.data->d.type->$113->$114)::text) AS dsid) dn
JOIN tenant t ON t.dsid = dn.dsid
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $115
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $116
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $117
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $118
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $119
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $120
WHERE d.time >= (SELECT an10_timestamp_start FROM time_window)
AND d.type ~ $121
AND d.rtu_id IN (SELECT rtu_id FROM device_map) -- Filters safely!
),
-- ==========================================
-- 5. UNPIVOTING TO INDIVIDUAL ASSETS
-- ==========================================
an10_unpivoted_amps AS (
SELECT time, ac1_asset_id AS asset_id, ac_ct_1 AS physical_ac_amps FROM an10_tenant_raw WHERE ac1_asset_id IS NOT NULL
UNION ALL SELECT time, ac2_asset_id, ac_ct_2 FROM an10_tenant_raw WHERE ac2_asset_id IS NOT NULL
UNION ALL SELECT time, ac3_asset_id, ac_ct_3 FROM an10_tenant_raw WHERE ac3_asset_id IS NOT NULL
UNION ALL SELECT time, ac4_asset_id, ac_ct_4 FROM an10_tenant_raw WHERE ac4_asset_id IS NOT NULL
UNION ALL SELECT time, ac5_asset_id, ac_ct_5 FROM an10_tenant_raw WHERE ac5_asset_id IS NOT NULL
UNION ALL SELECT time, ac6_asset_id, ac_ct_6 FROM an10_tenant_raw WHERE ac6_asset_id IS NOT NULL
),
an10_grouped AS (
SELECT
a.host_name,
date_trunc('minute', u.time) AS sync_time,
SUM(u.physical_ac_amps) AS total_physical_ac_amps -- Sums up feeds if an asset uses 2 clamps!
FROM an10_unpivoted_amps u
JOIN public.assets a ON u.asset_id = a.id
GROUP BY a.host_name, date_trunc('minute', u.time)
)
-- ==========================================
-- 6. THE UNIFIED MERGE
-- ==========================================
SELECT
COALESCE(s.host_name, a.host_name) AS host_name,
COALESCE(s.sync_time, a.sync_time) AS last_update,
s.snmp_metric,
s.snmp_value,
s.snmp_unit,
a.total_physical_ac_amps
FROM snmp_normalized s
FULL OUTER JOIN an10_grouped a
ON s.host_name = a.host_name AND s.sync_time = a.sync_time
ORDER BY last_update DESC, host_name ASC, snmp_metric ASC
|
|
0 min
< 0.1%
|
2,951 ms
|
10
sunrise_user
|
SELECT type AS __text, type AS __value
FROM (
SELECT DISTINCT type
FROM data_snmp
WHERE rtu_id = $1 AND type ~ $2
) t
ORDER BY (regexp_replace(type, $3, $4))::int
|
|
0 min
< 0.1%
|
683 ms
|
42
sunrise_user
|
WITH latest AS (
SELECT
dh.start_hour_time,
-- Live efficiency %
(dh.ac_kwh_ct / $1) * $2 AS rectifier_efficiency,
-- Live load %
(dh.ac_kwh_ct / $3) * $4 AS load_percent
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
WHERE a.host_name = $5
AND a.site_id = (SELECT id FROM sites WHERE name = $6)
AND dh.start_hour_time = (
SELECT MAX(start_hour_time)
FROM data_hour
WHERE asset_id = dh.asset_id
)
)
SELECT
-- snap to nearest 5% bucket to match performance_curve
ROUND(load_percent / $7) * $8 AS output_power,
rectifier_efficiency AS "Live Rectifier Efficiency"
FROM latest
|
|
0 min
< 0.1%
|
28,375 ms
|
1
sunrise_user
|
SELECT
d.time,
d.time_unix,
a.host_name,
v.key AS metric,
-- Apply normalization function to raw values
CASE
WHEN v.value ~ $1
THEN v.value::float * COALESCE(pndp.normalization_function, $2)
ELSE $3
END AS normalized_value,
pndp.unit,
s.name AS site_name
FROM data_json_ts d
JOIN assets a ON a.id = d.asset_id
JOIN sites s ON a.site_id = s.id
-- This expands ALL keys within the data jsonb column
CROSS JOIN LATERAL jsonb_each_text(d.data) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = a.part_number_id
AND pndp.data_type IN ($4 /*, ... */)
WHERE s.name = $5 -- Filtered by site for your export script
AND d.time >= $6 AND d.time < $7
ORDER BY d.time ASC, a.host_name, v.key
|
|
0 min
< 0.1%
|
28,254 ms
|
1
as_ro_user
|
WITH time_window AS (
-- 1. Control the scope to prevent database crashes
SELECT
EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS snmp_unix_start,
NOW() - INTERVAL $3 AS an10_timestamp_start
),
-- ==========================================
-- 2. THE SNMP NORMALIZATION ENGINE
-- ==========================================
device_map AS (
SELECT id AS asset_id, host_name, ip
FROM public.assets
WHERE site_id = $4 AND ip IS NOT NULL AND ip != $5
),
latest_packets AS (
SELECT DISTINCT ON (dj.ip) dj.ip, dj.time, dj.time_unix, dj.asset_id, dj.part_number_id, dj.data
FROM data_json_ts dj
JOIN device_map dm ON dj.asset_id = dm.asset_id
WHERE dj.time_unix >= (SELECT snmp_unix_start FROM time_window)
ORDER BY dj.ip, dj.time_unix DESC
),
unrolled_metrics AS (
SELECT lp.*, keys.key AS metric_name, jsonb_extract_path_text(lp.data, keys.key) AS raw_value_text
FROM latest_packets lp
CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
),
snmp_normalized AS (
SELECT
dm.host_name,
date_trunc($6, um.time) AS sync_time,
um.metric_name AS snmp_metric,
(um.raw_value_text::float * pndp.normalization_function) AS snmp_value,
pndp.unit AS snmp_unit
FROM unrolled_metrics um
JOIN device_map dm ON um.asset_id = dm.asset_id
INNER JOIN part_number_data_points pndp
ON um.part_number_id = pndp.part_number_id AND um.metric_name = pndp.name
WHERE pndp.data_type IN ($7 /*, ... */)
),
-- ==========================================
-- 3. THE AN10 CT MATH (PHP TRANSLATION)
-- ==========================================
ct_mapping AS (
-- Replicating the exact logic from $ctCfgRows in your PHP
SELECT
t.dsid,
ac.tenant_port,
a.host_name,
ac.ct_manufacturer,
ac.ct_rating,
ac.ct_factor
FROM public.ac_ct ac
JOIN public.tenant t ON t.id = ac.parent_tenant_id
JOIN public.assets a ON a.id = ac.asset_id
WHERE a.site_id = $8
),
an10_raw AS (
-- Extracting the dsid straight from the payload
SELECT
time,
type,
data,
trim(both $9 from (data->type->$10->>$11)) AS payload_dsid
FROM public.data_snmp
WHERE time >= (SELECT an10_timestamp_start FROM time_window)
AND type ~ $12
),
an10_calculated AS (
-- Applying the PHP dynamic JSON array math
SELECT
date_trunc($13, r.time) AS sync_time,
m.host_name,
CASE
WHEN m.ct_manufacturer ILIKE $14 THEN
(((COALESCE((r.data->r.type->$15->>(m.tenant_port + $16))::float, $17)+$18) / $19) * m.ct_factor)
WHEN m.ct_manufacturer ILIKE $20 THEN
(((COALESCE((r.data->r.type->$21->>(m.tenant_port + $22))::float, $23)+$24) * $25) * COALESCE(m.ct_rating, $26)) * m.ct_factor
ELSE
COALESCE((r.data->r.type->$27->>(m.tenant_port + $28))::float, $29)
END AS physical_ac_amps
FROM an10_raw r
JOIN ct_mapping m ON r.payload_dsid = m.dsid
),
an10_grouped AS (
-- If an asset uses multiple clamps, sum them together
SELECT
host_name,
sync_time,
SUM(physical_ac_amps) AS total_physical_ac_amps
FROM an10_calculated
GROUP BY host_name, sync_time
)
-- ==========================================
-- 4. THE UNIFIED MERGE
-- ==========================================
SELECT
COALESCE(s.host_name, a.host_name) AS host_name,
COALESCE(s.sync_time, a.sync_time) AS last_update,
s.snmp_metric,
s.snmp_value,
s.snmp_unit,
a.total_physical_ac_amps
FROM snmp_normalized s
FULL OUTER JOIN an10_grouped a
ON s.host_name = a.host_name AND s.sync_time = a.sync_time
ORDER BY last_update DESC, host_name ASC, snmp_metric ASC
|
|
0 min
< 0.1%
|
28,143 ms
|
1
sunrise_user
|
SELECT
d.time,
a.host_name,
v.key AS metric,
CASE
WHEN v.value ~ $1
THEN v.value::float * COALESCE(pndp.normalization_function, $2)
ELSE $3
END AS normalized_value
FROM data_json_ts d
JOIN assets a ON a.id = d.asset_id
JOIN sites s ON a.site_id = s.id
CROSS JOIN LATERAL jsonb_each_text(d.data) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = a.part_number_id
AND pndp.data_type IN ($4 /*, ... */)
WHERE s.name = $5
AND d.time >= $6 AND d.time < $7
|
|
0 min
< 0.1%
|
46 ms
|
604
sunrise_user
|
SELECT
dh.start_hour_time AS time,
-- dh.ac_kwh_grid, dh.dc_kwh
SUM(dh.ac_kwh_grid) AS grid_kwh
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $1 AND $2
AND s.name = $3
--AND a.host_name IN ('fngBRN701')
AND pc.title IN ($4)
GROUP BY dh.start_hour_time
|
|
0 min
< 0.1%
|
259 ms
|
107
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (dj.ip)
dj.ip,
dj.time,
(EXTRACT($1 FROM now()) - dj.time_unix) + $2 AS time_ago_seconds,
dj.asset_id,
dj.part_number_id,
dj.data,
dj.type,
CASE
WHEN dj.time_unix >= EXTRACT($3 FROM (now() - INTERVAL $4))::bigint
THEN $5
ELSE $6
END AS status,
a.host_name,
s.name AS site_name,
pn.name AS part_number_name,
cat.title AS category_title,
sub.title AS subcategory_title
FROM data_json_ts dj
LEFT JOIN assets a
ON dj.asset_id = a.id
LEFT JOIN sites s
ON a.site_id = s.id
LEFT JOIN part_numbers pn
ON a.part_number_id = pn.id
LEFT JOIN part_number_classifications sub
ON pn.sub_category_id = sub.id
LEFT JOIN part_number_classifications cat
ON pn.category_id = cat.id
WHERE s.name = $7
-- 🔥 CRITICAL: cutoff for chunk pruning
AND sub.title IN ($8 /*, ... */)
AND dj.time_unix >= EXTRACT($9 FROM (now() - INTERVAL $10))::bigint
ORDER BY
dj.ip,
dj.time_unix DESC
)
SELECT
lp.ip,
lp.time,
lp.status,
lp.host_name,
lp.site_name,
lp.part_number_id,
lp.part_number_name,
lp.category_title,
lp.subcategory_title,
keys.key AS metric,
jsonb_extract_path_text(lp.data, keys.key) AS raw_value
FROM latest_packets lp
CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
ORDER BY
lp.ip,
lp.time DESC,
keys.key
|
|
0 min
< 0.1%
|
230 ms
|
118
sunrise_user
|
SELECT DISTINCT ON (d.rtu_id, d.type)
d.rtu_id,
d.ip,
d.type,
d.time,
d.data,
d.dsid AS snmp_dsid
FROM data_normalized d
WHERE d.acq_type = $2
AND d.dsid = ANY($1::text[])
AND d.unix_time >= EXTRACT($3 FROM (now() - INTERVAL $4))::bigint
ORDER BY
d.rtu_id,
d.type,
d.unix_time DESC,
d.data_seq DESC
|
|
0 min
< 0.1%
|
27,057 ms
|
1
as_ro_user
|
WITH time_window AS (
SELECT
EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS snmp_unix_start,
NOW() - INTERVAL $3 AS an10_timestamp_start
),
device_map AS (
SELECT id AS asset_id, host_name, ip
FROM public.assets
WHERE site_id = $4 AND ip IS NOT NULL AND ip != $5
),
-- 1. NORMALIZED SNMP
latest_packets AS (
SELECT DISTINCT ON (dj.ip) dj.ip, dj.time, dj.time_unix, dj.asset_id, dj.part_number_id, dj.data
FROM data_json_ts dj
JOIN device_map dm ON dj.asset_id = dm.asset_id
WHERE dj.time_unix >= (SELECT snmp_unix_start FROM time_window)
ORDER BY dj.ip, dj.time_unix DESC
),
unrolled_metrics AS (
SELECT lp.*, keys.key AS metric_name, jsonb_extract_path_text(lp.data, keys.key) AS raw_value_text
FROM latest_packets lp
CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
),
snmp_normalized AS (
SELECT
dm.host_name,
date_trunc($6, um.time) AS sync_time,
um.metric_name AS metric,
(um.raw_value_text::float * pndp.normalization_function) AS value,
pndp.unit AS unit
FROM unrolled_metrics um
JOIN device_map dm ON um.asset_id = dm.asset_id
INNER JOIN part_number_data_points pndp
ON um.part_number_id = pndp.part_number_id AND um.metric_name = pndp.name
WHERE pndp.data_type IN ($7 /*, ... */)
),
-- 2. AN10 CT MATH
ct_mapping AS (
SELECT t.dsid, ac.tenant_port, a.host_name, ac.ct_manufacturer, ac.ct_rating, ac.ct_factor
FROM public.ac_ct ac
JOIN public.tenant t ON t.id = ac.parent_tenant_id
JOIN public.assets a ON a.id = ac.asset_id
WHERE a.site_id = $8
),
an10_raw AS (
SELECT time, type, data, trim(both $9 from (data->type->$10->>$11)) AS payload_dsid
FROM public.data_snmp
WHERE time >= (SELECT an10_timestamp_start FROM time_window)
AND type ~ $12
),
an10_calculated AS (
SELECT
date_trunc($13, r.time) AS sync_time,
m.host_name,
CASE
WHEN m.ct_manufacturer ILIKE $14 THEN
(((COALESCE((r.data->r.type->$15->>(m.tenant_port + $16))::float, $17)+$18) / $19) * m.ct_factor)
WHEN m.ct_manufacturer ILIKE $20 THEN
(((COALESCE((r.data->r.type->$21->>(m.tenant_port + $22))::float, $23)+$24) * $25) * COALESCE(m.ct_rating, $26)) * m.ct_factor
ELSE COALESCE((r.data->r.type->$27->>(m.tenant_port + $28))::float, $29)
END AS physical_ac_amps
FROM an10_raw r
JOIN ct_mapping m ON r.payload_dsid = m.dsid
),
an10_formatted_as_metric AS (
SELECT
host_name,
sync_time,
$30 AS metric,
SUM(physical_ac_amps) AS value,
$31 AS unit
FROM an10_calculated
GROUP BY host_name, sync_time
)
-- 3. THE CLEAN STACK (UNION ALL)
SELECT * FROM snmp_normalized
UNION ALL
SELECT * FROM an10_formatted_as_metric
ORDER BY sync_time DESC, host_name ASC, metric ASC
|
|
0 min
< 0.1%
|
26,995 ms
|
1
sunrise_user
|
SELECT
d.time,
a.host_name,
v.key AS metric,
v.value AS raw_val,
CASE
WHEN v.value ~ $1
THEN v.value::float * COALESCE(pndp.normalization_function, $2)
ELSE $3
END AS normalized_value
FROM data_json_ts d
JOIN assets a ON a.id = d.asset_id
JOIN sites s ON a.site_id = s.id
CROSS JOIN LATERAL jsonb_each_text(d.data) v
LEFT JOIN part_number_data_points pndp
ON pndp.name = v.key
AND pndp.part_number_id = a.part_number_id
WHERE s.name = $4
AND d.time >= $5 AND d.time < $6
AND v.value IS NOT NULL
|
|
0 min
< 0.1%
|
26,514 ms
|
1
as_ro_user
|
WITH time_window AS (
-- 1. Control the scope to prevent database crashes
SELECT
EXTRACT($1 FROM (now() - INTERVAL $2))::bigint AS snmp_unix_start,
NOW() - INTERVAL $3 AS an10_timestamp_start
),
device_map AS (
-- 2. Map standard IPs to AN10 RTU_IDs
SELECT
id AS asset_id,
host_name,
ip,
REPLACE(ip, $4, $5)::bigint AS rtu_id
FROM public.assets
WHERE site_id = $6 AND ip IS NOT NULL AND ip != $7
),
-- ==========================================
-- 3. THE SNMP NORMALIZATION ENGINE
-- ==========================================
latest_packets AS (
SELECT DISTINCT ON (dj.ip)
dj.ip, dj.time, dj.time_unix, dj.asset_id, dj.part_number_id, dj.data
FROM data_json_ts dj
JOIN device_map dm ON dj.asset_id = dm.asset_id
WHERE dj.time_unix >= (SELECT snmp_unix_start FROM time_window)
ORDER BY dj.ip, dj.time_unix DESC
),
unrolled_metrics AS (
SELECT lp.*, keys.key AS metric_name, jsonb_extract_path_text(lp.data, keys.key) AS raw_value_text
FROM latest_packets lp
CROSS JOIN LATERAL jsonb_object_keys(lp.data) AS keys(key)
),
snmp_normalized AS (
SELECT
dm.host_name,
date_trunc($8, um.time) AS sync_time,
um.metric_name AS snmp_metric,
(um.raw_value_text::float * pndp.normalization_function) AS snmp_value,
pndp.unit AS snmp_unit
FROM unrolled_metrics um
JOIN device_map dm ON um.asset_id = dm.asset_id
INNER JOIN part_number_data_points pndp
ON um.part_number_id = pndp.part_number_id AND um.metric_name = pndp.name
WHERE pndp.data_type IN ($9 /*, ... */)
),
-- ==========================================
-- 4. THE AN10 PHYSICAL IoT SENSORS (TRH & AC)
-- ==========================================
an10_iot_data AS (
SELECT
dm.host_name,
date_trunc('minute', ds.time) AS sync_time,
-- Extracting Temperature (T1) and AC Voltage (P1) as examples
MAX(CASE WHEN ds.type = $10 THEN ((ds.data->$11->$12)::text::float)/$13 END) AS an10_room_temp,
MAX(CASE WHEN ds.type = $14 THEN ((ds.data->$15->$16)::text::float)/$17 END) AS an10_ac_voltage
FROM data_snmp ds
JOIN device_map dm ON ds.rtu_id = dm.rtu_id
WHERE ds.time >= (SELECT an10_timestamp_start FROM time_window)
AND ds.type IN ($18 /*, ... */)
GROUP BY dm.host_name, date_trunc('minute', ds.time)
)
-- ==========================================
-- 5. THE UNIFIED MERGE
-- ==========================================
SELECT
COALESCE(s.host_name, a.host_name) AS host_name,
COALESCE(s.sync_time, a.sync_time) AS last_update,
s.snmp_metric,
s.snmp_value,
s.snmp_unit,
a.an10_room_temp AS physical_an10_temp,
a.an10_ac_voltage AS physical_an10_voltage
FROM snmp_normalized s
FULL OUTER JOIN an10_iot_data a
ON s.host_name = a.host_name AND s.sync_time = a.sync_time
ORDER BY last_update DESC, host_name ASC, snmp_metric ASC
|
|
0 min
< 0.1%
|
12,257 ms
|
2
as_ro_user
|
WITH site_nodes AS MATERIALIZED (
SELECT cn.ip
FROM corelink_node cn
JOIN sites s ON s.id = cn.site_id
WHERE s.name = $1
),
site_rtu AS MATERIALIZED (
SELECT DISTINCT REPLACE(host(ip), $2, $3)::integer AS rtu_id
FROM site_nodes
),
trh_types AS MATERIALIZED (
SELECT DISTINCT ds.type
FROM data_snmp ds
WHERE ds.rtu_id IN (SELECT rtu_id FROM site_rtu)
AND ds.type ~ $4
),
latest AS (
SELECT
d.type,
max(d.unix_time) AS unix_time,
last(d.data, d.unix_time) AS data
FROM data_normalized d
JOIN site_nodes sn ON d.ip::inet = sn.ip
WHERE d.acq_type = $5
AND d.type IN (SELECT type FROM trh_types)
-- IMPORTANT: prune chunks (adjust window as needed)
AND d.unix_time >= extract($6 from now() - interval $7)::bigint
GROUP BY d.type
)
SELECT
l.unix_time * $8 AS time_ms,
COALESCE(a.host_name, $9) AS host_name,
COALESCE(NULLIF(trh.placement, $10), $11) AS placement,
l.type AS register_address,
CASE
WHEN (l.data -> l.type ->> $12) ~ $13
THEN ROUND(((l.data -> l.type ->> $14)::double precision / $15)::numeric, $16)
ELSE $17
END AS temperature_c,
CASE
WHEN (l.data -> l.type ->> $18) ~ $19
THEN ROUND(((l.data -> l.type ->> $20)::double precision / $21)::numeric, $22)
ELSE $23
END AS humidity_pct
FROM latest l
LEFT JOIN trh_sensor trh ON trh.register_address = l.type
LEFT JOIN assets a ON a.id = trh.asset_id
ORDER BY substring(l.type from $24)::int, host_name, placement
|
|
0 min
< 0.1%
|
0 ms
|
223,325
sunrise_user
|
SELECT part_number_id, name, normalization_function
FROM part_number_data_points
WHERE data_type IN ($1 /*, ... */)
AND part_number_id = ANY($2)
AND name IN ($3 /*, ... */)
|
|
0 min
< 0.1%
|
12,153 ms
|
2
sunrise_user
|
WITH ranked AS (
SELECT
d.*,
ROW_NUMBER() OVER (PARTITION BY d.rtu_id, d.type ORDER BY d.time DESC, d.data_seq DESC) AS rn,
trim(both $2 from (d.data->d.type->$3->$4)::text) AS snmp_dsid,
EXTRACT($5 FROM (now() - time)) AS time_ago_seconds,
EXTRACT($6 FROM (now() - time)) AS time_ago_status
FROM data_snmp d
WHERE d.type ILIKE $7
)
SELECT DISTINCT ON (r.rtu_id, r.type)
r.rtu_id,
r.ip,
r.type,
r.time,
r.time_ago_seconds,
r.time_ago_status,
r.snmp_dsid AS dsid,
COALESCE((r.data->r.type->$8->$9)::text::integer, $10) AS temperature,
-- TelemetryLink Module details (matched via tenant DSID)
tlm.asset_qr AS telemetry_asset_qr,
tlm.label AS telemetry_label,
-- Site via onboarding_transactions
s.id AS site_id,
s.name AS site_name,
-- ================= CT 1 =================
CASE WHEN ac1.ct_rating IS NOT NULL
THEN (((COALESCE((r.data->r.type->$11->$12)::text::float, $13) + $14) * $15) * ac1.ct_rating) * $16
ELSE (r.data->r.type->$17->$18)::text::float
END AS ac_ct_1,
ac1.ct_rating AS ct_rating_1,
ac1.monitored_point AS monitored_point_1,
ac1.ct_manufacturer AS ct_manufacturer_1,
a1.host_name AS asset_host_name_1,
cat1.title AS category_title_1,
sub1.title AS sub_category_title_1,
-- ================= CT 2 =================
CASE WHEN ac2.ct_rating IS NOT NULL
THEN (((COALESCE((r.data->r.type->$19->$20)::text::float, $21) + $22) * $23) * ac2.ct_rating) * $24
ELSE (r.data->r.type->$25->$26)::text::float
END AS ac_ct_2,
ac2.ct_rating AS ct_rating_2,
ac2.monitored_point AS monitored_point_2,
ac2.ct_manufacturer AS ct_manufacturer_2,
a2.host_name AS asset_host_name_2,
cat2.title AS category_title_2,
sub2.title AS sub_category_title_2,
-- ================= CT 3 =================
CASE WHEN ac3.ct_rating IS NOT NULL
THEN (((COALESCE((r.data->r.type->$27->$28)::text::float, $29) + $30) * $31) * ac3.ct_rating) * $32
ELSE (r.data->r.type->$33->$34)::text::float
END AS ac_ct_3,
ac3.ct_rating AS ct_rating_3,
ac3.monitored_point AS monitored_point_3,
ac3.ct_manufacturer AS ct_manufacturer_3,
a3.host_name AS asset_host_name_3,
cat3.title AS category_title_3,
sub3.title AS sub_category_title_3,
-- ================= CT 4 =================
CASE WHEN ac4.ct_rating IS NOT NULL
THEN ((COALESCE((r.data->r.type->$35->$36)::text::float, $37) + $38) * $39) * ac4.ct_rating
ELSE (r.data->r.type->$40->$41)::text::float
END AS ac_ct_4,
ac4.ct_rating AS ct_rating_4,
ac4.monitored_point AS monitored_point_4,
ac4.ct_manufacturer AS ct_manufacturer_4,
a4.host_name AS asset_host_name_4,
cat4.title AS category_title_4,
sub4.title AS sub_category_title_4,
-- ================= CT 5 =================
CASE WHEN ac5.ct_rating IS NOT NULL
THEN ((COALESCE((r.data->r.type->$42->$43)::text::float, $44) + $45) * $46) * ac5.ct_rating
ELSE (r.data->r.type->$47->$48)::text::float
END AS ac_ct_5,
ac5.ct_rating AS ct_rating_5,
ac5.monitored_point AS monitored_point_5,
ac5.ct_manufacturer AS ct_manufacturer_5,
a5.host_name AS asset_host_name_5,
cat5.title AS category_title_5,
sub5.title AS sub_category_title_5,
-- ================= CT 6 =================
CASE WHEN ac6.ct_rating IS NOT NULL
THEN ((COALESCE((r.data->r.type->$49->$50)::text::float, $51) + $52) * $53) * ac6.ct_rating
ELSE (r.data->r.type->$54->$55)::text::float
END AS ac_ct_6,
ac6.ct_rating AS ct_rating_6,
ac6.monitored_point AS monitored_point_6,
ac6.ct_manufacturer AS ct_manufacturer_6,
a6.host_name AS asset_host_name_6,
cat6.title AS category_title_6,
sub6.title AS sub_category_title_6
FROM ranked r
JOIN tenant t
ON t.dsid = r.snmp_dsid
LEFT JOIN telemetrylink_module tlm
ON t.dsid IN (tlm.tenant1_dsid, tlm.tenant2_dsid, tlm.tenant3_dsid)
LEFT JOIN LATERAL (
SELECT ot.site_id
FROM onboarding_transactions ot
WHERE ot.equipment_type = $56
AND ot.equipment_id = tlm.id
ORDER BY ot.created_at DESC NULLS LAST
LIMIT $57
) ots ON $58
LEFT JOIN sites s ON s.id = ots.site_id
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $59
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $60
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $61
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $62
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $63
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $64
LEFT JOIN assets a1 ON a1.id = ac1.asset_id
LEFT JOIN assets a2 ON a2.id = ac2.asset_id
LEFT JOIN assets a3 ON a3.id = ac3.asset_id
LEFT JOIN assets a4 ON a4.id = ac4.asset_id
LEFT JOIN assets a5 ON a5.id = ac5.asset_id
LEFT JOIN assets a6 ON a6.id = ac6.asset_id
LEFT JOIN part_numbers pn1 ON pn1.id = a1.part_number_id
LEFT JOIN part_numbers pn2 ON pn2.id = a2.part_number_id
LEFT JOIN part_numbers pn3 ON pn3.id = a3.part_number_id
LEFT JOIN part_numbers pn4 ON pn4.id = a4.part_number_id
LEFT JOIN part_numbers pn5 ON pn5.id = a5.part_number_id
LEFT JOIN part_numbers pn6 ON pn6.id = a6.part_number_id
LEFT JOIN part_number_classifications cat1 ON cat1.id = pn1.category_id
LEFT JOIN part_number_classifications sub1 ON sub1.id = pn1.sub_category_id
LEFT JOIN part_number_classifications cat2 ON cat2.id = pn2.category_id
LEFT JOIN part_number_classifications sub2 ON sub2.id = pn2.sub_category_id
LEFT JOIN part_number_classifications cat3 ON cat3.id = pn3.category_id
LEFT JOIN part_number_classifications sub3 ON sub3.id = pn3.sub_category_id
LEFT JOIN part_number_classifications cat4 ON cat4.id = pn4.category_id
LEFT JOIN part_number_classifications sub4 ON sub4.id = pn4.sub_category_id
LEFT JOIN part_number_classifications cat5 ON cat5.id = pn5.category_id
LEFT JOIN part_number_classifications sub5 ON sub5.id = pn5.sub_category_id
LEFT JOIN part_number_classifications cat6 ON cat6.id = pn6.category_id
LEFT JOIN part_number_classifications sub6 ON sub6.id = pn6.sub_category_id
WHERE r.rn = $65
AND (
ac1.asset_id = $1 OR
ac2.asset_id = $1 OR
ac3.asset_id = $1 OR
ac4.asset_id = $1 OR
ac5.asset_id = $1 OR
ac6.asset_id = $1
)
ORDER BY r.rtu_id, r.type
|
|
0 min
< 0.1%
|
0 ms
|
238,455
sunrise_user
|
SELECT
t.dsid AS tenant_dsid,
ac.tenant_port,
ac.ct_manufacturer,
ac.ct_rating,
ac.ct_factor,
ac.monitored_point,
ac.breaker_id,
a.id as asset_id,
a.ip as asset_ip,
a.part_number_id,
a.host_name,
a.equipment_name,
a.equipment_location
FROM ac_ct ac
JOIN tenant t ON t.id = ac.parent_tenant_id
JOIN assets a ON a.id = ac.asset_id
WHERE a.host_name = $1
ORDER BY t.dsid, ac.tenant_port
|
|
0 min
< 0.1%
|
148 ms
|
160
sunrise_user
|
SELECT
a.host_name,
dh.start_hour_time AS time,
dh.temperature_delta,
$1::numeric AS temperature_t1,
$2::numeric AS temperature_t2,
$3::numeric AS temperature_t3,
$4::numeric AS temperature_t4
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
JOIN part_numbers pn ON a.part_number_id = pn.id
JOIN part_number_classifications pc ON pn.sub_category_id = pc.id
WHERE dh.start_hour_time BETWEEN $5 AND $6
AND s.name = $7
AND pc.title = $8
-- AND a.host_name NOT IN ('fkbBRN001', 'fkbBRN003', 'fkbBRN007', 'fngBRN701')
AND EXISTS (
SELECT $9
FROM ac_ct ac
JOIN tenant t ON t.id = ac.parent_tenant_id
WHERE ac.asset_id = a.id
)
ORDER BY dh.start_hour_time
|