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
New contributor
Sébastien Richez is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.


Your Answer

Sébastien Richez is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.