Hello,
I use volkszaehler.org (a DIY Smartmeter) on postgres.
The hardware is a Dual-core 2,4 GHz amd64-CPU and 6 GB RAM.
The databse is approx. 1.5 GB on SSD
my local config looks like:
root@vz:~# cat /etc/postgresql/15/main/conf.d/local.conf
# DB Version: 15
# OS Type: linux
# Total Memory (RAM): 6 GB
# CPUs num: 2
# Connections num: 20
# Data Storage: sdd
max_connections = 20
shared_buffers = 2GB
effective_cache_size = 4608MB
maintenance_work_mem = 384MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 0.9
effective_io_concurrency = 200
min_wal_size = 1GB
max_wal_size = 4GB
work_mem = 512MB
temp_buffers = 512MB
# log
log_directory = '/var/log/postgresql/' # directory where log files are
written,
# can be absolute or relative
to PGDATA
log_filename = 'postgresql-15-main.log'
#log_statement = 'all'
#logging_collector = on
log_temp_files = 0
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'
The Values:
volkszaehler=# SELECT * FROM data limit 10;
timestamp | channel_id | value
---------------+------------+-------
1666008690865 | 5 | 232
1666008691867 | 5 | 236
1666008692865 | 5 | 237
1666008693867 | 5 | 232
1666008694865 | 5 | 227
1666008695867 | 5 | 229
1666008698866 | 5 | 228
1666008699868 | 5 | 229
1666008700864 | 5 | 228
1666008701868 | 5 | 230
volkszaehler=# SELECT COUNT(value) FROM data;
count
----------
17884247
(1 row)
The Indexes:
volkszaehler=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE
tablename LIKE 'data%' ORDER BY tablename,indexname;
tablename | indexname |
indexdef
-----------+--------------------------------+--------------------------------------------------------------------------------------------------
data | idx_16391_idx_adf3f36372f5a1aa | CREATE INDEX
idx_16391_idx_adf3f36372f5a1aa ON volkszaehler.data USING btree (channel_id)
data | idx_16391_primary | CREATE UNIQUE INDEX
idx_16391_primary ON volkszaehler.data USING btree (channel_id, "timestamp")
data | idx_data_timestamp | CREATE INDEX
idx_data_timestamp ON volkszaehler.data USING btree ("timestamp")
(3 rows)
The Query:
volkszaehler=# explain analyze SELECT COUNT(DISTINCT DATE_TRUNC('day',
TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data
WHERE channel_id = 5 AND timestamp >= 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=590793.68..590793.69 rows=1 width=8) (actual
time=15449.536..15449.539 rows=1 loops=1)
-> Seq Scan on data (cost=0.00..382037.82 rows=16700469 width=8)
(actual time=247.092..3833.495 rows=16669429 loops=1)
Filter: (("timestamp" >= 0) AND (channel_id = 5))
Rows Removed by Filter: 1215163
Planning Time: 0.374 ms
JIT:
Functions: 5
Options: Inlining true, Optimization true, Expressions true,
Deforming true
Timing: Generation 1.201 ms, Inlining 158.833 ms, Optimization
59.816 ms, Emission 28.472 ms, Total 248.322 ms
Execution Time: 15451.093 ms
(10 rows)
Round about 16 sec is too long, the frontend run in timeout or other erros.
A simple count look like
volkszaehler=# explain analyze SELECT count (channel_id) FROM data;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=208089.76..208089.77 rows=1 width=8) (actual
time=3514.293..3523.842 rows=1 loops=1)
-> Gather (cost=208089.55..208089.76 rows=2 width=8) (actual
time=3514.247..3523.800 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=207089.55..207089.56 rows=1
width=8) (actual time=3427.139..3427.141 rows=1 loops=3)
-> Parallel Seq Scan on data (cost=0.00..188457.44
rows=7452844 width=8) (actual time=0.137..2121.695 rows=5962263 loops=3)
Planning Time: 0.247 ms
JIT:
Functions: 11
Options: Inlining false, Optimization false, Expressions true,
Deforming true
Timing: Generation 2.665 ms, Inlining 0.000 ms, Optimization 1.612
ms, Emission 48.580 ms, Total 52.857 ms
Execution Time: 3525.656 ms
(12 rows)
Why are the indexes not used ?
Is it a Problem of the DATE_TRUNC ?
How can I optimize?
Best regards
