Hi again, Thanks for the suggestions. - I increased work_mem to 64M, which caused disk-based sorting to be replaced with quicksort and resulted in a modest speedup. However I have to admit I didn't understand why more work_mem speeds up the heap scan. - the suggestion regarding "create statistics on group by" is awesome, to get rid of sorting is probably the best that could happen to the query. - ARMv8 instead of ARMv6 could have a positive impact I'll mirate to a containerized postgresql-version running on raspbian os 64-bit as I find some time to spare and report back. Thanks again, Clemens Am So., 16. Apr. 2023 um 22:50 Uhr schrieb David Rowley <dgrowleyml@xxxxxxxxx>: > > On Mon, 17 Apr 2023 at 05:00, Clemens Eisserer <linuxhippy@xxxxxxxxx> wrote: > > 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. > > I know you likely don't have much RAM to spare here, but more work_mem > might help, even just 16MBs might be enough. This would help the Sort > and to a lesser extent the Bitmap Heap Scan too. > > Also, if you'd opted to use PostgreSQL 14 or 15, then you could have > performed CREATE STATISTICS on your GROUP BY clause expression and > then run ANALYZE. That might cause the planner to flip to a Hash > Aggregate which would eliminate the Sort before aggregation. You'd > only need to sort 236 rows after the Hash Aggregate for the ORDER BY. > > Plus, what Justin said. > > David
