DrMichael
commented
5 months ago Could you post the SQL instead of the json please?
Closed andreadam82 closed 4 months ago
DrMichael
commented
5 months ago Could you post the SQL instead of the json please?
andreadam82
commented
5 months ago SELECT start_date AS "Start", end_date AS "End", ROUND(EXTRACT(EPOCH FROM start_date))1000 AS start_date_ts, ROUND(EXTRACT(EPOCH FROM end_date))1000 AS end_date_ts, '🚗 Driving' AS "Action", drives.duration_min AS "Duration", CASE WHEN start_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude) WHEN start_geofence_id IS NOT NULL THEN CONCAT(start_geofence_id, '/edit') END AS start_path, CASE WHEN end_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude) WHEN start_geofence_id IS NOT NULL THEN CONCAT(end_geofence_id, '/edit') END AS end_path, COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address", COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address", convert_km(end_km::NUMERIC, '$lengthunit') AS odometer$length_unit, convert_km(distance::NUMERIC, '$lengthunit') AS distance$length_unit, convertkm(end[[preferred_range]]_range_km::NUMERIC, '$length_unit') AS endrange$length_unit, convertkm((end[[preferred_range]]_rangekm - start[[preferred_range]]_range_km)::NUMERIC, '$length_unit') efficiency AS "kWh", convertkm((end[[preferred_range]]_rangekm - start[[preferred_range]]_range_km)::NUMERIC, '$length_unit') AS rangediff$length_unit, TP2.battery_level AS "SoC", TP2.battery_level-TP1.battery_level AS "SoC Diff", convert_celsius(outside_temp_avg, '$temp_unit') AS outside_tempavg$temp_unit, CONCAT('d/zm7wN6Zgz/drive-details?from=', ROUND(EXTRACT(EPOCH FROM start_date))1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date))*1000, '&var-car_id=', drives.car_id, '&var-drive_id=', drives.id) AS slotlink FROM drives LEFT OUTER JOIN positions AS TP1 on drives.start_position_id = TP1.id LEFT OUTER JOIN positions AS TP2 on drives.end_position_id = TP2.id LEFT JOIN addresses start_address ON start_address_id = start_address.id LEFT JOIN addresses end_address ON end_address_id = end_address.id LEFT JOIN geofences start_geofence ON start_geofence_id = start_geofence.id LEFT JOIN geofences end_geofence ON end_geofence_id = end_geofence.id JOIN cars ON cars.id = drives.car_id WHERE $__timeFilter(drives.start_date) AND drives.car_id = $car_id AND '🚗 Driving' in ($action_filter) AND (COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT like '%$text_filter%' or COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city))::TEXT like '%$text_filter%')
UNION SELECT start_date AS "Start", end_date AS "End", ROUND(EXTRACT(EPOCH FROM start_date))1000 AS start_date_ts, ROUND(EXTRACT(EPOCH FROM end_date))1000 AS end_date_ts, '🔋 Charging' AS "Action", charging_processes.duration_min AS "Duration", CASE WHEN geofence_id IS NULL THEN CONCAT('new?lat=', address.latitude, '&lng=', address.longitude) WHEN geofence_id IS NOT NULL THEN CONCAT(geofence_id, '/edit') END AS start_path, NULL AS end_path, COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address", '' AS "End Address", convert_km(position.odometer::NUMERIC, '$lengthunit') AS odometer$lengthunit, NULL AS distance$length_unit, convertkm(end[[preferred_range]]_range_km::NUMERIC, '$length_unit') AS endrange$length_unit, charging_processes.charge_energy_added AS "kWh", convertkm((end[[preferred_range]]_rangekm - start[[preferred_range]]_range_km)::NUMERIC, '$length_unit') AS rangediff$length_unit, end_battery_level AS "SoC", end_battery_level - start_battery_level AS "SoC Diff", convert_celsius(outside_temp_avg, '$temp_unit') AS outside_tempavg$temp_unit, CONCAT('d/BHhxFeZRz/charge-details?from=', ROUND(EXTRACT(EPOCH FROM start_date)-10)1000, '&to=', ROUND(EXTRACT(EPOCH FROM end_date)+10)1000, '&var-car_id=', charging_processes.car_id, '&var-charging_process_id=', charging_processes.id) AS slotlink FROM charging_processes INNER JOIN positions AS position ON position_id = position.id LEFT JOIN addresses address ON address_id = address.id LEFT JOIN geofences geofence ON geofence_id = geofence.id WHERE $timeFilter(charging_processes.start_date) AND charging_processes.charge_energy_added > 0 AND charging_processes.car_id = $car_id AND '🔋 Charging' in ($action_filter) AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT like '%$text_filter%' UNION SELECT d.end_date AS "Start", LEAD(d.start_date) over w AS "End", ROUND(EXTRACT(EPOCH FROM d.end_date)) 1000 AS start_date_ts, ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))1000 AS end_date_ts, '🅿️ Parking' AS "Action", EXTRACT(EPOCH FROM LEAD(d.start_date) over w - d.end_date)/60 AS "Duration", CASE WHEN d.end_geofence_id IS NULL THEN CONCAT('new?lat=', end_position.latitude, '&lng=', end_position.longitude) WHEN d.end_geofence_id IS NOT NULL THEN CONCAT(d.end_geofence_id, '/edit') END AS start_path, NULL AS end_path, COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city)) AS "Start Address", '' AS "End Address", convert_km(end_position.odometer::NUMERIC, '$lengthunit') AS odometer$lengthunit, NULL AS distance$length_unit, convertkm(LEAD(d.start[[preferred_range]]_range_km) over w::NUMERIC, '$length_unit') AS endrange$length_unit, convertkm(((LEAD(d.start[[preferred_range]]_range_km) over w + (LEAD(d.start_km) over w - d.endkm)) - d.end[[preferred_range]]_range_km)::NUMERIC, '$length_unit') efficiency AS "kWh", convertkm(((LEAD(d.start[[preferred_range]]_range_km) over w + (LEAD(d.start_km) over w - d.endkm)) - d.end[[preferred_range]]_range_km)::NUMERIC, '$length_unit') AS rangediff$length_unit, LEAD(start_position.battery_level) over w AS "SoC", LEAD(start_position.battery_level) over w - end_position.battery_level AS "SoC Diff", convert_celsius(outside_temp_avg, '$temp_unit') AS outside_tempavg$temp_unit, CONCAT('d/FkUpJpQZk/trip?from=', ROUND(EXTRACT(EPOCH FROM d.end_date))1000, '&to=', ROUND(EXTRACT(EPOCH FROM LEAD(d.start_date) over w))*1000, '&var-car_id=', d.car_id) AS slotlink FROM drives AS d LEFT OUTER JOIN positions start_position on d.start_position_id = start_position.id LEFT OUTER JOIN positions end_position on d.end_position_id = end_position.id LEFT JOIN addresses address ON d.end_address_id = address.id LEFT JOIN geofences geofence ON d.end_geofence_id = geofence.id JOIN cars ON cars.id = d.car_id WHERE $timeFilter(d.end_date) AND d.car_id=$car_id AND '🅿️ Parking' in ($action_filter) AND COALESCE(geofence.name, CONCAT_WS(', ', COALESCE(address.name, nullif(CONCAT_WS(' ', address.road, address.house_number), '')), address.city))::TEXT like '%$text_filter%' WINDOW w as (ORDER BY d.id ASC)
UNION SELECT T1.end_date +(1 interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row T2.start_date AS "End", ROUND(EXTRACT(EPOCH FROM T2.start_date)) 1000 - 1 AS start_date_ts, ROUND(EXTRACT(EPOCH FROM T2.start_date)) 1000 - 1 AS end_date_ts, '❓ Missing' AS "Action", -- EXTRACT(EPOCH FROM T2.start_date - T1.end_date)/60 AS "Duration", NULL AS "Duration", CASE WHEN T1.end_geofence_id IS NULL THEN CONCAT('new?lat=', TP1.latitude, '&lng=', TP1.longitude) WHEN T1.end_geofence_id IS NOT NULL THEN CONCAT(T1.end_geofence_id, '/edit') END AS start_path, CASE WHEN T2.start_geofence_id IS NULL THEN CONCAT('new?lat=', TP2.latitude, '&lng=', TP2.longitude) WHEN T2.start_geofence_id IS NOT NULL THEN CONCAT(T2.start_geofence_id, '/edit') END AS end_path, COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city)) AS "Start Address", COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)) AS "End Address", convert_km(TP2.odometer::INTEGER, '$lengthunit') AS odometer$length_unit, convert_km((TP2.odometer - TP1.odometer)::INTEGER, '$lengthunit') AS distance$length_unit, convertkm(T2.end[[preferred_range]]_range_km::NUMERIC, '$length_unit') AS endrange$length_unit, convert_km(((TP2.[[preferred_range]]_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.[[preferred_range]]_battery_range_km)::INTEGER, '$length_unit')::INTEGER efficiency AS "kWh", convert_km(((TP2.[[preferred_range]]_battery_range_km + (TP2.odometer - TP1.odometer)) - TP1.[[preferred_range]]_battery_range_km)::INTEGER, '$length_unit') AS rangediff$length_unit, NULL AS "SoC", NULL AS "SoC Diff", NULL AS outside_tempavg$temp_unit, NULL AS slotlink -- TP2.battery_level AS "SoC", -- TP2.battery_level-TP1.battery_level AS "SoC Diff", -- (T1.outside_temp_avg+T2.outside_temp_avg)/2 AS outside_tempavg$temp_unit FROM (SELECT row_number() over(ORDER BY start_date) AS time_id, FROM drives) AS T1 LEFT OUTER JOIN (SELECT row_number() over(ORDER BY start_date) AS time_id, FROM drives ) AS T2 on T1.time_id + 1 = T2.time_id LEFT OUTER JOIN positions AS TP1 on T1.end_position_id = TP1.id LEFT OUTER JOIN positions AS TP2 on T2.start_position_id = TP2.id LEFT JOIN addresses start_address ON T1.end_address_id = start_address.id LEFT JOIN addresses end_address ON T2.start_address_id = end_address.id LEFT JOIN geofences start_geofence ON T1.end_geofence_id = start_geofence.id LEFT JOIN geofences end_geofence ON T2.start_geofence_id = end_geofence.id JOIN cars ON cars.id = T2.car_id WHERE $__timeFilter(T1.end_date) AND T1.car_id=$car_id -- AND T2.car_id=$car_id -- AND TP2.car_id=$car_id -- AND TP1.car_id=$car_id AND TP2.odometer - TP1.odometer > 0.5 AND T1.end_address_id <> T2.start_address_id AND ((T1.end_geofence_id IS NOT NULL OR T2.end_geofence_id IS NOT NULL) OR T1.end_geofence_id <> T2.start_geofence_id) AND '❓ Missing' in ($action_filter) AND ( (COALESCE(start_geofence.name, CONCAT_WS(', ', COALESCE(start_address.name, nullif(CONCAT_WS(' ', start_address.road, start_address.house_number), '')), start_address.city))::TEXT like '%$text_filter%') or (COALESCE(end_geofence.name, CONCAT_WS(', ', COALESCE(end_address.name, nullif(CONCAT_WS(' ', end_address.road, end_address.house_number), '')), end_address.city)))::TEXT like '%$text_filter%') UNION SELECT start_date AS "Start", end_date AS "End", ROUND(EXTRACT(EPOCH FROM start_date))1000 AS start_date_ts, ROUND(EXTRACT(EPOCH FROM end_date))1000 AS end_date_ts, '💾 Updating' AS "Action", EXTRACT(EPOCH FROM end_date - start_date)/60 AS "Duration", NULL AS start_path, NULL AS endpath, version AS "Start Address", '' AS "End Address", NULL AS odometer$lengthunit, NULL AS distance$length_unit, NULL AS endrange$length_unit, NULL AS "kWh", NULL AS rangediff$length_unit, NULL AS "SoC", NULL AS "SoC Diff", NULL AS outside_tempavg$temp_unit, CONCAT('https://www.notateslaapp.com/software-updates/version/', split_part(version, ' ', 1), '/release-notes') AS slotlink FROM updates WHERE $__timeFilter(start_date) AND car_id = $car_id AND '💾 Updating' in ($action_filter) AND version::TEXT like '%$text_filter%'
ORDER BY "Start" DESC;
DrMichael
commented
5 months ago Ah, I see. You took out
-- AND T2.car_id=$car_id
-- AND TP2.car_id=$car_id
-- AND TP1.car_id=$car_idfrom action Missing... Not sure, what the impact is, when you have several cars in the account...
andreadam82
commented
5 months ago I have two cars in my account (my old Model 3 and my new Model Y) and with this fix I had no problem ...
github-actions[bot]
commented
4 months ago This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 7 days if no further activity occurs. Thank you for your contributions.
JakobLichterfeld
commented
4 months ago @DrMichael, @swiffer and @jheredianet what do you think of the above suggested changes to the timeline dashboard?
swiffer
commented
4 months ago I think that subquery can be improved way more - will have a look over the weekend! - two cars within my db but no ? Missing Action so far. Will most likely fake some entries while testing. Will ping @andreadam82 for validation once I found an elegant way.
jheredianet
commented
4 months ago I think that subquery can be improved way more - will have a look over the weekend! - two cars within my db but no
? MissingAction so far. Will most likely fake some entries while testing. Will ping @andreadam82 for validation once I found an elegant way.
Feel free to ping me as well if you want to test. I have 3 cars on the same database with more than 3 years of data.
swiffer
commented
4 months ago ok - from the analysis that i've done so far - you could comment out the filter cond. for TP1 & TP2 - commenting out T2 can result in query differences if I've understood the query correctly.
AND T2.car_id=$car_id
-- AND TP2.car_id=$car_id
-- AND TP1.car_id=$car_idbesides that i think there is definitly one error in the query
current:
AND T1.end_address_id <> T2.start_address_id AND ((T1.end_geofence_id IS NOT NULL OR T2.end_geofence_id IS NOT NULL) OR T1.end_geofence_id <> T2.start_geofence_id)fixed:
AND T1.end_address_id <> T2.start_address_id AND ((T1.end_geofence_id IS NOT NULL OR T2.start_geofence_id IS NOT NULL) OR T1.end_geofence_id <> T2.start_geofence_id)@jheredianet / @andreadam82 - could you please run the following queries and tell me execution time for all? (filtered for car_id = 2 - please change to your needs!)
taking all nearly equally long on my instance but only 3 months of data so far...
Query 1
SELECT
T1.end_date +(1 * interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row
T2.start_date AS "End"
FROM
(SELECT row_number() over(partition by car_id ORDER BY start_date) AS time_id, * FROM drives) AS T1
LEFT OUTER JOIN (SELECT row_number() over(partition by car_id ORDER BY start_date) AS time_id, * FROM drives ) AS T2 on T1.time_id + 1 = T2.time_id and T1.car_id = T2.car_id
LEFT OUTER JOIN positions AS TP1 on T1.end_position_id = TP1.id
LEFT OUTER JOIN positions AS TP2 on T2.start_position_id = TP2.id
LEFT JOIN addresses start_address ON T1.end_address_id = start_address.id
LEFT JOIN addresses end_address ON T2.start_address_id = end_address.id
LEFT JOIN geofences start_geofence ON T1.end_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON T2.start_geofence_id = end_geofence.id
WHERE
T1.car_id= 2
-- AND T2.car_id= 2
-- AND TP2.car_id= 2
-- AND TP1.car_id= 2
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND ((T1.end_geofence_id IS NOT NULL OR T2.start_geofence_id IS NOT NULL) OR T1.end_geofence_id <> T2.start_geofence_id)Query 2
SELECT
T1.end_date +(1 * interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row
T2.start_date AS "End"
FROM
(SELECT row_number() over(ORDER BY start_date) AS time_id, * FROM drives) AS T1
LEFT OUTER JOIN (SELECT row_number() over(ORDER BY start_date) AS time_id, * FROM drives ) AS T2 on T1.time_id + 1 = T2.time_id
LEFT OUTER JOIN positions AS TP1 on T1.end_position_id = TP1.id
LEFT OUTER JOIN positions AS TP2 on T2.start_position_id = TP2.id
LEFT JOIN addresses start_address ON T1.end_address_id = start_address.id
LEFT JOIN addresses end_address ON T2.start_address_id = end_address.id
LEFT JOIN geofences start_geofence ON T1.end_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON T2.start_geofence_id = end_geofence.id
WHERE
T1.car_id= 2
AND T2.car_id= 2
-- AND TP2.car_id= 2
-- AND TP1.car_id= 2
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND ((T1.end_geofence_id IS NOT NULL OR T2.start_geofence_id IS NOT NULL) OR T1.end_geofence_id <> T2.start_geofence_id)Query 3
SELECT
T1.end_date +(1 * interval '1 second') AS "Start", -- added 1 sec to get it after the corresponding Parking row
T2.start_date AS "End"
FROM
(SELECT row_number() over(ORDER BY start_date) AS time_id, * FROM drives) AS T1
LEFT OUTER JOIN (SELECT row_number() over(ORDER BY start_date) AS time_id, * FROM drives ) AS T2 on T1.time_id + 1 = T2.time_id
LEFT OUTER JOIN positions AS TP1 on T1.end_position_id = TP1.id
LEFT OUTER JOIN positions AS TP2 on T2.start_position_id = TP2.id
LEFT JOIN addresses start_address ON T1.end_address_id = start_address.id
LEFT JOIN addresses end_address ON T2.start_address_id = end_address.id
LEFT JOIN geofences start_geofence ON T1.end_geofence_id = start_geofence.id
LEFT JOIN geofences end_geofence ON T2.start_geofence_id = end_geofence.id
WHERE
T1.car_id= 2
AND T2.car_id= 2
AND TP2.car_id= 2
AND TP1.car_id= 2
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND ((T1.end_geofence_id IS NOT NULL OR T2.start_geofence_id IS NOT NULL) OR T1.end_geofence_id <> T2.start_geofence_id)Query 4
with drives_detect_missing as (
select
id,
lag(id) over (partition by car_id order by start_date) as previous_id,
start_geofence_id,
lag(end_geofence_id) over (partition by car_id order by start_date) as previous_end_geofence_id,
start_address_id,
lag(end_address_id) over (partition by car_id order by start_date) as previous_end_address_id,
start_position_id,
lag(end_position_id) over (partition by car_id order by start_date) as previous_end_position_id
from drives d
where d.car_id = 2
)
select
drives_detect_missing.*
from drives_detect_missing
inner join positions positions_start on positions_start.id = drives_detect_missing.start_position_id
inner join positions positions_previous_end on positions_previous_end.id = drives_detect_missing.previous_end_position_id
where
previous_id is not null
and previous_end_address_id <> start_address_id AND ((start_geofence_id IS NOT NULL OR previous_end_geofence_id IS NOT NULL) OR (start_geofence_id <> previous_end_geofence_id))
and positions_start.odometer - positions_previous_end.odometer > 0.5Query 5
with drives_detect_missing as (
select
d.*,
lag(id) over (partition by car_id order by start_date) as previous_id
from drives d
where d.car_id = 2
)
select
drives_detect_missing.*
from drives_detect_missing
inner join drives d on drives_detect_missing.previous_id = d.id
inner join positions positions_start on positions_start.id = drives_detect_missing.start_position_id
inner join positions positions_previous_end on positions_previous_end.id = d.end_position_id
where
d.end_address_id <> drives_detect_missing.start_address_id AND ((drives_detect_missing.start_geofence_id IS NOT NULL OR d.end_geofence_id IS NOT NULL) OR (drives_detect_missing.start_geofence_id <> d.end_geofence_id))
and positions_start.odometer - positions_previous_end.odometer > 0.5Query 6
SELECT
*
FROM
(SELECT row_number() over(ORDER BY start_date) AS time_id, * FROM drives) AS T1
LEFT OUTER JOIN (SELECT row_number() over(ORDER BY start_date) AS time_id, * FROM drives ) AS T2 on T1.time_id + 1 = T2.time_id
LEFT OUTER JOIN positions AS TP1 on T1.end_position_id = TP1.id
LEFT OUTER JOIN positions AS TP2 on T2.start_position_id = TP2.id
WHERE
T1.car_id=2
AND T2.car_id=2
AND TP2.car_id=2
AND TP1.car_id=2
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND (
(coalesce(T1.end_geofence_id, 0) <> coalesce(T2.start_geofence_id, 0))
or (T1.end_geofence_id is null and T2.start_geofence_id is null)
)Query 7
SELECT
*
FROM
(SELECT row_number() over(ORDER BY id) AS time_id, * FROM drives) AS T1
LEFT OUTER JOIN (SELECT row_number() over(ORDER BY id) AS time_id, * FROM drives ) AS T2 on T1.time_id + 1 = T2.time_id
LEFT OUTER JOIN positions AS TP1 on T1.end_position_id = TP1.id
LEFT OUTER JOIN positions AS TP2 on T2.start_position_id = TP2.id
WHERE
T1.car_id= '2'
AND T2.car_id= '2'
AND TP2.car_id= '2'
AND TP1.car_id= '2'
AND TP2.odometer - TP1.odometer > 0.5
AND T1.end_address_id <> T2.start_address_id AND (
(coalesce(T1.end_geofence_id, 0) <> coalesce(T2.start_geofence_id, 0))
or (T1.end_geofence_id is null and T2.start_geofence_id is null)
)Query 8
with drives_detect_missing as (
select
d.*,
lag(id) over (partition by car_id order by id) as previous_id
from drives d
where d.car_id = 2
)
select
drives_detect_missing.*
from drives_detect_missing
inner join drives d on drives_detect_missing.previous_id = d.id
inner join positions positions_start on positions_start.id = drives_detect_missing.start_position_id
inner join positions positions_previous_end on positions_previous_end.id = d.end_position_id
where
d.end_address_id <> drives_detect_missing.start_address_id AND ((drives_detect_missing.start_geofence_id IS NULL and d.end_geofence_id IS NULL) OR coalesce(drives_detect_missing.start_geofence_id, 0) <> coalesce(d.end_geofence_id, 0))
and positions_start.odometer - positions_previous_end.odometer > 0.5@swiffer Here are the results: (My data from March 2022 until now, over 11,811,476 records on positions table) TeslaMate running o a Cloud Server: 4 vCPU, 4GB RAM, SSD The query was run from local PC (Home) against the PostgreSQL v16 (on cloud)
1 day after (with 1 charges and some additional drives)
swiffer
commented
4 months ago Looks like a great improvement 👍 @DrMichael / @JakobLichterfeld could you maybe run the queries as well?
Please include Postgres version and test in bottom up order. @jheredianet - what PG version are you running on?
swiffer
commented
4 months ago diving into that seeing more issues within the query also leading to missing entries
i think the geofence condition needs to be:
AND T1.end_address_id <> T2.start_address_id AND (
(coalesce(T1.end_geofence_id, 0) <> coalesce(T2.start_geofence_id, 0))
or (T1.end_geofence_id is null and T2.start_geofence_id is null)
)in the query select part why is T2.start_date used as start_date and end_date - doesn't make sense either, right?
ROUND(EXTRACT(EPOCH FROM T2.start_date)) * 1000 - 1 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM T2.start_date)) * 1000 - 1 AS end_date_ts,shouldn't it be
ROUND(EXTRACT(EPOCH FROM T1.end_date)) * 1000 - 1 AS start_date_ts,
ROUND(EXTRACT(EPOCH FROM T2.start_date)) * 1000 - 1 AS end_date_ts,Looks like a great improvement 👍 @DrMichael / @JakobLichterfeld could you maybe run the queries as well?
Please include Postgres version and test in bottom up order. @jheredianet - what PG version are you running on?
I’ve edited my previous post with the additional info y are asking for.
swiffer
commented
4 months ago Thanks @jheredianet - how many drives do you have?
900 in my instance (32gb, 20 cpu, nvme, pg16) and all queries take equally long.
i've added queries 6-8, could you run these as well (and thanks for your effort!). It might returns additional results - if yes, please check if these look valid for you. they should.
DrMichael
commented
4 months ago Looks like a great improvement 👍 @DrMichael / @JakobLichterfeld could you maybe run the queries as well?
Ok, Postgres 16 running on RPiB3+, ARMv7 Processor rev 4 (v7l), 1GB: Query 1: Cost: 234.024 ms - Total 96 records Query 2: Cost: 177.335 ms - Total 96 records Query 3: Cost: 325.292 ms - Total 96 records Query 4: Cost: 91.687 ms - Total 96 records Query 5: Cost: 94.968 ms - Total 96 records Query 6: Cost: 488.475 ms - Total 124 records Query 7: Cost: 227.255 ms - Total 124 records Query 8: Cost: 98.128 ms - Total 124 records
Postgres 16 running on Ubuntu, Intel(R) Celeron(R) N5100 @ 1.10GHz, 8GB Query 1: Cost: 34.009 ms - Total 96 records Query 2: Cost: 58.707 ms - Total 96 records Query 3: Cost: 61.723 ms - Total 96 records Query 4: Cost: 10.817 ms - Total 96 records Query 5: Cost: 12.984 ms - Total 96 records Query 6: Cost: 81.117 ms - Total 124 records Query 7: Cost: 63.648 ms - Total 124 records Query 8: Cost: 20.441 ms - Total 124 records
All run locally on the box with psql. Not sure, where the different row numbers are coming from.
jheredianet
commented
4 months ago Thanks @jheredianet - how many drives do you have? 900 in my instance (32gb, 20 cpu, nvme, pg16) and all queries take equally long. i've added queries 6-8, could you run these as well (and thanks for your effort!). It might returns additional results - if yes, please check if these look valid for you. they should.
Here it is: (Just 1 car, data since March 2022 until now)
BTW, I've updated my previous post with query 6-8
swiffer
commented
4 months ago Ok, I think we will go with query 8, will open a PR later. Any other queries to speed up? ,🤓
JakobLichterfeld
commented
4 months ago Looks like a great improvement 👍 @DrMichael / @JakobLichterfeld could you maybe run the queries as well?
Please include Postgres version and test in bottom up order. @jheredianet - what PG version are you running on?
Postgres16 running on Raspberry Pi Model 3 B, executed via ssh and docker compose exec database psql teslamate teslamate with \timing on and car_id = 1
Query 1: Time: 163.651 ms - 31 rows Query 2: Time: 166.243 ms - 31 rows Query 3: Time: 123.651 ms - 31 rows Query 4: Time: 69.009 ms - 31 rows Query 5: Time: 89.676 ms - 31 rows Query 6: Time: 232.832 ms - 38 rows Query 7: Time: 215.058 ms - 38 rows Query 8: Time: 71.971 ms - 38 rows
Tested Query 1 to Query 8 (NOT bottom up order :-))
DrMichael
commented
4 months ago Ok, I think we will go with query 8, will open a PR later. Any other queries to speed up? ,🤓
Well, in Overview, Driver Temp, Outside Temp and Inside Temp feel a bit slow... 2,2 seconds on Ubuntu and 74,4 on the Raspberry.
andreadam82
commented
4 months ago Query 1: Successfully run. Total query runtime: 292 msec. 6 rows affected. Query 2: Successfully run. Total query runtime: 440 msec. 6 rows affected. Query 3: ... I stopped the execution, it had exceeded 1 minute Query 4: Successfully run. Total query runtime: 394 msec. 6 rows affected. Query 5: Successfully run. Total query runtime: 351 msec. 6 rows affected. Query 6: ... I stopped the execution, it had exceeded 1 minute Query 7: ... I stopped the execution, it had exceeded 1 minute Query 8: Successfully run. Total query runtime: 321 msec. 6 rows affected.
swiffer
commented
4 months ago 👍 - thanks @andreadam82 - you can try running the dashboard provided in #4019 by using the corresponding grafana image:
docker pull ghcr.io/teslamate-org/teslamate/grafana:pr-4019
Is there an existing issue for this?
What happened?
The timeline report returns error 502 because the query execution fails. I corrected the query and shared the json for grafana in the log section below. With this modification the report is much faster.
Expected Behavior
No response
Steps To Reproduce
No response
Relevant log output
Screenshots
Additional data
No response
Type of installation
Docker
Version
v1.29.1