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

Question

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                  | not null (foreign key) references parameters(id)
item_id        | int                  | not null (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?

Answer 1

In PostgreSQL 9.6 Date constructors were fixed.

Improve speed of the output functions for timestamp, time, and date data types (David Rowley, Andres Freund)

You can see the commit fest for this here. It's roughly 20x faster. In addition 9.6 supports parallel sequential scans. So this may be 20x faster on a single core. And, the whole scan may be able to use all of the cores on your system.

1. Download 9.6
2. Try again.
3. Get back with the results.