speeding up grafana sensor-data query on raspberry pi 3

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

Re: speeding up grafana sensor-data query on raspberry pi 3

back. Thanks again, Clemens Am So., 16. Apr. 2023 um 22:50 Uhr schrieb David Rowley : > > On Mon, 17 Apr 2023 at 05:00, Clemens Eisserer wrote: > > Example: > > table smartmeter with non-null column ts (timestamp with time zone) > > and brinc index on ts, no pk to avoid

Re: speeding up grafana sensor-data query on raspberry pi 3

ly as well as postgresql - awesome! Thanks again for all the suggestions and for such a great dbms! - Clemens Am Di., 18. Apr. 2023 um 14:14 Uhr schrieb Clemens Eisserer : > > Hi again, > > Thanks for the suggestions. > > - I increased work_mem to 64M, which caused disk-based s

Selection not "pushed down into" CTE

Hi, running postgresql 15.5 I was recently surpised postgresql didn't perform an optimization which I thought would be easy to apply. so in this case I don't have an actual performance problem but I am rather curious if this is limitation in postgresql or whether there is a semantic difference in

Re: Selection not "pushed down into" CTE

Hi Tom, Thanks for the detailed explanation what is preventing the optimization and the view behind the scenes. Best regards, Clemens

Re: Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan

is always true, all rows are evaluated >> QUERY PLAN >> - >> Seq Scan on test (cost=0.00..21.00 rows=1000 width=19) >> (1 fila) >> >> test=# EXPLAIN SELECT * FROM test WHERE id =

Why ORing with a false one-time filter turns an Index-Lookup into a SeqScan

Hello, Any idea what could cause postgresql (16.0) to fall back to a SeqScan when ORing a falsy one-time filter to a selection which would otherwise use an index scan? 1.) Without the false one-time condition, the query uses the existing index on owner to perform the lookup: select * from mytable

Any way to get nested loop index joins on CTEs?

Hi, I am using generate_series + a join to group time-series data into buckets, which works well as long as I do this only for one aggregation hierarchy: The index on the timestamp of the table with the actual time-series data is used for a nested loop index join. However with more aggregation le