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 the 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)
Hash Cond: (a.a = b.b)
-> Function Scan on generate_series a (cost=0.00..1.00 rows=100 width=4)
-> Hash (cost=1.00..1.00 rows=100...