Hi,
I am currently trying to migrate an influxdb 1.7 smarthome database to
postgresql (13.9) running on my raspberry 3.
It works quite well, but for the queries executed by grafana I get a
bit highter execution times than I'd hoped for.
Example:
table smartmeter with non-null column ts (timestamp with time zone)
and brinc index on ts, no pk to avoid a btree index.
Sensor values are stored every 5s, so for 1 month there are about 370k
rows - and in total the table currently holds about 3M rows.
The query to display the values for 1 month takes ~3s, with the bitmap
heap scan as well as aggregation taking up most of the time, with
sorting in between.
Is there anything that could be improved?
With influxdb I was able to view 3 and 6 months graphs, with
postgresql it simply takes too long.
I am currently running the 32-bit ARMv6 build, would it be a big
improvement running ARMv8/64-bit?
Thank you in advance, Clemens
smarthomedb=> explain analyze SELECT floor(extract(epoch from
ts)/10800)*10800 AS "time", AVG(stromL1) as l1, AVG(stromL2) as l2,
AVG(stroml3) as l3 FROM smartmeter WHERE ts BETWEEN '2023-03-16
T09:51:28.397Z' AND '2023-04-16T08:51:28.397Z' GROUP BY time order by time;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=117490.70..132536.10 rows=376135 width=32)
(actual time=2061.253..2974.336 rows=236 loops=1)
Group Key: ((floor((date_part('epoch'::text, ts) / '10800'::double
precision)) * '10800'::double precision))
-> Sort (cost=117490.70..118431.04 rows=376135 width=20) (actual
time=2058.407..2410.467 rows=371810 loops=1)
Sort Key: ((floor((date_part('epoch'::text, ts) /
'10800'::double precision)) * '10800'::double precision))
Sort Method: external merge Disk: 10960kB
-> Bitmap Heap Scan on smartmeter (cost=112.09..74944.93
rows=376135 width=20) (actual time=88.336..1377.862 rows=371810
loops=1)
Recheck Cond: ((ts >= '2023-03-16
10:51:28.397+01'::timestamp with time zone) AND (ts <= '2023-04-16
10:51:28.397+02'::timestamp with time zone))
Rows Removed by Index Recheck: 2131
Heap Blocks: lossy=4742
-> Bitmap Index Scan on smartmeter_ts_idx
(cost=0.00..18.05 rows=377166 width=0) (actual time=1.376..1.377
rows=47420 loops=1)
Index Cond: ((ts >= '2023-03-16
10:51:28.397+01'::timestamp with time zone) AND (ts <= '2023-04-16
10:51:28.397+02'::timestamp with time zone))
Planning Time: 0.419 ms
JIT:
Functions: 9
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 8.734 ms, Inlining 0.000 ms, Optimization 2.388
ms, Emission 83.137 ms, Total 94.259 ms
Execution Time: 2990.772 ms
(17 Zeilen)
