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 times 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. 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 plans at http://archives.postgresql.org/pgsql-hackers/2010-05/msg00889.php but not with actual data. It's possible to recreate the basic setup using the Dell Store data. Let's say you have a small set of orderline data you're...
