On Mon, 17 Apr 2023 at 05:00, Clemens Eisserer <linuxhippy@gmail.com> 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
