jeudi 26 février 2015

Simple avg query on large table much slower in PostgreSQL than SQL Server


I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012).


measurements:



column | type | attributes
---------------+----------------------+-----------------------------------------------------------
id | int/serial | (identity) primary key
measuretime | datetime/timestamp | not null
parameter_id | int | (foreign key) references parameters(id)
item_id | int | (foreign key) references items(id)
value | float | not null


and two nonclustered index on measuretime and parameter_id


I've inserted 2.609.280 rows in items (half a year with 5 seconds between each) and 31.311.360 rows in measurements (for each item with 12 parameters).


When I now try to query the average value per day per parameter it performs really well on SQL Server (00:00:02) but pretty bad on PostgreSQL (00:00:53).


SQL Server Query:



select parameter_id, convert(date, measuretime), avg(value)
from measurements
group by parameter_id, convert(date, measuretime)


PostgreSQL Query:



select parameter_id, date(measuretime), avg("value")
from measurements
group by parameter_id, date(measuretime)


Is there anything I can do about this? create an index? some server settings? change the query?





Aucun commentaire:

Enregistrer un commentaire