I'm using postgreSQL 14 to perform some pivot table analysis where there are around 10 dimensions and 5 measures.
All the data are stored in a single table ( similar to a fact table )
- dimensions like country, product, year, ... are stored as integer ( countryId, productId )
- measures stored as numeric ( M1, M2, M3 )
I don't know in advance on which dimensions and measures are choosen.
SELECT countryId, productId, year,sum(M1)
FROM fact where countryId in (1,3,4) and year in (2020)
GROUP BY countryId, productId, year
ORDER BY countryId, productId, year
The filtering is always using the IN Operator against whatever possible dimension
What will be the best strategy to optimize the query response time ?
- a BTREE index on each dimension
- a BTREE index on all dimensions
- another kind of index
- any way to keep the result in cache
