CODEWITHSUNDEEP
sqlperformancepostgresqlindexingaverage
Orestes Polyzos
Orestes Polyzos

Reputation: 33

Which INDEX type should I use?

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

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your best best for an index is a covering index. That would be:

create index idx_customer_consumption on alicante_1y(customer, consumption);

Postgres should also be able to use the index for the aggregation.

Upvotes: 0

Related Questions