38 seconds to 11. Same query, same forty million rows, no index added, no SQL changed. The only difference is PostgreSQL 9.6, released yesterday, and one setting.
The query is a typical report: count and sum over an events table, grouped by day, three months of data. On 9.5 the plan is one process grinding through the table. On 9.6 with parallelism enabled:
Finalize HashAggregate
-> Gather
Workers Planned: 4
-> Partial HashAggregate
-> Parallel Seq Scan on events
Four workers scan their own chunks, aggregate partially, the leader merges. Just more hands. For years we optimized SQL as if the database had exactly one worker per query. That assumption expired yesterday.
It is off by default. Set max_parallel_workers_per_gather above zero and the planner starts considering parallel plans. Start small and watch, because workers are not free. Each one is a process to launch and a share of work_mem to spend, and the planner knows it. That is why your OLTP queries will not change at all: fetch a user by primary key, and the index lookup finishes before a worker would even start. Parallelism pays only when there are millions of rows to chew. Analytics, reports, batch jobs.
That is also the caution. If your reports run on the same server as production traffic, four extra busy cores per report is CPU taken from user requests. The feature makes heavy queries cheaper. It does not make them free.
The hardware has had many cores for a decade. Nice to see the database finally allowed to use them. I wrote the caution paragraph for myself.