I have a huge dataset (customer,timestamp,consumption). I need to use an index to improve the performance of various queries, but I cannot seem to create any index that will boost up the performance of this specific query:
SELECT customer, AVG(consumption) FROM alicante_1y GROUP BY customer;
From what I have been reading, there is no easy/direct way to improve the performance of AVG function...
Any help appreciated. Thank you in advance.
explain (analyze, verbose) output:
HashAggregate (cost=194302.67..194315.09 rows=993 width=16) (actual time=6847.581..6848.630 rows=994 loops=1) Output: customer, avg(consumption) Group Key: alicante_1y.customer -> Seq Scan on public.alicante_1y (cost=0.00..150840.45 rows=8692445 width=16) (actual time=0.175..1829.867 rows=8692445 loops=1) Output: customer, t, consumption Planning time: 0.633 ms Execution time: 6849.036 ms
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `alicante_1y` ADD INDEX `alicante_1y_idx_customer` (`customer`);
SELECT alicante_1y.customer, AVG(alicante_1y.consumption) FROM alicante_1y GROUP BY alicante_1y.customer ORDER BY NULL