On Mon, 17 Jul 2023 at 21:13, basti wrote:
> volkszaehler=# explain analyze SELECT COUNT(DISTINCT DATE_TRUNC('day',
> TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data
> WHERE channel_id = 5 AND timestamp >= 0;
Alternatively, you could express this as:
SELECT COUNT(*) FROM
On Tue, 18 Jul 2023 at 06:19, basti wrote:
>
> Thanks a lot tomas, i will try it.
>
> I have find out that there is a 'aggregation' function in the frontend.
> But this is MySQL specific and I have no idea the transform it to postgres.
>
> It looks like:
> 'REPLACE INTO aggregate (channel_id,
Thanks a lot tomas, i will try it.
I have find out that there is a 'aggregation' function in the frontend.
But this is MySQL specific and I have no idea the transform it to postgres.
It looks like:
'REPLACE INTO aggregate (channel_id, type, timestamp, value, count)
SELECT channel_id, ? AS
On 7/17/23 04:13, basti wrote:
[snip]
The Indexes:
volkszaehler=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE
tablename LIKE 'data%' ORDER BY tablename,indexname;
tablename | indexname | indexdef
On 7/17/23 13:20, Tomas Vondra wrote:
> ...
>
> It's always going to be slow with the COUNT(DISTINCT), I'm afraid.
>
> Not sure how much you can modify the query / database, and how accurate
> results you need. If you're OK with estimates, you can try postgres-hll
> extension [2] which estimates
On 7/17/23 11:13, basti wrote:
> 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
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
