Comments (9)
swiffer
commented on July 24, 2024
Hi @DrMichael - those are loading instantly for me. Can you run the following sql snippet please?
EXPLAIN ANALYZE WITH last_position AS (
SELECT date, convert_celsius(outside_temp, 'C') AS "Outside Temperature [°C]"
FROM positions
WHERE car_id = '2' AND outside_temp IS NOT NULL AND date AT TIME ZONE 'Etc/UTC' >= (NOW() - interval '60m')
ORDER BY date DESC
LIMIT 1
),
last_charge AS (
SELECT date, convert_celsius(outside_temp, 'C') AS "Outside Temperature [°C]"
FROM charges
JOIN charging_processes ON charges.charging_process_id = charging_processes.id
WHERE car_id = '2' AND outside_temp IS NOT NULL AND date AT TIME ZONE 'Etc/UTC' >= (NOW() - interval '60m')
ORDER BY date DESC
LIMIT 1
)
SELECT * FROM last_position
UNION ALL
SELECT * FROM last_charge
ORDER BY date DESC
LIMIT 1;it should give an output similar to this one:
from teslamate.
DrMichael
commented on July 24, 2024
I get:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4.29..4.29 rows=1 width=40) (actual time=109393.998..109394.023 rows=0 loops=1)
CTE last_position
-> Limit (cost=0.43..3.46 rows=1 width=13) (actual time=107120.295..107120.299 rows=0 loops=1)
-> Index Scan Backward using positions_date_index on positions (cost=0.43..219425.94 rows=72377 width=13) (actual time=107120.280..107120.282 rows=0 loops=1)
Filter: ((outside_temp IS NOT NULL) AND (car_id = '1'::smallint) AND ((date AT TIME ZONE 'Etc/UTC'::text) >= (now() - '01:00:00'::interval)))
Rows Removed by Filter: 4278161
CTE last_charge
-> Limit (cost=0.58..0.77 rows=1 width=14) (actual time=2273.591..2273.596 rows=0 loops=1)
-> Nested Loop (cost=0.58..5557.24 rows=29374 width=14) (actual time=2273.578..2273.583 rows=0 loops=1)
-> Index Scan Backward using charges_date_index on charges (cost=0.29..4652.35 rows=29374 width=18) (actual time=2273.568..2273.568 rows=0 loops=1)
Filter: ((outside_temp IS NOT NULL) AND ((date AT TIME ZONE 'Etc/UTC'::text) >= (now() - '01:00:00'::interval)))
Rows Removed by Filter: 88137
-> Memoize (cost=0.29..0.31 rows=1 width=4) (never executed)
Cache Key: charges.charging_process_id
Cache Mode: logical
-> Index Scan using charging_processes_pkey on charging_processes (cost=0.28..0.30 rows=1 width=4) (never executed)
Index Cond: (id = charges.charging_process_id)
Filter: (car_id = '1'::smallint)
-> Sort (cost=0.06..0.07 rows=2 width=40) (actual time=109393.993..109393.999 rows=0 loops=1)
Sort Key: last_position.date DESC
Sort Method: quicksort Memory: 17kB
-> Append (cost=0.00..0.05 rows=2 width=40) (actual time=109393.948..109393.952 rows=0 loops=1)
-> CTE Scan on last_position (cost=0.00..0.02 rows=1 width=40) (actual time=107120.306..107120.307 rows=0 loops=1)
-> CTE Scan on last_charge (cost=0.00..0.02 rows=1 width=40) (actual time=2273.607..2273.608 rows=0 loops=1)
Planning Time: 3.436 ms
Execution Time: 109394.402 ms
(26 rows)
from teslamate.
swiffer
commented on July 24, 2024
Those index scans are taking way longer than expected - could you run these 2 commands once (backup exists?)
VACUUM FULL ANALYZE;
REINDEX DATABASE teslamate;from teslamate.
DrMichael
commented on July 24, 2024
I ran those on the Ubuntu installation and imported the database on the RPi, a bit better. The two commands take ages on the RPi, shall I run them there also again?
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4.23..4.23 rows=1 width=40) (actual time=83308.976..83309.000 rows=0 loops=1)
CTE last_position
-> Limit (cost=0.43..3.40 rows=1 width=13) (actual time=80824.181..80824.184 rows=0 loops=1)
-> Index Scan Backward using positions_date_index on positions (cost=0.43..219449.14 rows=73957 width=13) (actual time=80824.165..80824.167 rows=0 loops=1)
Filter: ((outside_temp IS NOT NULL) AND (car_id = '1'::smallint) AND ((date AT TIME ZONE 'Etc/UTC'::text) >= (now() - '01:00:00'::interval)))
Rows Removed by Filter: 4278167
CTE last_charge
-> Limit (cost=0.58..0.77 rows=1 width=14) (actual time=2484.563..2484.569 rows=0 loops=1)
-> Nested Loop (cost=0.58..5555.48 rows=29378 width=14) (actual time=2484.551..2484.556 rows=0 loops=1)
-> Index Scan Backward using charges_date_index on charges (cost=0.29..4653.16 rows=29378 width=18) (actual time=2484.542..2484.543 rows=0 loops=1)
Filter: ((outside_temp IS NOT NULL) AND ((date AT TIME ZONE 'Etc/UTC'::text) >= (now() - '01:00:00'::interval)))
Rows Removed by Filter: 88143
-> Memoize (cost=0.29..0.31 rows=1 width=4) (never executed)
Cache Key: charges.charging_process_id
Cache Mode: logical
-> Index Scan using charging_processes_pkey on charging_processes (cost=0.28..0.30 rows=1 width=4) (never executed)
Index Cond: (id = charges.charging_process_id)
Filter: (car_id = '1'::smallint)
-> Sort (cost=0.06..0.07 rows=2 width=40) (actual time=83308.970..83308.976 rows=0 loops=1)
Sort Key: last_position.date DESC
Sort Method: quicksort Memory: 17kB
-> Append (cost=0.00..0.05 rows=2 width=40) (actual time=83308.806..83308.811 rows=0 loops=1)
-> CTE Scan on last_position (cost=0.00..0.02 rows=1 width=40) (actual time=80824.192..80824.193 rows=0 loops=1)
-> CTE Scan on last_charge (cost=0.00..0.02 rows=1 width=40) (actual time=2484.580..2484.581 rows=0 loops=1)
Planning Time: 77.680 ms
Execution Time: 83309.780 ms
(26 rows)
from teslamate.
DrMichael
commented on July 24, 2024
Hmmm, perhaps one could add $__timeFilter(date). That makes it a lot faster. The dashboard has a mixture of current values and values according to the timefilter...
from teslamate.
swiffer
commented on July 24, 2024
@DrMichael - two ways to solve this i guess - will rework #4019 after i've received your feedback.
first:
EXPLAIN ANALYZE WITH last_position AS (
SELECT date, convert_celsius(last_value(outside_temp) over (order by date desc), 'C') AS "Outside Temperature [°C]"
FROM positions
WHERE car_id = '2' AND outside_temp IS NOT NULL
ORDER BY date DESC
LIMIT 1
),
last_charge AS (
SELECT date, convert_celsius(last_value(outside_temp) over (order by date desc), 'C') AS "Outside Temperature [°C]"
FROM charges
JOIN charging_processes ON charges.charging_process_id = charging_processes.id
WHERE car_id = '2' AND outside_temp IS NOT NULL
ORDER BY date DESC
LIMIT 1
),
final as (
SELECT * FROM last_position
UNION ALL
SELECT * FROM last_charge
ORDER BY date desc
LIMIT 1
)
select * from final where date >= (NOW() at time zone 'UTC' - interval '60m')second:
EXPLAIN ANALYZE WITH last_position AS (
SELECT date, case when date >= (NOW() at time zone 'UTC' - interval '60m') then convert_celsius(last_value(outside_temp) over (order by date desc), 'C') else null end AS "Outside Temperature [°C]"
FROM positions
WHERE car_id = '2' AND outside_temp IS NOT NULL
ORDER BY date DESC
LIMIT 1
),
last_charge AS (
SELECT date, case when date >= (NOW() at time zone 'UTC' - interval '60m') then convert_celsius(last_value(outside_temp) over (order by date desc), 'C') else null end AS "Outside Temperature [°C]"
FROM charges
JOIN charging_processes ON charges.charging_process_id = charging_processes.id
WHERE car_id = '2' AND outside_temp IS NOT NULL
ORDER BY date DESC
LIMIT 1
)
SELECT * FROM last_position
UNION ALL
SELECT * FROM last_charge
ORDER BY date DESC
LIMIT 1;what runs faster for you? - any reason why temp is only fetched if data was logged within last 60 minutes or was that an attempt of increasing performance in the past?
depending on results i might go back to one query per panel in grafana to make it easier to maintain, what do you think?
from teslamate.
DrMichael
commented on July 24, 2024
The second one, slightly. But both fast...
First:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on final (cost=1.97..1.99 rows=1 width=40) (actual time=6.571..6.591 rows=0 loops=1)
Filter: (final.date >= ((now() AT TIME ZONE 'UTC'::text) - '01:00:00'::interval))
Rows Removed by Filter: 1
CTE last_position
-> Limit (cost=0.43..1.24 rows=1 width=40) (actual time=6.214..6.218 rows=1 loops=1)
-> WindowAgg (cost=0.43..179972.33 rows=221872 width=40) (actual time=6.210..6.213 rows=1 loops=1)
-> Index Scan Backward using positions_date_index on positions (cost=0.43..176644.25 rows=221872 width=13) (actual time=6.140..6.156 rows=3 loops=1)
Filter: ((outside_temp IS NOT NULL) AND (car_id = '1'::smallint))
CTE last_charge
-> Limit (cost=0.58..0.66 rows=1 width=40) (actual time=0.270..0.277 rows=1 loops=1)
-> WindowAgg (cost=0.58..7462.76 rows=88134 width=40) (actual time=0.266..0.272 rows=1 loops=1)
-> Nested Loop (cost=0.58..6140.75 rows=88134 width=14) (actual time=0.212..0.235 rows=2 loops=1)
-> Index Scan Backward using charges_date_index on charges (cost=0.29..3771.73 rows=88134 width=18) (actual time=0.115..0.122 rows=2 loops=1)
Filter: (outside_temp IS NOT NULL)
-> Memoize (cost=0.29..0.31 rows=1 width=4) (actual time=0.045..0.046 rows=1 loops=2)
Cache Key: charges.charging_process_id
Cache Mode: logical
Hits: 1 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using charging_processes_pkey on charging_processes (cost=0.28..0.30 rows=1 width=4) (actual time=0.066..0.067 rows=1 loops=1)
Index Cond: (id = charges.charging_process_id)
Filter: (car_id = '1'::smallint)
-> Limit (cost=0.06..0.06 rows=1 width=40) (actual time=6.552..6.559 rows=1 loops=1)
-> Sort (cost=0.06..0.07 rows=2 width=40) (actual time=6.548..6.553 rows=1 loops=1)
Sort Key: last_position.date DESC
Sort Method: quicksort Memory: 17kB
-> Append (cost=0.00..0.05 rows=2 width=40) (actual time=6.225..6.514 rows=2 loops=1)
-> CTE Scan on last_position (cost=0.00..0.02 rows=1 width=40) (actual time=6.221..6.225 rows=1 loops=1)
-> CTE Scan on last_charge (cost=0.00..0.02 rows=1 width=40) (actual time=0.275..0.278 rows=1 loops=1)
Planning Time: 3.491 ms
Execution Time: 7.044 ms
(30 rows)
Second:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.99..1.99 rows=1 width=40) (actual time=0.680..0.703 rows=1 loops=1)
CTE last_position
-> Limit (cost=0.43..1.25 rows=1 width=40) (actual time=0.383..0.388 rows=1 loops=1)
-> WindowAgg (cost=0.43..182191.05 rows=221872 width=40) (actual time=0.380..0.382 rows=1 loops=1)
-> Index Scan Backward using positions_date_index on positions (cost=0.43..176644.25 rows=221872 width=13) (actual time=0.264..0.279 rows=3 loops=1)
Filter: ((outside_temp IS NOT NULL) AND (car_id = '1'::smallint))
CTE last_charge
-> Limit (cost=0.58..0.67 rows=1 width=40) (actual time=0.217..0.224 rows=1 loops=1)
-> WindowAgg (cost=0.58..8344.10 rows=88134 width=40) (actual time=0.214..0.219 rows=1 loops=1)
-> Nested Loop (cost=0.58..6140.75 rows=88134 width=14) (actual time=0.165..0.187 rows=2 loops=1)
-> Index Scan Backward using charges_date_index on charges (cost=0.29..3771.73 rows=88134 width=18) (actual time=0.084..0.091 rows=2 loops=1)
Filter: (outside_temp IS NOT NULL)
-> Memoize (cost=0.29..0.31 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=2)
Cache Key: charges.charging_process_id
Cache Mode: logical
Hits: 1 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using charging_processes_pkey on charging_processes (cost=0.28..0.30 rows=1 width=4) (actual time=0.055..0.056 rows=1 loops=1)
Index Cond: (id = charges.charging_process_id)
Filter: (car_id = '1'::smallint)
-> Sort (cost=0.06..0.07 rows=2 width=40) (actual time=0.676..0.680 rows=1 loops=1)
Sort Key: last_position.date DESC
Sort Method: quicksort Memory: 17kB
-> Append (cost=0.00..0.05 rows=2 width=40) (actual time=0.395..0.630 rows=2 loops=1)
-> CTE Scan on last_position (cost=0.00..0.02 rows=1 width=40) (actual time=0.391..0.395 rows=1 loops=1)
-> CTE Scan on last_charge (cost=0.00..0.02 rows=1 width=40) (actual time=0.222..0.225 rows=1 loops=1)
Planning Time: 3.964 ms
Execution Time: 1.167 ms
(27 rows)
from teslamate.
DrMichael
commented on July 24, 2024
any reason why temp is only fetched if data was logged within last 60 minutes or was that an attempt of increasing performance in the past?
Actually, I think Adrian wanted to show a time, which is current. And not one maybe hours in the past. Without that, the query are fast, I think.
depending on results i might go back to one query per panel in grafana to make it easier to maintain, what do you think?
When they as fast as it seems, yes. That looks pretty good. (Although I did like the trick with the pannel query...:-)
from teslamate.
JakobLichterfeld
commented on July 24, 2024
Hi @DrMichael - those are loading instantly for me. Can you run the following sql snippet please?
EXPLAIN ANALYZE WITH last_position AS ( SELECT date, convert_celsius(outside_temp, 'C') AS "Outside Temperature [°C]" FROM positions WHERE car_id = '2' AND outside_temp IS NOT NULL AND date AT TIME ZONE 'Etc/UTC' >= (NOW() - interval '60m') ORDER BY date DESC LIMIT 1 ), last_charge AS ( SELECT date, convert_celsius(outside_temp, 'C') AS "Outside Temperature [°C]" FROM charges JOIN charging_processes ON charges.charging_process_id = charging_processes.id WHERE car_id = '2' AND outside_temp IS NOT NULL AND date AT TIME ZONE 'Etc/UTC' >= (NOW() - interval '60m') ORDER BY date DESC LIMIT 1 ) SELECT * FROM last_position UNION ALL SELECT * FROM last_charge ORDER BY date DESC LIMIT 1;
I run the query as well on my Rpi3:
Limit (cost=30.34..30.34 rows=1 width=40) (actual time=4.490..4.505 rows=0 loops=1)
CTE last_position
-> Limit (cost=4.47..4.47 rows=1 width=13) (actual time=4.173..4.176 rows=0 loops=1)
-> Sort (cost=4.47..4.47 rows=1 width=13) (actual time=4.170..4.172 rows=0 loops=1)
Sort Key: positions.date DESC
Sort Method: quicksort Memory: 17kB
-> Index Scan using positions_car_id_index on positions (cost=0.43..4.46 rows=1 width=13) (actual time=4.125..4.126 rows=0 loops=1)
Index Cond: (car_id = '2'::smallint)
Filter: ((outside_temp IS NOT NULL) AND ((date AT TIME ZONE 'Etc/UTC'::text) >= (now() - '01:00:00'::interval)))
CTE last_charge
-> Limit (cost=25.80..25.80 rows=1 width=13) (actual time=0.140..0.144 rows=0 loops=1)
-> Sort (cost=25.80..26.02 rows=90 width=13) (actual time=0.136..0.139 rows=0 loops=1)
Sort Key: charges.date DESC
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.44..25.35 rows=90 width=13) (actual time=0.086..0.089 rows=0 loops=1)
-> Index Scan using charging_processes_car_id_index on charging_processes (cost=0.15..4.17 rows=1 width=4) (actual time=0.083..0.084 rows=0 loops=1)
Index Cond: (car_id = '2'::smallint)
-> Index Scan using charges_charging_process_id_index on charges (cost=0.29..20.22 rows=96 width=17) (never executed)
Index Cond: (charging_process_id = charging_processes.id)
Filter: ((outside_temp IS NOT NULL) AND ((date AT TIME ZONE 'Etc/UTC'::text) >= (now() - '01:00:00'::interval)))
-> Sort (cost=0.06..0.07 rows=2 width=40) (actual time=4.486..4.490 rows=0 loops=1)
Sort Key: last_position.date DESC
Sort Method: quicksort Memory: 17kB
-> Append (cost=0.00..0.05 rows=2 width=40) (actual time=4.334..4.337 rows=0 loops=1)
-> CTE Scan on last_position (cost=0.00..0.02 rows=1 width=40) (actual time=4.184..4.185 rows=0 loops=1)
-> CTE Scan on last_charge (cost=0.00..0.02 rows=1 width=40) (actual time=0.144..0.144 rows=0 loops=1)
Planning Time: 94.377 ms
Execution Time: 5.049 ms
(28 rows)
Edit: Forget to change car_id before running, now already run a VACUUM FULL ANALYZE;
from teslamate.
Related Issues (20)
- Unable to delete, edit or add geofencing HOT 2
- Net and gross values in different places HOT 2
- Little issue that I have on migration (solved), now TeslaFI Import issue HOT 9
- perf: updates dashboard slow HOT 10
- [dev] Unable to establish code space; postgres user / password not established HOT 3
- Grafana maps not fitting data; zoomed out to world view HOT 5
- State showing offline when alseep since 2024.14.9 HOT 2
- Can't set charging session cost HOT 6
- 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 4
- Teslamate is failing to run after update HOT 2
- Unauthorized using Tesla Fleet API directly HOT 20
- "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 1
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.