Enabling and disabling optimizer settings

So far, the most important optimizations that are performed by the planner have been discussed in detail. PostgreSQL has improved a lot over the years. Still, something can go south, and users have to convince the planner to do the right thing.

To modify plans, PostgreSQL offers a couple of runtime variables that will have a significant impact on planning. The idea is to give the end user a chance to make certain types of nodes in the plan more expensive than others. What does that mean in practice? Here is a simple plan:

test=# explain SELECT *
 FROM generate_series(1, 100) AS a,
      generate_series(1, 100) AS b
 WHERE a = b;
                          QUERY PLAN
----------------------------------------------------------
 Hash Join (cost=2.25..4.63 rows=100 width=8)
  ...