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).


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?

  • 2
    It's not clear why the difference might be so extreme, but if you have multiple cores on the machine and this is the only query you are running, I would expect SQL Server to be much faster. Postgres will not use more than 1 core for a single query, whereas SQL Server could be using 8 or more cores for the query depending on your hardware and MAXDOP settings. At a glance, this looks like a query that SQL Server would be able to parallelize quite efficiently. So this might be at least one of the factors explaining the difference you are seeing. – Geoff Patterson Feb 26 '15 at 21:25
  • is there any way to get postgres to speed it up / also use multiple cores? – Staeff Feb 27 '15 at 9:24
  • 1
    Without an EXPLAIN ANALYZE output one can only guess what's wrong. My best idea is that you need an index on date(measuretime), as an index on the column itself cannot support the query you have. – dezso Feb 14 '17 at 10:50
  • Actually, I guess the index should be more effective if it comprises both grouping parameters: (parameter_id, date(measuretime)). Or it may be good for nothing and PostgreSQL decides it is better to sequential scan and then sort (or hash, or...) – joanolo Feb 14 '17 at 22:51

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.
  • On my Windows laptop Postgres 9.6.2 could do a sample query with 38 million rows in 4 seconds with 4 workers: explain.depesz.com/s/l5P and 3 seconds with 6 workers: explain.depesz.com/s/7Et8 – a_horse_with_no_name Feb 14 '17 at 8:52
  • 1
    I'm currently not anymore involved with that project (nor postgres databases for that matter as we stuck to SQL Server after our performance tests), so I probably won't come back with results anytime soon. But we always suspected that the date conversation and non-multicore support where to blame so I think your answer seems pretty sound. – Staeff Feb 14 '17 at 19:11

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.