Re: Query take a long time and use no index

2023-07-18 Thread David Rowley
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 (SE

Re: Query take a long time and use no index

2023-07-18 Thread David Rowley
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, type

Re: Query take a long time and use no index

2023-07-17 Thread basti
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 type

Re: Query take a long time and use no index

2023-07-17 Thread Ron
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 ---++---

Re: Query take a long time and use no index

2023-07-17 Thread Tomas Vondra
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

Re: Query take a long time and use no index

2023-07-17 Thread Tomas Vondra
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 /etc/postgresql/15/main/conf.d/local.co

Query take a long time and use no index

2023-07-17 Thread basti
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 (