|
18 min
18%
|
1,154 ms
|
946
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*/
|
|
16 min
16%
|
39 ms
|
24,980
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
|
|
10 min
10%
|
15,652 ms
|
40
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.name = $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
|
|
8 min
8%
|
0 ms
|
4,959,877
sunrise_user
|
INSERT INTO data_snmp_202601 ( 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
)
|
|
8 min
8%
|
0 ms
|
4,597,679
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
)
|
|
5 min
5%
|
5,383 ms
|
57
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 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
|
|
5 min
5%
|
2,670 ms
|
114
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
|
|
5 min
5%
|
0 ms
|
599,235
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
)
|
|
4 min
3%
|
3,760 ms
|
57
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
|
|
3 min
2%
|
6 ms
|
24,940
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
|
|
2 min
2%
|
2,346 ms
|
57
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
|
|
2 min
2%
|
0 ms
|
553,046
sunrise_user
|
INSERT INTO data_json_202601 ( asset_id, part_number_id, ip, time, time_unix, type, data) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7
)
|
|
2 min
2%
|
0 ms
|
777,383
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
)
|
|
1 min
1%
|
8,140 ms
|
10
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
|
|
1 min
1%
|
78 ms
|
919
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*/
|
|
1 min
1%
|
1,101 ms
|
58
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 $4 from (d.data->d.type->$5->$6)::text) AS snmp_dsid,
EXTRACT($7 FROM (now() - time)) AS time_ago_seconds,
EXTRACT($8 FROM (now() - time)) AS time_ago_status
FROM data_snmp_202512 d
WHERE d.type ILIKE $9
AND d.time BETWEEN $1 AND $2
)
SELECT
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->$10->$11)::text::integer, $12) AS temperature,
-- TelemetryLink Module details
tlm.asset_qr AS telemetry_asset_qr,
tlm.label AS telemetry_label,
-- Site
s.id AS site_id,
s.name AS site_name,
-- ================= CT 1 =================
CASE
WHEN ac1.ct_manufacturer ILIKE $13
THEN ((COALESCE((r.data->r.type->$14->$15)::text::float,$16)+$17)/$18)
* COALESCE(ac1.ct_rating,$19) * ac1.ct_factor
WHEN ac1.ct_manufacturer ILIKE $20
THEN ((COALESCE((r.data->r.type->$21->$22)::text::float,$23)+$24)*$25)
* COALESCE(ac1.ct_rating,$26) * ac1.ct_factor
ELSE $27
END AS ac_ct_1,
ac1.ct_rating, ac1.monitored_point AS monitored_point_1, ac1.ct_manufacturer,
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_manufacturer ILIKE $28
THEN ((COALESCE((r.data->r.type->$29->$30)::text::float,$31)+$32)/$33)
* COALESCE(ac2.ct_rating,$34) * ac2.ct_factor
WHEN ac2.ct_manufacturer ILIKE $35
THEN ((COALESCE((r.data->r.type->$36->$37)::text::float,$38)+$39)*$40)
* COALESCE(ac2.ct_rating,$41) * ac2.ct_factor
ELSE $42
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_manufacturer ILIKE $43
THEN ((COALESCE((r.data->r.type->$44->$45)::text::float,$46)+$47)/$48)
* COALESCE(ac3.ct_rating,$49) * ac3.ct_factor
WHEN ac3.ct_manufacturer ILIKE $50
THEN ((COALESCE((r.data->r.type->$51->$52)::text::float,$53)+$54)*$55)
* COALESCE(ac3.ct_rating,$56) * ac3.ct_factor
ELSE $57
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_manufacturer ILIKE $58
THEN ((COALESCE((r.data->r.type->$59->$60)::text::float,$61)+$62)/$63)
* COALESCE(ac4.ct_rating,$64) * ac4.ct_factor
WHEN ac4.ct_manufacturer ILIKE $65
THEN ((COALESCE((r.data->r.type->$66->$67)::text::float,$68)+$69)*$70)
* COALESCE(ac4.ct_rating,$71) * ac4.ct_factor
ELSE $72
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_manufacturer ILIKE $73
THEN ((COALESCE((r.data->r.type->$74->$75)::text::float,$76)+$77)/$78)
* COALESCE(ac5.ct_rating,$79) * ac5.ct_factor
WHEN ac5.ct_manufacturer ILIKE $80
THEN ((COALESCE((r.data->r.type->$81->$82)::text::float,$83)+$84)*$85)
* COALESCE(ac5.ct_rating,$86) * ac5.ct_factor
ELSE $87
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_manufacturer ILIKE $88
THEN ((COALESCE((r.data->r.type->$89->$90)::text::float,$91)+$92)/$93)
* COALESCE(ac6.ct_rating,$94) * ac6.ct_factor
WHEN ac6.ct_manufacturer ILIKE $95
THEN ((COALESCE((r.data->r.type->$96->$97)::text::float,$98)+$99)*$100)
* COALESCE(ac6.ct_rating,$101) * ac6.ct_factor
ELSE $102
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 = $103
AND ot.equipment_id = tlm.id
ORDER BY ot.created_at DESC NULLS LAST
LIMIT $104
) ots ON $105
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 = $106
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $107
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $108
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $109
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $110
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $111
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 s.name = $3
ORDER BY r.time ASC
|
|
1 min
1.0%
|
61,295 ms
|
1
sunrise_user
|
WITH ranked AS (
SELECT
d.*,
NOW() - d.time AS time_ago,
EXTRACT($1 FROM (NOW() - d.time)) AS time_ago_seconds,
EXTRACT($2 FROM (NOW() - d.time)) AS time_ago_status,
ROW_NUMBER() OVER (
PARTITION BY d.rtu_id, d.type
ORDER BY d.time DESC, d.data_seq DESC
) AS rn
FROM public.data_snmp_202511 d
)
SELECT
ls.rtu_id,
ls.type,
ls.data_seq,
ls.time,
ls.ip,
ls.data,
COALESCE((ls.data->ls.type->$3)::text::integer, $4)/$5 AS temperature,
COALESCE((ls.data->ls.type->$6)::text::integer, $7)/$8 AS humidity,
ls.time_ago,
ls.time_ago_seconds,
ls.time_ago_status,
$9 AS device_type,
trh.id AS trh_id,
trh.label AS trh_label,
trh.asset_qr AS trh_asset_qr,
trh.placement AS trh_location,
trh.register_address AS trh_register_address,
a.id AS asset_id,
a.host_name AS asset_host_name,
pn.id AS part_number_id,
c1.title AS category_title,
c2.title AS sub_category_title,
cn.id AS corelink_id,
cn.label AS corelink_label,
cn.site_id AS site_id,
s.name AS site_name,
s.address AS site_address
FROM ranked ls
LEFT JOIN corelink_node cn
ON ls.ip::inet = cn.ip
LEFT JOIN sites s
ON s.id = cn.site_id
LEFT JOIN trh_sensor trh
ON trh.register_address = ls.type
AND ls.type ILIKE $10 AND ls.type NOT ILIKE $11
LEFT JOIN assets a
ON a.id = trh.asset_id
LEFT JOIN part_numbers pn
ON pn.id = a.part_number_id
LEFT JOIN part_number_classifications c1
ON c1.id = pn.category_id
LEFT JOIN part_number_classifications c2
ON c2.id = pn.sub_category_id
WHERE ls.rn = $12
AND s.name = $13
AND ls.type ILIKE $14
AND ls.type NOT ILIKE $15
ORDER BY ls.rtu_id, ls.type
|
|
1 min
0.7%
|
0 ms
|
415,185
sunrise_user
|
INSERT INTO data_snmp_202602 ( 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.6%
|
693 ms
|
52
sunrise_user
|
SELECT
time,
CASE
WHEN jsonb_extract_path_text(data, $1) ~ $2
THEN CAST(jsonb_extract_path_text(data, $3) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $4
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($5 /*, ... */)
), $6)
ELSE $7
END AS ac_phase_01_voltage,
CASE
WHEN jsonb_extract_path_text(data, $8) ~ $9
THEN CAST(jsonb_extract_path_text(data, $10) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $11
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($12 /*, ... */)
), $13)
ELSE $14
END AS ac_phase_02_voltage,
CASE
WHEN jsonb_extract_path_text(data, $15) ~ $16
THEN CAST(jsonb_extract_path_text(data, $17) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $18
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($19 /*, ... */)
), $20)
ELSE $21
END AS ac_phase_03_voltage,
CASE
WHEN jsonb_extract_path_text(data, $22) ~ $23
THEN CAST(jsonb_extract_path_text(data, $24) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $25
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($26 /*, ... */)
), $27)
ELSE $28
END AS ac_phase_01_power_factor,
CASE
WHEN jsonb_extract_path_text(data, $29) ~ $30
THEN CAST(jsonb_extract_path_text(data, $31) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $32
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($33 /*, ... */)
), $34)
ELSE $35
END AS ac_phase_02_power_factor,
CASE
WHEN jsonb_extract_path_text(data, $36) ~ $37
THEN CAST(jsonb_extract_path_text(data, $38) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $39
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($40 /*, ... */)
), $41)
ELSE $42
END AS ac_phase_03_power_factor
FROM data_json
WHERE ip IS NOT NULL
AND ip = $43::inet
AND time BETWEEN $44 AND $45
ORDER BY time
|
|
1 min
0.5%
|
1 ms
|
33,571
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
|
|
1 min
0.5%
|
3,156 ms
|
10
sunrise_user
|
SELECT
d.time,
CASE
WHEN jsonb_extract_path_text(d.data, key) ~ $1
THEN jsonb_extract_path_text(d.data, key)::float * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = key
AND pndp.part_number_id = d.part_number_id
AND pndp.data_type IN ($2 /*, ... */)
), $3)
ELSE $4
END AS value,
key AS metric
FROM data_json_ts d,
-- LATERAL UNNEST(ARRAY[quote_literal('compressor1RAW','compressor2RAW','compressorOperating1RAW','compressorOperating2RAW','compressorOperating3RAW','compressorOperating4RAW','compressorOperating5RAW','compressorOperating6RAW','compressorsAvailable')::text]) AS key
LATERAL UNNEST(ARRAY[$5 /*, ... */]) AS key
WHERE d.ip IS NOT NULL
AND d.ip = $6::inet
AND d.time BETWEEN $7 AND $8
ORDER BY d.time, d.asset_id, key
|
|
1 min
0.5%
|
204 ms
|
152
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
|
|
1 min
0.5%
|
10,179 ms
|
3
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_202512 d
WHERE d.type ILIKE $7 -- all TEN* types
)
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_manufacturer ILIKE $11
THEN ((COALESCE((r.data->r.type->$12->$13)::text::float,$14)+$15)/$16) * COALESCE(ac1.ct_rating,$17) * ac1.ct_factor
WHEN ac1.ct_manufacturer ILIKE $18
THEN ((COALESCE((r.data->r.type->$19->$20)::text::float,$21)+$22)*$23) * COALESCE(ac1.ct_rating,$24) * ac1.ct_factor
ELSE $25
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_manufacturer ILIKE $26
THEN ((COALESCE((r.data->r.type->$27->$28)::text::float,$29)+$30)/$31) * COALESCE(ac2.ct_rating,$32) * ac2.ct_factor
WHEN ac2.ct_manufacturer ILIKE $33
THEN ((COALESCE((r.data->r.type->$34->$35)::text::float,$36)+$37)*$38) * COALESCE(ac2.ct_rating,$39) * ac2.ct_factor
ELSE $40
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_manufacturer ILIKE $41
THEN ((COALESCE((r.data->r.type->$42->$43)::text::float,$44)+$45)/$46) * COALESCE(ac3.ct_rating,$47) * ac3.ct_factor
WHEN ac3.ct_manufacturer ILIKE $48
THEN ((COALESCE((r.data->r.type->$49->$50)::text::float,$51)+$52)*$53) * COALESCE(ac3.ct_rating,$54) * ac3.ct_factor
ELSE $55
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_manufacturer ILIKE $56
THEN ((COALESCE((r.data->r.type->$57->$58)::text::float,$59)+$60)/$61) * COALESCE(ac4.ct_rating,$62) * ac4.ct_factor
WHEN ac4.ct_manufacturer ILIKE $63
THEN ((COALESCE((r.data->r.type->$64->$65)::text::float,$66)+$67)*$68) * COALESCE(ac4.ct_rating,$69) * ac4.ct_factor
ELSE $70
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_manufacturer ILIKE $71
THEN ((COALESCE((r.data->r.type->$72->$73)::text::float,$74)+$75)/$76) * COALESCE(ac5.ct_rating,$77) * ac5.ct_factor
WHEN ac5.ct_manufacturer ILIKE $78
THEN ((COALESCE((r.data->r.type->$79->$80)::text::float,$81)+$82)*$83) * COALESCE(ac5.ct_rating,$84) * ac5.ct_factor
ELSE $85
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_manufacturer ILIKE $86
THEN ((COALESCE((r.data->r.type->$87->$88)::text::float,$89)+$90)/$91) * COALESCE(ac6.ct_rating,$92) * ac6.ct_factor
WHEN ac6.ct_manufacturer ILIKE $93
THEN ((COALESCE((r.data->r.type->$94->$95)::text::float,$96)+$97)*$98) * COALESCE(ac6.ct_rating,$99) * ac6.ct_factor
ELSE $100
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 = $101
AND ot.equipment_id = tlm.id
ORDER BY ot.created_at DESC NULLS LAST
LIMIT $102
) ots ON $103
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 = $104
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $105
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $106
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $107
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $108
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $109
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 = $110
AND s.name = $1
ORDER BY r.rtu_id, r.type
|
|
0 min
0.4%
|
234 ms
|
105
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
EXTRACT($1 FROM (data_json.time - CURRENT_TIMESTAMP)) AS time_ago_seconds,
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 $2 THEN $3
ELSE $4
END AS status,
COALESCE(assets.host_name, $5) AS 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
LEFT JOIN assets ON data_json.asset_id = assets.id
LEFT JOIN sites ON assets.site_id = sites.id
LEFT 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 data_json.ip::text = '10.70.13.154'
AND data_json.ip = $6
ORDER BY data_json.ip, data_json.time DESC
)
SELECT
lp.ip,
lp.time,
lp.asset_id,
lp.part_number_id,
lp.time_unix,
lp.type,
lp.status,
lp.host_name,
lp.floor,
lp.room,
lp.zone,
lp.site_name,
lp.site_address,
lp.cluster_id,
lp.part_number_name,
lp.part_number_short_name,
lp.part_number_description,
lp.category_title,
lp.category_short_name,
lp.subcategory_title,
lp.subcategory_short_name,
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,
keys.key AS metric,
pndp.unit
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT keys AS key FROM jsonb_object_keys(lp.data) AS keys) 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
ORDER BY lp.ip, lp.time DESC, keys.key
|
|
0 min
0.3%
|
863 ms
|
24
sunrise_user
|
SELECT
d.time,
CASE
WHEN jsonb_extract_path_text(d.data, key) ~ $1
THEN jsonb_extract_path_text(d.data, key)::float * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = key
AND pndp.part_number_id = d.part_number_id
AND pndp.data_type IN ($2 /*, ... */)
), $3)
ELSE $4
END AS value,
key AS metric
FROM data_json_ts d,
-- LATERAL UNNEST(ARRAY[quote_literal('alarm-10-spv')::text]) AS key
LATERAL UNNEST(ARRAY[$5]) AS key
WHERE d.ip IS NOT NULL
AND d.ip = $6::inet
AND d.time BETWEEN $7 AND $8
ORDER BY d.time, d.asset_id, key
|
|
0 min
0.3%
|
20,278 ms
|
1
sunrise_user
|
WITH ranked AS (
SELECT
d.*,
NOW() - d.time AS time_ago,
EXTRACT($1 FROM (NOW() - d.time)) AS time_ago_seconds,
EXTRACT($2 FROM (NOW() - d.time)) AS time_ago_status,
ROW_NUMBER() OVER (
PARTITION BY d.rtu_id, d.type
ORDER BY d.time DESC, d.data_seq DESC
) AS rn
FROM public.data_snmp_202511 d
)
SELECT
ls.rtu_id,
ls.type,
ls.data_seq,
ls.time,
ls.ip,
ls.data,
ls.time_ago,
ls.time_ago_seconds,
ls.time_ago_status,
-- Device classification
CASE
WHEN ls.type ILIKE $3 THEN $4
WHEN ls.type ILIKE $5 THEN $6
WHEN ls.type ILIKE $7 THEN $8
ELSE $9
END AS device_type,
-- Extract DSID for Tenant devices
CASE
WHEN ls.type ILIKE $10 THEN trim(both $11 from (ls.data->ls.type->$12->$13)::text)
ELSE $14
END AS dsid,
-- Corelink Node info
cn.id AS corelink_id,
cn.label AS corelink_label,
cn.asset_qr AS corelink_asset_qr,
cn.floor,
cn.room,
cn.zone,
-- Site info
s.id AS site_id,
s.name AS site_name,
s.address AS site_address,
s.lat,
s.long,
-- TRH Sensor info (when applicable)
trh.id AS trh_id,
trh.label AS trh_label,
trh.asset_qr AS trh_asset_qr,
trh.placement AS trh_location,
trh.register_address AS trh_register_address,
-- Telemetrylink module info (linked by dsid match)
tlm.id AS telemetrylink_id,
tlm.label AS telemetrylink_label,
tlm.asset_qr AS telemetrylink_asset_qr,
tlm.tenant1_dsid,
tlm.tenant2_dsid,
tlm.tenant3_dsid
FROM ranked ls
LEFT JOIN corelink_node cn
ON ls.ip::inet = cn.ip
LEFT JOIN sites s
ON s.id = cn.site_id
-- ✅ TRH sensor join (when type = trh_sensor.register_address)
LEFT JOIN trh_sensor trh
ON trh.register_address = ls.type
AND ls.type ILIKE $15
-- ✅ Telemetrylink module join (for Tenant devices via DSID match)
LEFT JOIN telemetrylink_module tlm
ON (
trim(both $16 from (ls.data->ls.type->$17->$18)::text) IN (
tlm.tenant1_dsid, tlm.tenant2_dsid, tlm.tenant3_dsid
)
)
AND ls.type ILIKE $19
WHERE ls.rn = $20
ORDER BY ls.rtu_id, ls.type
|
|
0 min
0.3%
|
2,705 ms
|
7
sunrise_user
|
SELECT DISTINCT ON (d.rtu_id, d.type)
d.rtu_id,
d.type,
d.data_seq,
d.time,
d.ip,
d.data,
(d.data->d.type->>$2)::float / $3 AS temperature,
(d.data->d.type->>$4)::float / $5 AS humidity,
$6 AS device_type,
trh.id AS trh_id,
trh.label AS trh_label,
trh.asset_qr AS trh_asset_qr,
trh.placement AS trh_location,
trh.register_address AS trh_register_address,
a.id AS asset_id,
a.host_name AS asset_host_name,
pn.id AS part_number_id,
c1.title AS category_title,
c2.title AS sub_category_title,
cn.id AS corelink_id,
cn.label AS corelink_label,
cn.site_id AS site_id,
s.name AS site_name,
s.address AS site_address
FROM public.data_snmp_202512 d
JOIN trh_sensor trh
ON trh.register_address = d.type
AND d.type LIKE $7 -- use LIKE if not case-insensitive needed
AND trh.asset_id = $1 -- moved here for earliest reduction
LEFT JOIN corelink_node cn
ON d.ip::inet = cn.ip
LEFT JOIN sites s
ON s.id = cn.site_id
LEFT JOIN assets a
ON a.id = trh.asset_id
LEFT JOIN part_numbers pn
ON pn.id = a.part_number_id
LEFT JOIN part_number_classifications c1
ON c1.id = pn.category_id
LEFT JOIN part_number_classifications c2
ON c2.id = pn.sub_category_id
ORDER BY d.rtu_id, d.type, d.time DESC, d.data_seq DESC
|
|
0 min
0.3%
|
3,450 ms
|
5
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
sites.cluster_id,
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
FROM data_json_202507 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
ORDER BY data_json.ip, data_json.time DESC
|
|
0 min
0.2%
|
240 ms
|
55
sunrise_user
|
SELECT
time,
CASE
WHEN jsonb_extract_path_text(data, $1) ~ $2
THEN CAST(jsonb_extract_path_text(data, $3) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $4
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($5 /*, ... */)
), $6)
ELSE $7
END AS AC_PHASE_01_VOLTAGE,
CASE
WHEN jsonb_extract_path_text(data, $8) ~ $9
THEN CAST(jsonb_extract_path_text(data, $10) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $11
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($12 /*, ... */)
), $13)
ELSE $14
END AS AC_PHASE_02_VOLTAGE,
CASE
WHEN jsonb_extract_path_text(data, $15) ~ $16
THEN CAST(jsonb_extract_path_text(data, $17) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $18
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($19 /*, ... */)
), $20)
ELSE $21
END AS AC_PHASE_03_VOLTAGE
FROM data_json
WHERE ip IS NOT NULL
AND ip = $22::inet
AND "time" BETWEEN $23 AND $24
ORDER BY time
|
|
0 min
0.2%
|
266 ms
|
46
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
|
|
0 min
0.2%
|
6,007 ms
|
2
sunrise_user
|
SELECT
-- d.unix_time AS time,
date_trunc($1, to_timestamp(d.unix_time)) AS time,
d.type,
dn.dsid,
COALESCE((d.data->d.type->$2->$3)::text::integer, $4) AS temperature,
ct.port,
ct.asset_id,
ct.host_name,
ct.equipment_name,
ct.equipment_location,
ct.ct_value,
ct.ct_rating,
ct.ct_manufacturer,
ct.monitored_point,
ct.breaker_id,
ct.notes,
CONCAT(ct.host_name, $5, ct.equipment_name, $6, ct.breaker_id, $7, ct.monitored_point) AS ct_label
FROM data_snmp d
CROSS JOIN LATERAL (
SELECT trim(both $8 from (d.data->d.type->$9->$10)::text) AS dsid
) dn
JOIN tenant t
ON t.dsid = dn.dsid
-- Per-port CTs
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $11
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $12
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $13
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $14
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $15
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $16
-- Each CT has its own asset
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
-- 🔻 Unpivot CT1..CT6 into rows
CROSS JOIN LATERAL (
SELECT *
FROM (
VALUES
-- CT 1
(
$17,
CASE
WHEN ac1.ct_manufacturer ILIKE $18
THEN ((COALESCE((d.data->d.type->$19->$20)::text::float,$21)+$22)/$23)
* COALESCE(ac1.ct_factor,$24)
WHEN ac1.ct_manufacturer ILIKE $25
THEN ((COALESCE((d.data->d.type->$26->$27)::text::float,$28)+$29)*$30)
* COALESCE(ac1.ct_rating,$31) * COALESCE(ac1.ct_factor,$32)
ELSE COALESCE((d.data->d.type->$33->$34)::text::float,$35)
END,
ac1.ct_rating,
ac1.ct_manufacturer,
ac1.monitored_point,
ac1.breaker_id,
ac1.notes,
a1.id,
a1.host_name,
a1.equipment_name,
a1.equipment_location
),
-- CT 2
(
$36,
CASE
WHEN ac2.ct_manufacturer ILIKE $37
THEN ((COALESCE((d.data->d.type->$38->$39)::text::float,$40)+$41)/$42)
* COALESCE(ac2.ct_factor,$43)
WHEN ac2.ct_manufacturer ILIKE $44
THEN ((COALESCE((d.data->d.type->$45->$46)::text::float,$47)+$48)*$49)
* COALESCE(ac2.ct_rating,$50) * COALESCE(ac2.ct_factor,$51)
ELSE COALESCE((d.data->d.type->$52->$53)::text::float,$54)
END,
ac2.ct_rating,
ac2.ct_manufacturer,
ac2.monitored_point,
ac2.breaker_id,
ac2.notes,
a2.id,
a2.host_name,
a2.equipment_name,
a2.equipment_location
),
-- CT 3
(
$55,
CASE
WHEN ac3.ct_manufacturer ILIKE $56
THEN ((COALESCE((d.data->d.type->$57->$58)::text::float,$59)+$60)/$61)
* COALESCE(ac3.ct_factor,$62)
WHEN ac3.ct_manufacturer ILIKE $63
THEN ((COALESCE((d.data->d.type->$64->$65)::text::float,$66)+$67)*$68)
* COALESCE(ac3.ct_rating,$69) * COALESCE(ac3.ct_factor,$70)
ELSE COALESCE((d.data->d.type->$71->$72)::text::float,$73)
END,
ac3.ct_rating,
ac3.ct_manufacturer,
ac3.monitored_point,
ac3.breaker_id,
ac3.notes,
a3.id,
a3.host_name,
a3.equipment_name,
a3.equipment_location
),
-- CT 4
(
$74,
CASE
WHEN ac4.ct_manufacturer ILIKE $75
THEN ((COALESCE((d.data->d.type->$76->$77)::text::float,$78)+$79)/$80)
* COALESCE(ac4.ct_factor,$81)
WHEN ac4.ct_manufacturer ILIKE $82
THEN ((COALESCE((d.data->d.type->$83->$84)::text::float,$85)+$86)*$87)
* COALESCE(ac4.ct_rating,$88) * COALESCE(ac4.ct_factor,$89)
ELSE COALESCE((d.data->d.type->$90->$91)::text::float,$92)
END,
ac4.ct_rating,
ac4.ct_manufacturer,
ac4.monitored_point,
ac4.breaker_id,
ac4.notes,
a4.id,
a4.host_name,
a4.equipment_name,
a4.equipment_location
),
-- CT 5
(
$93,
CASE
WHEN ac5.ct_manufacturer ILIKE $94
THEN ((COALESCE((d.data->d.type->$95->$96)::text::float,$97)+$98)/$99)
* COALESCE(ac5.ct_factor,$100)
WHEN ac5.ct_manufacturer ILIKE $101
THEN ((COALESCE((d.data->d.type->$102->$103)::text::float,$104)+$105)*$106)
* COALESCE(ac5.ct_rating,$107) * COALESCE(ac5.ct_factor,$108)
ELSE COALESCE((d.data->d.type->$109->$110)::text::float,$111)
END,
ac5.ct_rating,
ac5.ct_manufacturer,
ac5.monitored_point,
ac5.breaker_id,
ac5.notes,
a5.id,
a5.host_name,
a5.equipment_name,
a5.equipment_location
),
-- CT 6
(
$112,
CASE
WHEN ac6.ct_manufacturer ILIKE $113
THEN ((COALESCE((d.data->d.type->$114->$115)::text::float,$116)+$117)/$118)
* COALESCE(ac6.ct_factor,$119)
WHEN ac6.ct_manufacturer ILIKE $120
THEN ((COALESCE((d.data->d.type->$121->$122)::text::float,$123)+$124)*$125)
* COALESCE(ac6.ct_factor,$126)
ELSE COALESCE((d.data->d.type->$127->$128)::text::float,$129)
END,
ac6.ct_rating,
ac6.ct_manufacturer,
ac6.monitored_point,
ac6.breaker_id,
ac6.notes,
a6.id,
a6.host_name,
a6.equipment_name,
a6.equipment_location
)
) AS x(
port,
ct_value,
ct_rating,
ct_manufacturer,
monitored_point,
breaker_id,
notes,
asset_id,
host_name,
equipment_name,
equipment_location
)
WHERE x.asset_id IS NOT NULL -- only existing CTs
) AS ct
-- WHERE d.rtu_id = 102101969
-- WHERE d.rtu_id IN (SELECT REPLACE( split_part(ip::text, '/', 1), '.', '' )::bigint AS ip_number FROM corelink_node WHERE site_id = 4)
JOIN (
SELECT REPLACE(split_part(ip::text, $130, $131), $132, $133)::bigint AS rtu_id
FROM corelink_node
WHERE site_id = $134
) cn ON cn.rtu_id = d.rtu_id
AND d.time BETWEEN $135 AND $136
-- AND (ct.asset_id = 101) -- 👈 replace 108 with $asset_id in Grafana // OR 14 IS NULL
AND ct.host_name = $137
ORDER BY d.time ASC, ct.port
|
|
0 min
0.2%
|
1,407 ms
|
8
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
sites.cluster_id,
assets.host_name,
sites.name AS site_name,
assets.room,
sites.address AS site_address,
cat_class.title AS category_title,
subcat_class.title AS subcategory_title,
part_numbers.name AS part_number_name,
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
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 = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE s.name = $5 AND a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
|
|
0 min
0.2%
|
431 ms
|
25
sunrise_user
|
SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6 /*, ... */) AND n.nspname !~ $7 AND c.relkind IN ($8 /*, ... */) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/
|
|
0 min
0.2%
|
871 ms
|
12
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
sites.cluster_id,
assets.host_name,
sites.name AS site_name,
assets.room,
sites.address AS site_address,
cat_class.title AS category_title,
subcat_class.title AS subcategory_title,
part_numbers.name AS part_number_name,
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
FROM 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 = $4
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE s.name = $5 AND a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
|
|
0 min
0.2%
|
5,119 ms
|
2
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
sites.cluster_id,
assets.host_name,
sites.name AS site_name,
assets.room,
sites.address AS site_address,
cat_class.title AS category_title,
subcat_class.title AS subcategory_title,
part_numbers.name AS part_number_name,
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
AND sites.name = $13
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE s.name = $14 AND a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
|
|
0 min
0.2%
|
0 ms
|
46,189
sunrise_user
|
INSERT INTO data_json_202602 ( asset_id, part_number_id, ip, time, time_unix, type, data) VALUES (
$1,
$2,
$3,
$4,
$5,
$6,
$7
)
|
|
0 min
0.2%
|
205 ms
|
48
sunrise_user
|
SELECT
time,
CASE
WHEN jsonb_extract_path_text(data, $1) ~ $2
THEN CAST(jsonb_extract_path_text(data, $3) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $4
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($5 /*, ... */)
), $6)
ELSE $7
END AS SYSTEM_VOLTAGE/*,
CASE
WHEN jsonb_extract_path_text(data, 'loadCurrent') ~ '^[+-]?[0-9]*\.?[0-9]*$'
THEN CAST(jsonb_extract_path_text(data, 'loadCurrent') AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = 'loadCurrent'
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ('float', 'integer')
), 1.0)
ELSE NULL
END AS LOAD_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, 'batteryCurrent') ~ '^[+-]?[0-9]*\.?[0-9]*$'
THEN CAST(jsonb_extract_path_text(data, 'batteryCurrent') AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = 'batteryCurrent'
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ('float', 'integer')
), 1.0)
ELSE NULL
END AS BATTERY_CURRENT*/
FROM data_json
WHERE ip IS NOT NULL
AND ip = $8::inet
AND "time" BETWEEN $9 AND $10
ORDER BY time
|
|
0 min
0.2%
|
11 ms
|
919
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*/
|
|
0 min
0.2%
|
939 ms
|
10
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
EXTRACT($1 FROM (CURRENT_TIMESTAMP - data_json.time)) + $2 AS time_ago_seconds,
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 $3 THEN $4
ELSE $5
END AS status,
COALESCE(assets.host_name, $6) AS 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
LEFT JOIN assets ON data_json.asset_id = assets.id
LEFT JOIN sites ON assets.site_id = sites.id
LEFT 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 data_json.ip = $7::inet
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
)
SELECT
lp.ip,
lp.time,
lp.time_ago_seconds,
lp.asset_id,
lp.part_number_id,
lp.time_unix,
lp.type,
lp.status,
lp.host_name,
lp.floor,
lp.room,
lp.zone,
lp.site_name,
lp.site_address,
lp.cluster_id,
lp.part_number_name,
lp.part_number_short_name,
lp.part_number_description,
lp.category_title,
lp.category_short_name,
lp.subcategory_title,
lp.subcategory_short_name,
jsonb_extract_path_text(lp.data, keys.key) AS raw_value,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $8
THEN jsonb_extract_path_text(lp.data, keys.key)::float
ELSE $9
END AS raw_numeric_value,
pndp.normalization_function,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $10
THEN (jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $11))
ELSE $12
END AS normalized_value,
keys.key AS metric,
pndp.unit,
pndp.data_type
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT keys AS key FROM jsonb_object_keys(lp.data) AS keys) 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 ($13 /*, ... */)
WHERE jsonb_typeof(lp.data) = $14
ORDER BY lp.ip, lp.time DESC, keys.key
|
|
0 min
0.1%
|
634 ms
|
13
sunrise_user
|
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
sites.cluster_id,
assets.host_name,
sites.name AS site_name,
assets.room,
sites.address AS site_address,
cat_class.title AS category_title,
subcat_class.title AS subcategory_title,
part_numbers.name AS part_number_name,
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
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 = $13
AND data_json.ip IN (
SELECT DISTINCT a.ip::inet
FROM assets a
JOIN sites s ON a.site_id = s.id
WHERE s.name = $14 AND a.ip IS NOT NULL
)
ORDER BY data_json.ip, data_json.time DESC
|
|
0 min
0.1%
|
2,681 ms
|
3
sunrise_user
|
WITH base AS (
SELECT
date_trunc($1, to_timestamp(d.unix_time)) AS time,
d.unix_time,
d.data,
d.type,
a.host_name,
trh.placement,
s.name AS site_name
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 LIKE $2
AND d.type NOT LIKE $3
AND a.host_name = $4
-- AND a.id = 101
AND s.name = $5
AND d.unix_time BETWEEN EXTRACT($6 FROM $7::timestamptz)
AND EXTRACT($8 FROM $9::timestamptz)
),
vals AS (
SELECT
b.time,
b.host_name,
b.placement,
(b.data->b.type->>$10)::float / $11 AS temperature,
(b.data->b.type->>$12)::float / $13 AS humidity
FROM base b
)
-- Temperature metric
SELECT
time,
temperature AS value,
host_name || $14 || placement || $15 AS metric
FROM vals
UNION ALL
-- Humidity metric
SELECT
time,
humidity AS value,
host_name || $16 || placement || $17 AS metric
FROM vals
ORDER BY time
|
|
0 min
0.1%
|
4 ms
|
1,930
sunrise_user
|
SELECT
d.unix_time AS time,
d.type,
d.data,
trh.asset_id,
trh.placement
FROM data_normalized d
LEFT JOIN trh_sensor trh ON trh.register_address = d.type
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
|
|
0 min
0.1%
|
3,709 ms
|
2
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
EXTRACT($1 FROM (CURRENT_TIMESTAMP - data_json.time)) + $2 AS time_ago_seconds,
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 $3 THEN $4
ELSE $5
END AS status,
COALESCE(assets.host_name, $6) AS 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
LEFT JOIN assets ON data_json.asset_id = assets.id
LEFT JOIN sites ON assets.site_id = sites.id
LEFT 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 data_json.ip = '$ip_filter'::inet
AND sites.name = $7
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
)
SELECT
lp.ip,
lp.time,
lp.time_ago_seconds,
lp.asset_id,
lp.part_number_id,
lp.time_unix,
lp.type,
lp.status,
lp.host_name,
lp.floor,
lp.room,
lp.zone,
lp.site_name,
lp.site_address,
lp.cluster_id,
lp.part_number_name,
lp.part_number_short_name,
lp.part_number_description,
lp.category_title,
lp.category_short_name,
lp.subcategory_title,
lp.subcategory_short_name,
jsonb_extract_path_text(lp.data, keys.key) AS raw_value,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $8
THEN jsonb_extract_path_text(lp.data, keys.key)::float
ELSE $9
END AS raw_numeric_value,
pndp.normalization_function,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $10
THEN (jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $11))
ELSE $12
END AS normalized_value,
keys.key AS metric,
pndp.unit,
pndp.data_type
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT keys AS key FROM jsonb_object_keys(lp.data) AS keys) 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 ($13 /*, ... */)
WHERE jsonb_typeof(lp.data) = $14
ORDER BY lp.ip, lp.time DESC, keys.key
|
|
0 min
0.1%
|
161 ms
|
46
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%
|
1,684 ms
|
4
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
EXTRACT($1 FROM (CURRENT_TIMESTAMP - data_json.time)) + $2 AS time_ago_seconds,
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 $3 THEN $4
ELSE $5
END AS status,
COALESCE(assets.host_name, $6) AS 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
LEFT JOIN assets ON data_json.asset_id = assets.id
LEFT JOIN sites ON assets.site_id = sites.id
LEFT 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 data_json.ip = '$ip_filter'::inet
-- AND sites.name = 'ZFOX01'
AND assets.host_name IN ($7)
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
)
SELECT
lp.ip,
lp.time,
lp.time_ago_seconds,
lp.asset_id,
lp.part_number_id,
lp.time_unix,
lp.type,
lp.status,
lp.host_name,
lp.floor,
lp.room,
lp.zone,
lp.site_name,
lp.site_address,
lp.cluster_id,
lp.part_number_name,
lp.part_number_short_name,
lp.part_number_description,
lp.category_title,
lp.category_short_name,
lp.subcategory_title,
lp.subcategory_short_name,
jsonb_extract_path_text(lp.data, keys.key) AS raw_value,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $8
THEN jsonb_extract_path_text(lp.data, keys.key)::float
ELSE $9
END AS raw_numeric_value,
pndp.normalization_function,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $10
THEN (jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $11))
ELSE $12
END AS normalized_value,
keys.key AS metric,
pndp.unit,
pndp.data_type
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT keys AS key FROM jsonb_object_keys(lp.data) AS keys) 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 ($13 /*, ... */)
WHERE jsonb_typeof(lp.data) = $14
ORDER BY lp.ip, lp.time DESC, keys.key
|
|
0 min
0.1%
|
14 ms
|
485
sunrise_user
|
SELECT
a.host_name,
dh.start_hour_time AS time,
dh.temperature_delta
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
WHERE dh.start_hour_time BETWEEN $1 AND $2
AND a.site_id = $3
AND EXISTS (
SELECT $4
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
|
|
0 min
0.1%
|
24 ms
|
265
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 = $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
|
|
0 min
0.1%
|
6,457 ms
|
1
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 $1 from (d.data->d.type->$2->$3)::text) AS snmp_dsid,
EXTRACT($4 FROM (now() - time)) AS time_ago_seconds,
EXTRACT($5 FROM (now() - time)) AS time_ago_status
FROM data_snmp_202511 d
WHERE d.type ILIKE $6 -- all TEN* types
)
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->$7->$8)::text::integer, $9) 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->$10->$11)::text::float, $12) + $13) * $14) * ac1.ct_rating) * $15
ELSE (r.data->r.type->$16->$17)::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->$18->$19)::text::float, $20) + $21) * $22) * ac2.ct_rating) * $23
ELSE (r.data->r.type->$24->$25)::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->$26->$27)::text::float, $28) + $29) * $30) * ac3.ct_rating) * $31
ELSE (r.data->r.type->$32->$33)::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->$34->$35)::text::float, $36) + $37) * $38) * ac4.ct_rating
ELSE (r.data->r.type->$39->$40)::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->$41->$42)::text::float, $43) + $44) * $45) * ac5.ct_rating
ELSE (r.data->r.type->$46->$47)::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->$48->$49)::text::float, $50) + $51) * $52) * ac6.ct_rating
ELSE (r.data->r.type->$53->$54)::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
-- Match TLM by tenant DSID
LEFT JOIN telemetrylink_module tlm
ON t.dsid IN (tlm.tenant1_dsid, tlm.tenant2_dsid, tlm.tenant3_dsid)
-- Site via onboarding_transactions (latest for TLM)
LEFT JOIN LATERAL (
SELECT ot.site_id
FROM onboarding_transactions ot
WHERE ot.equipment_type = $55
AND ot.equipment_id = tlm.id
ORDER BY ot.created_at DESC NULLS LAST
LIMIT $56
) ots ON $57
LEFT JOIN sites s ON s.id = ots.site_id
-- Per-port CTs
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $58
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $59
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $60
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $61
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $62
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $63
-- Asset and part info for each CT
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 = $64 AND s.name = $65
ORDER BY r.rtu_id, r.type
|
|
0 min
0.1%
|
6,419 ms
|
1
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%
|
125 ms
|
46
sunrise_user
|
SELECT DISTINCT ON (d.rtu_id, d.type)
d.rtu_id,
d.type,
d.time,
d.ip,
COALESCE((d.data -> d.type ->> $2)::integer, $3) / $4 AS voltage_p1,
COALESCE((d.data -> d.type ->> $5)::integer, $6) / $7 AS voltage_p2,
COALESCE((d.data -> d.type ->> $8)::integer, $9) / $10 AS voltage_p3,
COALESCE((d.data -> d.type ->> $11)::integer, $12) / $13 AS current_p1,
COALESCE((d.data -> d.type ->> $14)::integer, $15) / $16 AS current_p2,
COALESCE((d.data -> d.type ->> $17)::integer, $18) / $19 AS current_p3,
COALESCE((d.data -> d.type ->> $20)::integer, $21) / $22 AS frequency,
COALESCE((d.data -> d.type ->> $23)::integer, $24) / $25 AS power_factor_p1,
COALESCE((d.data -> d.type ->> $26)::integer, $27) / $28 AS power_factor_p2,
COALESCE((d.data -> d.type ->> $29)::integer, $30) / $31 AS power_factor_p3,
s.name AS site_name,
cn.label AS corelink_label
FROM data_normalized d
LEFT JOIN corelink_node cn
ON d.ip::inet = cn.ip
LEFT JOIN sites s
ON s.id = cn.site_id
WHERE d.type = $32
AND d.unix_time >= EXTRACT($33 FROM (now() - INTERVAL $34))::bigint
AND s.name = $1
ORDER BY
d.rtu_id,
d.type,
d.unix_time DESC,
d.data_seq DESC
|
|
0 min
< 0.1%
|
103 ms
|
44
sunrise_user
|
SELECT
date_trunc($1, to_timestamp(time_unix)) AS time,
CASE
WHEN jsonb_extract_path_text(data, $2) ~ $3
THEN CAST(jsonb_extract_path_text(data, $4) AS numeric)
* COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $5
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($6 /*, ... */)
), $7)
ELSE $8
END AS ac_phase_01_voltage,
CASE
WHEN jsonb_extract_path_text(data, $9) ~ $10
THEN CAST(jsonb_extract_path_text(data, $11) AS numeric)
* COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $12
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($13 /*, ... */)
), $14)
ELSE $15
END AS ac_phase_02_voltage,
CASE
WHEN jsonb_extract_path_text(data, $16) ~ $17
THEN CAST(jsonb_extract_path_text(data, $18) AS numeric)
* COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $19
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($20 /*, ... */)
), $21)
ELSE $22
END AS ac_phase_03_voltage
FROM data_json
WHERE ip IS NOT NULL
AND ip = $23::inet
AND time_unix BETWEEN EXTRACT($24 FROM $25::timestamptz)
AND EXTRACT($26 FROM $27::timestamptz)
ORDER BY time
|
|
0 min
< 0.1%
|
147 ms
|
31
sunrise_user
|
SELECT
-- d.unix_time AS time,
date_trunc($1, to_timestamp(d.unix_time)) AS time,
d.type,
dn.dsid,
COALESCE((d.data->d.type->$2->$3)::text::integer, $4) AS temperature,
ct.port,
ct.asset_id,
ct.host_name,
ct.equipment_name,
ct.equipment_location,
ct.ct_value,
ct.ct_rating,
ct.ct_manufacturer,
ct.monitored_point,
ct.breaker_id,
ct.notes,
CONCAT(ct.host_name, $5, ct.equipment_name, $6, ct.breaker_id, $7, ct.monitored_point) AS ct_label
FROM data_snmp d
CROSS JOIN LATERAL (
SELECT trim(both $8 from (d.data->d.type->$9->$10)::text) AS dsid
) dn
JOIN tenant t
ON t.dsid = dn.dsid
-- Per-port CTs
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $11
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $12
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $13
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $14
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $15
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $16
-- Each CT has its own asset
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
-- 🔻 Unpivot CT1..CT6 into rows
CROSS JOIN LATERAL (
SELECT *
FROM (
VALUES
-- CT 1
(
$17,
CASE
WHEN ac1.ct_manufacturer ILIKE $18
THEN ((COALESCE((d.data->d.type->$19->$20)::text::float,$21)+$22)/$23)
* COALESCE(ac1.ct_factor,$24)
WHEN ac1.ct_manufacturer ILIKE $25
THEN ((COALESCE((d.data->d.type->$26->$27)::text::float,$28)+$29)*$30)
* COALESCE(ac1.ct_rating,$31) * COALESCE(ac1.ct_factor,$32)
ELSE COALESCE((d.data->d.type->$33->$34)::text::float,$35)
END,
ac1.ct_rating,
ac1.ct_manufacturer,
ac1.monitored_point,
ac1.breaker_id,
ac1.notes,
a1.id,
a1.host_name,
a1.equipment_name,
a1.equipment_location
),
-- CT 2
(
$36,
CASE
WHEN ac2.ct_manufacturer ILIKE $37
THEN ((COALESCE((d.data->d.type->$38->$39)::text::float,$40)+$41)/$42)
* COALESCE(ac2.ct_factor,$43)
WHEN ac2.ct_manufacturer ILIKE $44
THEN ((COALESCE((d.data->d.type->$45->$46)::text::float,$47)+$48)*$49)
* COALESCE(ac2.ct_rating,$50) * COALESCE(ac2.ct_factor,$51)
ELSE COALESCE((d.data->d.type->$52->$53)::text::float,$54)
END,
ac2.ct_rating,
ac2.ct_manufacturer,
ac2.monitored_point,
ac2.breaker_id,
ac2.notes,
a2.id,
a2.host_name,
a2.equipment_name,
a2.equipment_location
),
-- CT 3
(
$55,
CASE
WHEN ac3.ct_manufacturer ILIKE $56
THEN ((COALESCE((d.data->d.type->$57->$58)::text::float,$59)+$60)/$61)
* COALESCE(ac3.ct_factor,$62)
WHEN ac3.ct_manufacturer ILIKE $63
THEN ((COALESCE((d.data->d.type->$64->$65)::text::float,$66)+$67)*$68)
* COALESCE(ac3.ct_rating,$69) * COALESCE(ac3.ct_factor,$70)
ELSE COALESCE((d.data->d.type->$71->$72)::text::float,$73)
END,
ac3.ct_rating,
ac3.ct_manufacturer,
ac3.monitored_point,
ac3.breaker_id,
ac3.notes,
a3.id,
a3.host_name,
a3.equipment_name,
a3.equipment_location
),
-- CT 4
(
$74,
CASE
WHEN ac4.ct_manufacturer ILIKE $75
THEN ((COALESCE((d.data->d.type->$76->$77)::text::float,$78)+$79)/$80)
* COALESCE(ac4.ct_factor,$81)
WHEN ac4.ct_manufacturer ILIKE $82
THEN ((COALESCE((d.data->d.type->$83->$84)::text::float,$85)+$86)*$87)
* COALESCE(ac4.ct_rating,$88) * COALESCE(ac4.ct_factor,$89)
ELSE COALESCE((d.data->d.type->$90->$91)::text::float,$92)
END,
ac4.ct_rating,
ac4.ct_manufacturer,
ac4.monitored_point,
ac4.breaker_id,
ac4.notes,
a4.id,
a4.host_name,
a4.equipment_name,
a4.equipment_location
),
-- CT 5
(
$93,
CASE
WHEN ac5.ct_manufacturer ILIKE $94
THEN ((COALESCE((d.data->d.type->$95->$96)::text::float,$97)+$98)/$99)
* COALESCE(ac5.ct_factor,$100)
WHEN ac5.ct_manufacturer ILIKE $101
THEN ((COALESCE((d.data->d.type->$102->$103)::text::float,$104)+$105)*$106)
* COALESCE(ac5.ct_rating,$107) * COALESCE(ac5.ct_factor,$108)
ELSE COALESCE((d.data->d.type->$109->$110)::text::float,$111)
END,
ac5.ct_rating,
ac5.ct_manufacturer,
ac5.monitored_point,
ac5.breaker_id,
ac5.notes,
a5.id,
a5.host_name,
a5.equipment_name,
a5.equipment_location
),
-- CT 6
(
$112,
CASE
WHEN ac6.ct_manufacturer ILIKE $113
THEN ((COALESCE((d.data->d.type->$114->$115)::text::float,$116)+$117)/$118)
* COALESCE(ac6.ct_factor,$119)
WHEN ac6.ct_manufacturer ILIKE $120
THEN ((COALESCE((d.data->d.type->$121->$122)::text::float,$123)+$124)*$125)
* COALESCE(ac6.ct_factor,$126)
ELSE COALESCE((d.data->d.type->$127->$128)::text::float,$129)
END,
ac6.ct_rating,
ac6.ct_manufacturer,
ac6.monitored_point,
ac6.breaker_id,
ac6.notes,
a6.id,
a6.host_name,
a6.equipment_name,
a6.equipment_location
)
) AS x(
port,
ct_value,
ct_rating,
ct_manufacturer,
monitored_point,
breaker_id,
notes,
asset_id,
host_name,
equipment_name,
equipment_location
)
WHERE x.asset_id IS NOT NULL -- only existing CTs
) AS ct
-- WHERE d.rtu_id = 102101969
WHERE d.rtu_id IN (SELECT REPLACE( split_part(ip::text, $130, $131), $132, $133 )::bigint AS ip_number FROM corelink_node WHERE site_id = $134)
AND d.time BETWEEN $135 AND $136
AND (ct.asset_id = $137) -- 👈 replace 108 with $asset_id in Grafana
ORDER BY d.time ASC, ct.port
|
|
0 min
< 0.1%
|
2,144 ms
|
2
sunrise_user
|
SELECT type
FROM (
SELECT DISTINCT type
FROM data_snmp_202511
WHERE rtu_id = $1 AND type ~ $2
) t
ORDER BY substring(type from $3)::int
|
|
0 min
< 0.1%
|
25 ms
|
161
sunrise_user
|
WITH base AS (
SELECT
a.host_name,
dh.start_hour_time AS time,
CASE
WHEN dh.ac_kwh_ct >= $1 THEN $2
ELSE $3
END AS power_status,
LAG(
CASE
WHEN dh.ac_kwh_ct >= $4 THEN $5
ELSE $6
END
) OVER (
PARTITION BY a.host_name
ORDER BY dh.start_hour_time
) AS prev_status
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
WHERE dh.start_hour_time BETWEEN $7 AND $8
AND a.site_id = $9
AND EXISTS (
SELECT $10
FROM ac_ct ac
WHERE ac.asset_id = a.id
)
),
state_groups AS (
SELECT *,
SUM(
CASE
WHEN power_status <> prev_status OR prev_status IS NULL
THEN $11 ELSE $12
END
) OVER (
PARTITION BY host_name
ORDER BY time
) AS grp
FROM base
),
duty_blocks AS (
SELECT
host_name,
power_status,
COUNT(*) AS duration_hours
FROM state_groups
GROUP BY host_name, power_status, grp
)
SELECT
host_name,
/* ========================
Total Durations
======================== */
SUM(CASE WHEN power_status = $13 THEN duration_hours ELSE $14 END) AS on_hours,
SUM(CASE WHEN power_status = $15 THEN duration_hours ELSE $16 END) AS off_hours,
/* ========================
Duty Cycle %
======================== */
ROUND(
$17 * SUM(CASE WHEN power_status = $18 THEN duration_hours ELSE $19 END)
/ SUM(duration_hours),
$20
) AS on_duty_percent,
ROUND(
$21 * SUM(CASE WHEN power_status = $22 THEN duration_hours ELSE $23 END)
/ SUM(duration_hours),
$24
) AS off_duty_percent,
/* ========================
State Switch Count
======================== */
COUNT(*) - $25 AS state_switch_count,
/* ========================
ON Duration Statistics
======================== */
MIN(CASE WHEN power_status = $26 THEN duration_hours END) AS on_min_hours,
MAX(CASE WHEN power_status = $27 THEN duration_hours END) AS on_max_hours,
ROUND(
AVG(CASE WHEN power_status = $28 THEN duration_hours END),
$29
) AS on_avg_hours,
/* ========================
OFF Duration Statistics
======================== */
MIN(CASE WHEN power_status = $30 THEN duration_hours END) AS off_min_hours,
MAX(CASE WHEN power_status = $31 THEN duration_hours END) AS off_max_hours,
ROUND(
AVG(CASE WHEN power_status = $32 THEN duration_hours END),
$33
) AS off_avg_hours
FROM duty_blocks
GROUP BY host_name
ORDER BY host_name
|
|
0 min
< 0.1%
|
18 ms
|
220
sunrise_user
|
WITH grid_total AS (
SELECT
SUM(hour_grid_kwh) AS total_grid_kwh
FROM (
SELECT
dh.start_hour_time,
MAX(dh.ac_kwh_grid) AS hour_grid_kwh
FROM data_hour dh
WHERE dh.start_hour_time BETWEEN $1 AND $2
AND dh.site_id = $3
GROUP BY dh.start_hour_time
) g
)
SELECT
a.host_name,
pc.title,
SUM(dh.ac_kwh_ct) AS kwh,
SUM(dh.ac_kwh_grid) AS kwh_grid,
-- CT share among CT totals (your original metric)
(SUM(dh.ac_kwh_ct) / SUM(SUM(dh.ac_kwh_ct)) OVER ()) * $4 AS load_percentage,
-- CT vs TOTAL GRID (correct: grid counted once per hour)
(SUM(dh.ac_kwh_ct) / NULLIF((SELECT total_grid_kwh FROM grid_total), $5)) * $6
AS load_percentage_grid
FROM data_hour dh
JOIN assets a ON a.id = dh.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
WHERE dh.start_hour_time BETWEEN $7 AND $8
AND a.site_id = $9
AND pc.title != $10
AND EXISTS (
SELECT $11
FROM ac_ct ac
WHERE ac.asset_id = a.id
)
GROUP BY a.host_name, pc.title
ORDER BY load_percentage DESC
|
|
0 min
< 0.1%
|
19 ms
|
190
sunrise_user
|
SELECT
a.host_name,
dh.start_hour_time AS time,
dh.ac_kwh_ct,
CASE
WHEN dh.ac_kwh_ct IS NULL THEN $1
WHEN dh.ac_kwh_ct < $2 THEN $3
ELSE $4
END AS power_status,
dh.temperature_top,
dh.temperature_bottom,
dh.temperature_delta,
(dh.ac_kwh_ct * $5) AS CHF
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
WHERE dh.start_hour_time BETWEEN $6 AND $7
AND a.site_id = $8
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
|
|
0 min
< 0.1%
|
4 ms
|
934
sunrise_user
|
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/
|
|
0 min
< 0.1%
|
77 ms
|
46
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (dj.ip)
dj.ip,
dj.time,
(EXTRACT($2 FROM now()) - dj.time_unix) + $3 AS time_ago_seconds,
dj.asset_id,
dj.part_number_id,
dj.data,
dj.type,
CASE
WHEN dj.time_unix >= EXTRACT($4 FROM (now() - INTERVAL $5))::bigint
THEN $6
ELSE $7
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 = $1
-- 🔥 CRITICAL: cutoff for chunk pruning
AND dj.time_unix >= EXTRACT($8 FROM (now() - INTERVAL $9))::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%
|
348 ms
|
10
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
EXTRACT($1 FROM (CURRENT_TIMESTAMP - data_json.time)) + $2 AS time_ago_seconds,
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 $3 THEN $4
ELSE $5
END AS status,
COALESCE(assets.host_name, $6) AS 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
LEFT JOIN assets ON data_json.asset_id = assets.id
LEFT JOIN sites ON assets.site_id = sites.id
LEFT 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 data_json.ip = $7::inet
--AND (sites.name = '$site_filter' OR '$site_filter' = '')
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
)
SELECT
lp.ip,
lp.time,
lp.time_ago_seconds,
lp.asset_id,
lp.part_number_id,
lp.time_unix,
lp.type,
lp.status,
lp.host_name,
lp.floor,
lp.room,
lp.zone,
lp.site_name,
lp.site_address,
lp.cluster_id,
lp.part_number_name,
lp.part_number_short_name,
lp.part_number_description,
lp.category_title,
lp.category_short_name,
lp.subcategory_title,
lp.subcategory_short_name,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $8
THEN (jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $9))
ELSE $10
END AS normalized_value,
keys.key AS metric,
pndp.unit
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT keys AS key FROM jsonb_object_keys(lp.data) AS keys) 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 = $11
WHERE jsonb_typeof(lp.data) = $12
ORDER BY lp.ip, lp.time DESC, keys.key
|
|
0 min
< 0.1%
|
119 ms
|
29
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
|
|
0 min
< 0.1%
|
680 ms
|
5
sunrise_user
|
WITH base AS (
SELECT
date_trunc($1, to_timestamp(d.time_unix)) AS time,
d.part_number_id,
d.data,
d.ip
FROM data_json d
WHERE d.ip = $2::inet
AND d.time_unix BETWEEN EXTRACT($3 FROM $4::timestamptz)
AND EXTRACT($5 FROM $6::timestamptz)
),
norm AS (
SELECT
b.time,
b.ip,
b.part_number_id,
-- Extract JSON values once per row
(b.data->>$7) AS raw_a,
(b.data->>$8) AS raw_b,
(b.data->>$9) AS raw_c
FROM base b
),
norm_fn AS (
SELECT
p.part_number_id,
MAX(CASE WHEN p.name=$10 THEN p.normalization_function END) AS nf_a,
MAX(CASE WHEN p.name=$11 THEN p.normalization_function END) AS nf_b,
MAX(CASE WHEN p.name=$12 THEN p.normalization_function END) AS nf_c
FROM part_number_data_points p
WHERE p.data_type IN ($13 /*, ... */)
GROUP BY p.part_number_id
)
SELECT
n.time,
CASE WHEN n.raw_a ~ $14
THEN n.raw_a::numeric * COALESCE(f.nf_a,$15)
ELSE $16 END AS ac_phase_01_voltage,
CASE WHEN n.raw_b ~ $17
THEN n.raw_b::numeric * COALESCE(f.nf_b,$18)
ELSE $19 END AS ac_phase_02_voltage,
CASE WHEN n.raw_c ~ $20
THEN n.raw_c::numeric * COALESCE(f.nf_c,$21)
ELSE $22 END AS ac_phase_03_voltage
FROM norm n
JOIN norm_fn f ON f.part_number_id = n.part_number_id
ORDER BY n.time
|
|
0 min
< 0.1%
|
4 ms
|
919
sunrise_user
|
SELECT nsp.nspname AS schema, rel.relname AS table, con.conname AS name, fnsp.nspname AS referenced_schema, frel.relname AS referenced_table FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace WHERE con.convalidated = $1 /*pghero*/
|
|
0 min
< 0.1%
|
4 ms
|
919
sunrise_user
|
SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $2 AND n.nspname NOT IN ($3 /*, ... */) /*pghero*/
|
|
0 min
< 0.1%
|
14 ms
|
234
sunrise_user
|
SELECT
a.host_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 a.site_id = $4
AND pc.title != $5
AND EXISTS (
SELECT $6
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, pc.title
ORDER BY load_percentage DESC
|
|
0 min
< 0.1%
|
3,104 ms
|
1
sunrise_user
|
SELECT
type || $1 || dsid AS __text,
type AS __value
FROM (
SELECT DISTINCT ON (d.rtu_id, d.type)
d.type,
trim(both $2 from (d.data->d.type->$3->>$4)) AS dsid,
d.time,
d.data_seq
FROM data_snmp d
WHERE d.rtu_id = $5
AND d.type ~ $6
ORDER BY d.rtu_id, d.type, d.time DESC, d.data_seq DESC
) latest
ORDER BY substring(type from $7)::int
|
|
0 min
< 0.1%
|
511 ms
|
6
sunrise_user
|
SELECT
time,
CASE
WHEN jsonb_extract_path_text(data, $1) ~ $2
THEN CAST(jsonb_extract_path_text(data, $3) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $4
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $5
), $6)
ELSE $7
END AS RECTIFIER_01_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $8) ~ $9
THEN CAST(jsonb_extract_path_text(data, $10) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $11
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $12
), $13)
ELSE $14
END AS RECTIFIER_02_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $15) ~ $16
THEN CAST(jsonb_extract_path_text(data, $17) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $18
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $19
), $20)
ELSE $21
END AS RECTIFIER_03_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $22) ~ $23
THEN CAST(jsonb_extract_path_text(data, $24) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $25
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $26
), $27)
ELSE $28
END AS RECTIFIER_04_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $29) ~ $30
THEN CAST(jsonb_extract_path_text(data, $31) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $32
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $33
), $34)
ELSE $35
END AS RECTIFIER_05_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $36) ~ $37
THEN CAST(jsonb_extract_path_text(data, $38) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $39
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $40
), $41)
ELSE $42
END AS RECTIFIER_06_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $43) ~ $44
THEN CAST(jsonb_extract_path_text(data, $45) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $46
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $47
), $48)
ELSE $49
END AS RECTIFIER_07_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $50) ~ $51
THEN CAST(jsonb_extract_path_text(data, $52) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $53
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $54
), $55)
ELSE $56
END AS RECTIFIER_08_CURRENT,
CASE
WHEN jsonb_extract_path_text(data, $57) ~ $58
THEN CAST(jsonb_extract_path_text(data, $59) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $60
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type = $61
), $62)
ELSE $63
END AS RECTIFIER_09_CURRENT
FROM data_json
WHERE ip IS NOT NULL
AND ip = $64::inet
AND "time" BETWEEN $65 AND $66
ORDER BY time
|
|
0 min
< 0.1%
|
0 ms
|
27,716
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%
|
2,977 ms
|
1
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
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,
(COALESCE(output_power_phase_a, $23) + COALESCE(output_power_phase_b, $24) + COALESCE(output_power_phase_c, $25)) AS output_power,
(COALESCE(input_power_phase_a, $26) + COALESCE(input_power_phase_b, $27) + COALESCE(input_power_phase_c, $28)) AS input_power,
(COALESCE(output_power_phase_a, $29) + COALESCE(output_power_phase_b, $30) + COALESCE(output_power_phase_c, $31)) -
(COALESCE(input_power_phase_a, $32) + COALESCE(input_power_phase_b, $33) + COALESCE(input_power_phase_c, $34)) AS delta_power,
CASE
WHEN (COALESCE(input_power_phase_a, $35) + COALESCE(input_power_phase_b, $36) + COALESCE(input_power_phase_c, $37)) > $38
THEN (
(COALESCE(output_power_phase_a, $39) + COALESCE(output_power_phase_b, $40) + COALESCE(output_power_phase_c, $41)) /
(COALESCE(input_power_phase_a, $42) + COALESCE(input_power_phase_b, $43) + COALESCE(input_power_phase_c, $44))
) * $45
ELSE $46
END AS efficiency
FROM power_calculations
ORDER BY ip, time DESC
|
|
0 min
< 0.1%
|
102 ms
|
29
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
|
|
0 min
< 0.1%
|
484 ms
|
6
sunrise_user
|
SELECT
time,
CASE
WHEN jsonb_extract_path_text(data, $1) ~ $2
THEN CAST(jsonb_extract_path_text(data, $3) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $4
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($5 /*, ... */)
), $6)
ELSE $7
END AS ac_phase_01_voltage,
CASE
WHEN jsonb_extract_path_text(data, $8) ~ $9
THEN CAST(jsonb_extract_path_text(data, $10) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $11
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($12 /*, ... */)
), $13)
ELSE $14
END AS ac_phase_02_voltage,
CASE
WHEN jsonb_extract_path_text(data, $15) ~ $16
THEN CAST(jsonb_extract_path_text(data, $17) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $18
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($19 /*, ... */)
), $20)
ELSE $21
END AS ac_phase_03_voltage,
CASE
WHEN jsonb_extract_path_text(data, $22) ~ $23
THEN CAST(jsonb_extract_path_text(data, $24) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $25
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($26 /*, ... */)
), $27)
ELSE $28
END AS ac_phase_01_power_factor,
CASE
WHEN jsonb_extract_path_text(data, $29) ~ $30
THEN CAST(jsonb_extract_path_text(data, $31) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $32
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($33 /*, ... */)
), $34)
ELSE $35
END AS ac_phase_02_power_factor,
CASE
WHEN jsonb_extract_path_text(data, $36) ~ $37
THEN CAST(jsonb_extract_path_text(data, $38) AS numeric) * COALESCE((
SELECT pndp.normalization_function
FROM part_number_data_points pndp
WHERE pndp.name = $39
AND pndp.part_number_id = data_json.part_number_id
AND pndp.data_type IN ($40 /*, ... */)
), $41)
ELSE $42
END AS ac_phase_03_power_factor
FROM data_json
WHERE ip IS NOT NULL
AND ip = $43::inet
AND "time" BETWEEN $44 AND $45
ORDER BY time
|
|
0 min
< 0.1%
|
31 ms
|
85
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
|
|
0 min
< 0.1%
|
15 ms
|
172
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 pc.title IN ($3 /*, ... */)
GROUP BY dh.start_hour_time
|
|
0 min
< 0.1%
|
33 ms
|
74
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 a.host_name IN ($9)
AND dj.time_unix >= EXTRACT($10 FROM (now() - INTERVAL $11))::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%
|
0 ms
|
24,980
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%
|
10 ms
|
230
sunrise_user
|
SELECT
dh.start_hour_time,
SUM(dh.ac_kwh_grid) 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 a.site_id = $3
AND a.host_name = $4
GROUP BY dh.start_hour_time
|
|
0 min
< 0.1%
|
14 ms
|
160
sunrise_user
|
SELECT
a.host_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 a.site_id = $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, pc.title
ORDER BY load_percentage DESC
|
|
0 min
< 0.1%
|
24 ms
|
85
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 = $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
|
|
0 min
< 0.1%
|
0 ms
|
24,980
sunrise_user
|
SELECT tlm.id, tlm.label, tlm.asset_qr, tlm.tenant1_dsid, tlm.tenant2_dsid, tlm.tenant3_dsid
FROM onboarding_transactions ot
JOIN telemetrylink_module tlm ON tlm.id = ot.equipment_id
WHERE ot.site_id = $1 AND ot.equipment_type = $2
ORDER BY ot.created_at DESC
|
|
0 min
< 0.1%
|
39 ms
|
47
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 = $7
),
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
)
SELECT
b.day::date AS day,
pc.title AS pc_title,
/* kWh */
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,
/* Load % */
CASE
WHEN pc.title = $11 THEN
(SUM(b.input_power) / $12)
/ NULLIF(gd.total_grid_kwh, $13) * $14
WHEN pc.title = $15 THEN
$16
ELSE
SUM(b.ac_kwh_ct)
/ NULLIF(gd.total_grid_kwh, $17) * $18
END AS load_percentage
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
LEFT JOIN ac_ct ac ON ac.asset_id = a.id
JOIN grid_per_day gd ON gd.day = b.day
WHERE
pc.title IN ($19 /*, ... */)
OR ac.asset_id IS NOT NULL
GROUP BY
b.day,
pc.title,
gd.total_grid_kwh
ORDER BY
day,
pc.title
|
|
0 min
< 0.1%
|
72 ms
|
25
sunrise_user
|
SELECT schemaname AS schema, relname AS table, indexrelname AS index, pg_relation_size(i.indexrelid) AS size_bytes, idx_scan as index_scans FROM pg_stat_user_indexes ui INNER JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan <= $1 ORDER BY pg_relation_size(i.indexrelid) DESC, relname ASC /*pghero*/
|
|
0 min
< 0.1%
|
25 ms
|
71
sunrise_user
|
WITH time_bounds AS (
SELECT
date_trunc($1::text, $2::timestamptz) AS day_start,
date_trunc($3::text, $4::timestamptz) + interval $5 AS day_end
),
grid_per_day AS (
SELECT
DATE(g.start_hour_time) AS day,
SUM(g.hour_grid_kwh) AS total_grid_kwh
FROM (
SELECT
dh.start_hour_time,
MAX(dh.ac_kwh_grid) AS hour_grid_kwh
FROM data_hour dh
CROSS JOIN time_bounds tb
WHERE dh.start_hour_time >= tb.day_start
AND dh.start_hour_time < tb.day_end
AND dh.site_id = $6
GROUP BY dh.start_hour_time
) g
GROUP BY DATE(g.start_hour_time)
)
SELECT
DATE(dh.start_hour_time) AS day,
pc.title AS pc_title,
CASE
WHEN pc.title = $7 THEN SUM(dh.ac_kwh_grid)
WHEN pc.title = $8 THEN SUM(dh.input_power) / $9
ELSE SUM(dh.ac_kwh_ct)
END AS kwh,
CASE
WHEN pc.title = $10 THEN
(SUM(dh.input_power) / $11)
/ NULLIF(gd.total_grid_kwh, $12) * $13
WHEN pc.title = $14 THEN
$15
ELSE
SUM(dh.ac_kwh_ct)
/ NULLIF(gd.total_grid_kwh, $16) * $17
END AS load_percentage
FROM data_hour dh
JOIN assets a ON a.id = dh.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
JOIN grid_per_day gd ON gd.day = DATE(dh.start_hour_time)
CROSS JOIN time_bounds tb
WHERE dh.start_hour_time >= tb.day_start
AND dh.start_hour_time < tb.day_end
AND a.site_id = $18
AND (
pc.title = $19
OR pc.title = $20
OR EXISTS (
SELECT $21
FROM ac_ct ac
WHERE ac.asset_id = a.id
)
)
GROUP BY
DATE(dh.start_hour_time),
pc.title,
gd.total_grid_kwh
ORDER BY
day,
pc.title
|
|
0 min
< 0.1%
|
0 ms
|
22,704
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, "dc_load_current" = $7, "ac_load_current_ct" = $8, "ac_voltage_grid" = $9, "ac_voltage_reftifier" = $10, "dc_voltage_rectifier" = $11, "input_power" = $12, "output_power" = $13, "efficiency" = $14, "humidity_top" = $15, "humidity_bottom" = $16, "humidity_delta" = $17 where "site_id" = $18 and "asset_id" = $19 and "start_hour_time" = $20
|
|
0 min
< 0.1%
|
434 ms
|
4
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
|
|
0 min
< 0.1%
|
232 ms
|
7
sunrise_user
|
SELECT
-- d.unix_time AS time,
date_trunc($1, to_timestamp(d.unix_time)) AS time,
d.type,
dn.dsid,
COALESCE((d.data->d.type->$2->$3)::text::integer, $4) AS temperature,
ct.port,
ct.asset_id,
ct.host_name,
ct.equipment_name,
ct.equipment_location,
ct.ct_value,
ct.ct_rating,
ct.ct_manufacturer,
ct.monitored_point,
ct.breaker_id,
ct.notes,
CONCAT(ct.host_name, $5, ct.equipment_name, $6, ct.breaker_id, $7, ct.monitored_point) AS ct_label
FROM data_snmp d
CROSS JOIN LATERAL (
SELECT trim(both $8 from (d.data->d.type->$9->$10)::text) AS dsid
) dn
JOIN tenant t
ON t.dsid = dn.dsid
-- Per-port CTs
LEFT JOIN ac_ct ac1 ON ac1.parent_tenant_id = t.id AND ac1.tenant_port = $11
LEFT JOIN ac_ct ac2 ON ac2.parent_tenant_id = t.id AND ac2.tenant_port = $12
LEFT JOIN ac_ct ac3 ON ac3.parent_tenant_id = t.id AND ac3.tenant_port = $13
LEFT JOIN ac_ct ac4 ON ac4.parent_tenant_id = t.id AND ac4.tenant_port = $14
LEFT JOIN ac_ct ac5 ON ac5.parent_tenant_id = t.id AND ac5.tenant_port = $15
LEFT JOIN ac_ct ac6 ON ac6.parent_tenant_id = t.id AND ac6.tenant_port = $16
-- Each CT has its own asset
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
-- 🔻 Unpivot CT1..CT6 into rows
CROSS JOIN LATERAL (
SELECT *
FROM (
VALUES
-- CT 1
(
$17,
CASE
WHEN ac1.ct_manufacturer ILIKE $18
THEN ((COALESCE((d.data->d.type->$19->$20)::text::float,$21)+$22)/$23)
* COALESCE(ac1.ct_factor,$24)
WHEN ac1.ct_manufacturer ILIKE $25
THEN ((COALESCE((d.data->d.type->$26->$27)::text::float,$28)+$29)*$30)
* COALESCE(ac1.ct_rating,$31) * COALESCE(ac1.ct_factor,$32)
ELSE COALESCE((d.data->d.type->$33->$34)::text::float,$35)
END,
ac1.ct_rating,
ac1.ct_manufacturer,
ac1.monitored_point,
ac1.breaker_id,
ac1.notes,
a1.id,
a1.host_name,
a1.equipment_name,
a1.equipment_location
),
-- CT 2
(
$36,
CASE
WHEN ac2.ct_manufacturer ILIKE $37
THEN ((COALESCE((d.data->d.type->$38->$39)::text::float,$40)+$41)/$42)
* COALESCE(ac2.ct_factor,$43)
WHEN ac2.ct_manufacturer ILIKE $44
THEN ((COALESCE((d.data->d.type->$45->$46)::text::float,$47)+$48)*$49)
* COALESCE(ac2.ct_rating,$50) * COALESCE(ac2.ct_factor,$51)
ELSE COALESCE((d.data->d.type->$52->$53)::text::float,$54)
END,
ac2.ct_rating,
ac2.ct_manufacturer,
ac2.monitored_point,
ac2.breaker_id,
ac2.notes,
a2.id,
a2.host_name,
a2.equipment_name,
a2.equipment_location
),
-- CT 3
(
$55,
CASE
WHEN ac3.ct_manufacturer ILIKE $56
THEN ((COALESCE((d.data->d.type->$57->$58)::text::float,$59)+$60)/$61)
* COALESCE(ac3.ct_factor,$62)
WHEN ac3.ct_manufacturer ILIKE $63
THEN ((COALESCE((d.data->d.type->$64->$65)::text::float,$66)+$67)*$68)
* COALESCE(ac3.ct_rating,$69) * COALESCE(ac3.ct_factor,$70)
ELSE COALESCE((d.data->d.type->$71->$72)::text::float,$73)
END,
ac3.ct_rating,
ac3.ct_manufacturer,
ac3.monitored_point,
ac3.breaker_id,
ac3.notes,
a3.id,
a3.host_name,
a3.equipment_name,
a3.equipment_location
),
-- CT 4
(
$74,
CASE
WHEN ac4.ct_manufacturer ILIKE $75
THEN ((COALESCE((d.data->d.type->$76->$77)::text::float,$78)+$79)/$80)
* COALESCE(ac4.ct_factor,$81)
WHEN ac4.ct_manufacturer ILIKE $82
THEN ((COALESCE((d.data->d.type->$83->$84)::text::float,$85)+$86)*$87)
* COALESCE(ac4.ct_rating,$88) * COALESCE(ac4.ct_factor,$89)
ELSE COALESCE((d.data->d.type->$90->$91)::text::float,$92)
END,
ac4.ct_rating,
ac4.ct_manufacturer,
ac4.monitored_point,
ac4.breaker_id,
ac4.notes,
a4.id,
a4.host_name,
a4.equipment_name,
a4.equipment_location
),
-- CT 5
(
$93,
CASE
WHEN ac5.ct_manufacturer ILIKE $94
THEN ((COALESCE((d.data->d.type->$95->$96)::text::float,$97)+$98)/$99)
* COALESCE(ac5.ct_factor,$100)
WHEN ac5.ct_manufacturer ILIKE $101
THEN ((COALESCE((d.data->d.type->$102->$103)::text::float,$104)+$105)*$106)
* COALESCE(ac5.ct_rating,$107) * COALESCE(ac5.ct_factor,$108)
ELSE COALESCE((d.data->d.type->$109->$110)::text::float,$111)
END,
ac5.ct_rating,
ac5.ct_manufacturer,
ac5.monitored_point,
ac5.breaker_id,
ac5.notes,
a5.id,
a5.host_name,
a5.equipment_name,
a5.equipment_location
),
-- CT 6
(
$112,
CASE
WHEN ac6.ct_manufacturer ILIKE $113
THEN ((COALESCE((d.data->d.type->$114->$115)::text::float,$116)+$117)/$118)
* COALESCE(ac6.ct_factor,$119)
WHEN ac6.ct_manufacturer ILIKE $120
THEN ((COALESCE((d.data->d.type->$121->$122)::text::float,$123)+$124)*$125)
* COALESCE(ac6.ct_factor,$126)
ELSE COALESCE((d.data->d.type->$127->$128)::text::float,$129)
END,
ac6.ct_rating,
ac6.ct_manufacturer,
ac6.monitored_point,
ac6.breaker_id,
ac6.notes,
a6.id,
a6.host_name,
a6.equipment_name,
a6.equipment_location
)
) AS x(
port,
ct_value,
ct_rating,
ct_manufacturer,
monitored_point,
breaker_id,
notes,
asset_id,
host_name,
equipment_name,
equipment_location
)
WHERE x.asset_id IS NOT NULL -- only existing CTs
) AS ct
-- WHERE d.rtu_id = 102101969
WHERE d.rtu_id IN (SELECT REPLACE( split_part(ip::text, $130, $131), $132, $133 )::bigint AS ip_number FROM corelink_node WHERE site_id = $134)
AND d.time BETWEEN $135 AND $136
-- AND (ct.asset_id = 65 OR 65 IS NULL) -- 👈 replace 108 with $asset_id in Grafana
AND ct.host_name = $137
ORDER BY d.time ASC, ct.port
|
|
0 min
< 0.1%
|
179 ms
|
9
sunrise_user
|
SELECT
date_trunc($1, to_timestamp(unix_time)) AS time,
(d.data->d.type->>$2)::float / $3 AS value,
a.host_name || $4 || trh.placement || $5 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 $6
AND d.type NOT ILIKE $7
-- AND a.id = 10
AND a.host_name = $8
-- AND a.host_name = 'fkaBRN007'
AND s.name = $9
AND "time" BETWEEN $10 AND $11
UNION ALL
SELECT
date_trunc($12, to_timestamp(unix_time)) AS time,
(d.data->d.type->>$13)::float / $14 AS value,
a.host_name || $15 || trh.placement || $16 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 $17
AND d.type NOT ILIKE $18
-- AND a.id = 14
AND a.host_name = $19
-- AND a.host_name = 'fkaBRN007'
AND s.name = $20
AND "time" BETWEEN $21 AND $22
ORDER BY time
|
|
0 min
< 0.1%
|
112 ms
|
14
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 ('AHU','Drycooler')
AND dj.time_unix >= EXTRACT($8 FROM (now() - INTERVAL $9))::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%
|
83 ms
|
18
sunrise_user
|
WITH latest_packets AS (
SELECT DISTINCT ON (data_json.ip)
data_json.ip,
data_json.time,
EXTRACT($1 FROM (CURRENT_TIMESTAMP - data_json.time)) + $2 AS time_ago_seconds,
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 $3 THEN $4
ELSE $5
END AS status,
COALESCE(assets.host_name, $6) AS 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
LEFT JOIN assets ON data_json.asset_id = assets.id
LEFT JOIN sites ON assets.site_id = sites.id
LEFT 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 data_json.ip = $7::inet
--AND (sites.name = '$site_filter' OR '$site_filter' = '')
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
)
SELECT
lp.ip,
lp.time,
lp.time_ago_seconds,
lp.asset_id,
lp.part_number_id,
lp.time_unix,
lp.type,
lp.status,
lp.host_name,
lp.floor,
lp.room,
lp.zone,
lp.site_name,
lp.site_address,
lp.cluster_id,
lp.part_number_name,
lp.part_number_short_name,
lp.part_number_description,
lp.category_title,
lp.category_short_name,
lp.subcategory_title,
lp.subcategory_short_name,
CASE
WHEN jsonb_extract_path_text(lp.data, keys.key) ~ $8
THEN (jsonb_extract_path_text(lp.data, keys.key)::float * COALESCE(pndp.normalization_function, $9))
ELSE $10
END AS normalized_value,
keys.key AS metric,
pndp.unit
FROM latest_packets lp
CROSS JOIN LATERAL (SELECT keys AS key FROM jsonb_object_keys(lp.data) AS keys) 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
ORDER BY lp.ip, lp.time DESC, keys.key
|
|
0 min
< 0.1%
|
10 ms
|
150
sunrise_user
|
SELECT
a.host_name,
dh.start_hour_time AS time,
dh.temperature_delta
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
WHERE dh.start_hour_time BETWEEN $1 AND $2
AND a.site_id = $3
AND a.host_name NOT IN ($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
)
ORDER BY dh.start_hour_time
|
|
0 min
< 0.1%
|
14 ms
|
103
sunrise_user
|
SELECT
relname AS "Name",
CASE relkind WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END AS "Engine",
pg_table_size(c.oid) AS "Data_length",
pg_indexes_size(c.oid) AS "Index_length",
obj_description(c.oid, $6) AS "Comment",
$7 AS "Oid",
reltuples AS "Rows",
relispartition::int AS partition,
current_schema() AS nspname
FROM pg_class c
WHERE relkind IN ($8 /*, ... */)
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema())
ORDER BY relname
|
|
0 min
< 0.1%
|
0 ms
|
615,328
sunrise_user
|
BEGIN
|
|
0 min
< 0.1%
|
1 ms
|
1,319
sunrise_user
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typtype IN ($1 /*, ... */)
|
|
0 min
< 0.1%
|
0 ms
|
9,336
sunrise_user
|
insert into "data_hour" ("site_id", "asset_id", "start_hour_time", "ac_kwh_grid", "ac_kwh_ct", "dc_kwh", "temperature_top", "temperature_bottom", "temperature_delta", "dc_load_current", "ac_load_current_ct", "ac_voltage_grid", "ac_voltage_reftifier", "dc_voltage_rectifier", "input_power", "output_power", "efficiency", "humidity_top", "humidity_bottom", "humidity_delta") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20)
|
|
0 min
< 0.1%
|
8 ms
|
175
sunrise_user
|
SELECT
dh.start_hour_time AS time,
-- dh.ac_kwh_grid, dh.dc_kwh
SUM(dh.ac_kwh_ct) AS rectifier_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 a.host_name IN ('fngBRN701')
AND pc.title IN ($3)
GROUP BY dh.start_hour_time
|
|
0 min
< 0.1%
|
50 ms
|
25
sunrise_user
|
SELECT pg_database_size(current_database()) /*pghero*/
|
|
0 min
< 0.1%
|
1 ms
|
1,319
sunrise_user
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typelem IN ($1 /*, ... */)
|
|
0 min
< 0.1%
|
14 ms
|
86
sunrise_user
|
SELECT
a.host_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 a.site_id = $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, pc.title
ORDER BY kwh DESC
|
|
0 min
< 0.1%
|
33 ms
|
36
sunrise_user
|
SELECT
a.host_name,
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 $1 AND $2
AND pc.title IN ($3 /*, ... */)
GROUP BY a.host_name, dh.start_hour_time
|
|
0 min
< 0.1%
|
19 ms
|
61
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
|
|
0 min
< 0.1%
|
3 ms
|
346
sunrise_user
|
SELECT
dh.start_hour_time AS time,
dh.ac_kwh_ct, dh.temperature_top, dh.temperature_bottom, dh.temperature_delta, dh.humidity_top, dh.humidity_bottom, dh.humidity_delta, (dh.ac_kwh_ct * $1) AS CHF
FROM data_hour dh
JOIN assets a ON a.id = dh.asset_id
JOIN sites s ON s.id = dh.site_id
WHERE dh.start_hour_time BETWEEN $2 AND $3
AND a.host_name IN ($4)
AND a.site_id = $5
AND EXISTS (
SELECT $6
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
|
|
0 min
< 0.1%
|
1 ms
|
1,319
sunrise_user
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typname IN ($1 /*, ... */)
|