PGCon2017 - 20180510

PGCon 2017
The PostgreSQL Conference

Thomas Munro
Day Talks - Day 1 - 2017-05-25
Room DMS 1120
Start time 15:00
Duration 00:45
ID 1053
Event type Lecture
Track Hacking
Language used for presentation English

Hash Joins: Past, Present and Future

A peek inside the engine room

An introduction to the implementation of hash joins in PostgreSQL, and a discussion of some potential future improvements in the area of parallelism.

"Hash joins" are one of the three strategies that PostgreSQL uses to join relations (along with merge joins and nested loops), and can provide excellent performance under certain conditions. Before diving into the implementation details, I will give an introduction to the general concepts. Then the first part will describe the implementation of hash joins in PostgreSQL 9.5 and earlier including a detailed look at planning and execution, with a focus on memory management. The second part will discuss the multi-core join execution available in PostgreSQL 9.6 and its limitations. The third part will look at several strategies being worked on to tackle those limitations with different trade-offs, so that parallel hash joins can make better use of available memory and CPU cores in future releases.