<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clip

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

<a class="user-mention notranslate" data-hovercard-type="user" data-hover

I took a closer look. <a href="https://github.com/user-attachments/f

closed by <a class="issue-link js-issue-link" data-error-text="Failed to load title" d

perf: updates dashboard slow about teslamate HOT 10 CLOSED

JakobLichterfeld commented on August 27, 2024

perf: updates dashboard slow

from teslamate.

Comments (10)

swiffer commented on August 27, 2024 1

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.

swiffer commented on August 27, 2024 1

I do not see much benefit in number of charges and range anyways in the update dashboard.

Same

from teslamate.

swiffer commented on August 27, 2024

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.

JakobLichterfeld commented on August 27, 2024

:-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

 "rawSql": "with u as (\r\n select *, coalesce(lag(start_date) over(order by start_date desc), now()) as next_start_date \r\n from updates\r\n where car_id = $car_id and $__timeFilter(start_date)\r\n),\r\nrng as (\r\n SELECT\r\n\t to_timestamp(floor(extract(epoch from date)/21600)*21600) AS date,\r\n\t (sum([[preferred_range]]_battery_range_km) / nullif(sum(coalesce(usable_battery_level,battery_level)),0) * 100 ) AS \"battery_rng\"\r\n FROM (\r\n select battery_level, usable_battery_level, date, rated_battery_range_km, ideal_battery_range_km\r\n from positions\r\n where car_id = $car_id and $__timeFilter(date) and ideal_battery_range_km is not null\r\n union all\r\n select battery_level, coalesce(usable_battery_level,battery_level) as usable_battery_level, date, rated_battery_range_km, ideal_battery_range_km\r\n from charges c\r\n join charging_processes p ON p.id = c.charging_process_id \r\n where $__timeFilter(date) and p.car_id = $car_id\r\n ) as data\r\n GROUP BY 1\r\n)\r\n\r\nselect\t\r\n u.start_date as time,\r\n\textract(EPOCH FROM u.end_date - u.start_date) AS update_duration,\r\n\textract(EPOCH FROM u.start_date - lag(u.start_date) OVER (ORDER BY u.start_date)) AS since_last_update,\r\n\tsplit_part(u.version, ' ', 1) as version,\r\n\tcount(distinct cp.id) as chg_ct,\r\n\tconvert_km(avg(r.battery_rng), '$length_unit')::numeric(6,2) AS avg_[[preferred_range]]_range_[[length_unit]]\r\nfrom u u\r\nleft join charging_processes cp\r\n\tON u.car_id = cp.car_id\r\n \tand cp.start_date between u.start_date and u.next_start_date\r\nleft join rng r\r\n\tON r.date between u.start_date and u.next_start_date\r\ngroup by u.car_id,\r\n\tu.start_date,\r\n\tu.end_date,\r\n\tnext_start_date,\r\n\tsplit_part(u.version, ' ', 1)", 

from teslamate.

swiffer commented on August 27, 2024

@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 commented on August 27, 2024

@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.

swiffer commented on August 27, 2024

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.

JakobLichterfeld commented on August 27, 2024

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.

JakobLichterfeld commented on August 27, 2024

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.

JakobLichterfeld commented on August 27, 2024

closed by #4051

from teslamate.

perf: updates dashboard slow about teslamate HOT 10 CLOSED

Comments (10)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent