mardi 23 décembre 2014

PostgreSQL indexing table on multiple columns


I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below:



day: timestamp without time zone, // describes a day
meter_id:bigint,
meter_type: bigint,
customer_id:bigint,
hourly_values: double precision[]


I need an efficient index strategy to be able to query this table by day, meter_id, meter_type and customer_id.


Example of queries:



select hourly_values from table where meter_type = :mt and customer_id = :ci and day >= :startDate and day <= :endDate

select hourly_values from table where meter_type = :mt and day = :oneDay

select hourly_values from table where meter_type = :mt and customerId = :oneDay

select hourly_values from table where meter_id = :mi and customerId = :ci and day >= :startDate and day <= :endDate


What would be an optimal solution for this problem.


Thank you for your help!





Aucun commentaire:

Enregistrer un commentaire