PostgreSQL 10 High Performance - Third Edition

Avoiding plan restructuring with OFFSET

Another technique that shows up sometimes to work around bad plan issues is using OFFSET 0 strategically in a query. Having no offset from when results return doesn't change the query result, but it is considered a volatile change that prevents some types of plan node changes—sometimes in a good way, despite what the planner predicts.

A good example of how this can work comes from a potential performance regression from certain types of joins that was introduced in PostgreSQL 8.4, and also still exists in 9.0 and 10.0. It's an interesting case study in how progress in improving the PostgreSQL optimizer tends to happen. The problem is described by Andres Freund using a test case and associated query ...