Comments (10)
Took a Look already - it Scans positions and charges for 10 years. I guess this can be simplified without big sacrifice in data accuracy. Will come up with an alternative query, let's see how much they differ.
from teslamate.
I do not see much benefit in number of charges and range anyways in the update dashboard.
Same
from teslamate.
Could you run an explain analyze on the slow query and add it here? Guess my data is to small or the compute power to big 😀
from teslamate.
:-D Yeah for sure. Just need to figure out the single query.
or the compute power to big
Did not spot it on my Mac Studio M2 Max, but on my Raspberry Pi 3 B and I know the updates are stored in separate table, therefore the time needed does not make sense to me.
Number of Updates and Median time between updates are fast (80-90ms)
But the third query does take 42 seconds:
Query:
teslamate/grafana/dashboards/updates.json
Line 630 in d2a1371
from teslamate.
@JakobLichterfeld - can you run this query (once with explain analyze and once without to compare data outcome vs current dashboard result)
with u as (
select *, coalesce(lag(start_date) over(order by start_date desc), now()) as next_start_date
from updates
where car_id = '2' and start_date BETWEEN '2014-07-04T12:37:24.227Z' AND '2024-07-04T12:37:24.227Z'
),
rng as (
SELECT
date_trunc('hour', date) AS date,
(sum(rated_battery_range_km) / nullif(sum(usable_battery_level),0) * 100 ) AS "battery_rng",
sum(case when action = 'Charge' then 1 else 0 end) as chg_ct
FROM (
select coalesce(usable_battery_level, battery_level) as usable_battery_level, start_date as date, start_rated_range_km as rated_battery_range_km, start_ideal_range_km as ideal_battery_range_km, 'Drive' as action
from drives d
inner join positions p on d.start_position_id = p.id
where d.car_id = '2' and d.start_date BETWEEN '2014-07-04T12:37:24.227Z' AND '2024-07-04T12:37:24.227Z'
union all
select end_battery_level as usable_battery_level, end_date, end_rated_range_km as rated_battery_range_km, end_ideal_range_km as ideal_battery_range_km, 'Charge' as action
from charging_processes p
where p.car_id = '2' and end_date BETWEEN '2014-07-04T12:37:24.227Z' AND '2024-07-04T12:37:24.227Z'
) as data
GROUP BY 1
)
select
u.start_date as time,
extract(EPOCH FROM u.end_date - u.start_date) AS update_duration,
extract(EPOCH FROM u.start_date - lag(u.start_date) OVER (ORDER BY u.start_date)) AS since_last_update,
split_part(u.version, ' ', 1) as version,
sum(r.chg_ct) as chg_ct,
convert_km(avg(r.battery_rng), 'km')::numeric(6,2) AS avg_rated_range_km
from u u
left join rng r
ON r.date between u.start_date and u.next_start_date
group by u.car_id,
u.start_date,
u.end_date,
next_start_date,
split_part(u.version, ' ', 1)
from teslamate.
@JakobLichterfeld - can you run this query (once with explain analyze and once without to compare data outcome vs current dashboard result)
The query with card_id = '1'
returns 136 rows for me and runs 420.269 ms
With EXPLAIN ANALYZE it takes 545.820 ms to return 36 rows ?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=17237.84..17242.26 rows=136 width=168) (actual time=543.859..545.200 rows=136 loops=1)
-> Sort (cost=17237.84..17238.18 rows=136 width=122) (actual time=543.817..543.898 rows=136 loops=1)
Sort Key: updates.start_date
Sort Method: quicksort Memory: 28kB
-> HashAggregate (cost=17230.64..17233.02 rows=136 width=122) (actual time=542.824..543.554 rows=136 loops=1)
Group Key: updates.car_id, updates.start_date, updates.end_date, (COALESCE((lag(updates.start_date) OVER (?))::timestamp with time zone, now())), split_part((updates.version)::text, ' '::text, 1)
Batches: 1 Memory Usage: 88kB
-> Nested Loop Left Join (cost=16643.72..17177.76 rows=3022 width=98) (actual time=144.089..534.579 rows=2005 loops=1)
Join Filter: ((r.date >= updates.start_date) AND (r.date <= (COALESCE((lag(updates.start_date) OVER (?))::timestamp with time zone, now()))))
Rows Removed by Join Filter: 273127
-> WindowAgg (cost=9.20..12.26 rows=136 width=53) (actual time=0.508..1.631 rows=136 loops=1)
-> Sort (cost=9.20..9.54 rows=136 width=41) (actual time=0.482..0.609 rows=136 loops=1)
Sort Key: updates.start_date DESC
Sort Method: quicksort Memory: 26kB
-> Seq Scan on updates (cost=0.00..4.38 rows=136 width=41) (actual time=0.045..0.244 rows=136 loops=1)
Filter: ((start_date >= '2014-07-04 12:37:24.227'::timestamp without time zone) AND (start_date <= '2024-07-04 12:37:24.227'::timestamp without time zone) AND (car_id = '1'::smallint))
-> Materialize (cost=16634.52..16681.08 rows=200 width=48) (actual time=0.783..1.814 rows=2023 loops=136)
-> Subquery Scan on r (cost=16634.52..16680.08 rows=200 width=48) (actual time=106.474..136.080 rows=2023 loops=1)
-> GroupAggregate (cost=16634.52..16678.08 rows=200 width=48) (actual time=106.470..133.886 rows=2023 loops=1)
Group Key: (date_trunc('hour'::text, "*SELECT* 1".date))
-> Sort (cost=16634.52..16640.95 rows=2571 width=48) (actual time=106.397..108.172 rows=2571 loops=1)
Sort Key: (date_trunc('hour'::text, "*SELECT* 1".date))
Sort Method: quicksort Memory: 189kB
-> Result (cost=0.43..16488.90 rows=2571 width=48) (actual time=0.088..97.473 rows=2571 loops=1)
-> Append (cost=0.43..16456.76 rows=2571 width=48) (actual time=0.078..85.684 rows=2571 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.43..16410.09 rows=1996 width=48) (actual time=0.076..79.200 rows=1996 loops=1)
-> Nested Loop (cost=0.43..16390.13 rows=1996 width=62) (actual time=0.073..76.010 rows=1996 loops=1)
-> Seq Scan on drives d (cost=0.00..75.93 rows=1996 width=18) (actual time=0.017..7.368 rows=1996 loops=1)
Filter: ((start_date >= '2014-07-04 12:37:24.227'::timestamp without time zone) AND (start_date <= '2024-07-04 12:37:24.227'::timestamp without time zone) AND (car_id = '1'::smallint))
-> Index Scan using positions_pkey on positions p (cost=0.43..8.17 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1996)
Index Cond: (id = d.start_position_id)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..33.81 rows=575 width=48) (actual time=0.081..3.813 rows=575 loops=1)
-> Seq Scan on charging_processes p_1 (cost=0.00..28.06 rows=575 width=62) (actual time=0.076..2.772 rows=575 loops=1)
Filter: ((end_date >= '2014-07-04 12:37:24.227'::timestamp without time zone) AND (end_date <= '2024-07-04 12:37:24.227'::timestamp without time zone) AND (car_id = '1'::smallint))
Planning Time: 2.560 ms
Execution Time: 545.820 ms
(36 rows)
from teslamate.
36 rows is the query plan, not the actual result set. Ok if you compare data compared by the query with the current dashboard how much does it differ? I'm expecting slight differences in the avg range and maybe number of charges column but as it's that much quicker I would say we use the updated one?
from teslamate.
36 rows is the query plan, not the actual result set.
Thanks for clarifying.
but as it's that much quicker I would say we use the updated one?
That's my feeling as well. Will look more into detail by tomorrow. I do not see much benefit in number of charges and range anyways in the update dashboard.
from teslamate.
I took a closer look.
Updates-data-2024-07-05 09_03_59.xlsx
- Number of Charges only deviate +-1 in 18 cases
- Range deviate in 13 cases more than +- 10 km
- the new query does not return a range in 4 cases
--> I would go with the new query and take the speed improvement of 100x or 1000%.
from teslamate.
closed by #4051
from teslamate.
Related Issues (20)
- Wrong battery capacity in Battery Health HOT 7
- Lost all Map Details upgrading from 1.29.2 to 1.30.0 HOT 3
- Grafana map automatic zoom not working HOT 2
- 1.30.1 - Localized Grafana doesn't work as per claim in #4064 HOT 16
- Battery Health not plotting HOT 10
- Teslamate is failing to run after update HOT 2
- Unauthorized using Tesla Fleet API directly HOT 39
- "Range Added" is incorrect, and has incorrect units on the "Charges" dashboard HOT 1
- Status: 500. Message: db query error: pq: time zone "$__timezone" not recognized on Statistics dashboard HOT 2
- Switching to Fleet API from current Owners API. Refersh Token mismatch issue HOT 2
- "Last status change" in the status panel is not displayed correctly HOT 2
- Switching to Teslemetry gives token error? HOT 1
- Bug with docker and grafana:latest HOT 4
- Timeout/alarm in logs when submitting jwt tokens HOT 3
- Drive Stats dashboard has an area with a confusing title HOT 2
- Can't login Teslamate via Token HOT 5
- Charge scheduling missing after 2024.26.3.1 update HOT 14
- Temp and Net/Gross not showing for Cybertruck. HOT 1
- Trailer mode HOT 3
- Token login bug HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from teslamate.