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