Sometimes the optimizer doesn't do what you want, and you may want some tools to either force it to change its mind or to see what alternatives it's considering but rejecting. Consider this simple query that searches for one order and joins with its matching customer record:
This is a nice efficient query. But the type of join used is very sensitive to just how many rows are expected to be returned from the orders table, ones that then have to be matched against customers. This style of query continues to be preferred all the way up to one that selects 195 customers:
Make the query just a bit less selective, so one more row is considered, and the join type changes completely. When there are a fairly large number of matches expected, the planner uses a Merge Join with an inner-index scan for the order key, while matching against the entire customers table. It builds a hash table holding the relevant information from all 20,000 rows rather than using a Nested Loop:
That's exactly where the threshold is on this particular copy of the Dell store database: the expected 195 records gets a Nested Loop join. This gives you an idea of just how fast a query plan can completely change on you in production. If the execution time on the Merge Join version of this was really terrible, that one extra record that showed up would kill the performance of your server. Unless you were logging query plan EXPLAIN data, it's unlikely you'd have any idea what hit you.
What if the whole table is being scanned? That's even less selective, and the Hash Join is now preferred:
Because query plan changes can be so disastrous to a production server, some database administrators like to provide what are called optimizer hints to the server. These are strong suggestions to the optimizer that it executes a query a particular way. PostgreSQL doesn't explicitly support hints, based on two observations. Firstly, it makes users better motivated to provide feedback to the database developers on what the optimizer does badly, so that they can continually improve it. Secondly, many plan changes are actually the right thing to do. The original hint could have been based on statistics that are now out-of-date compared with what's actually in the table now.
There is a way to simulate something like an optimizer hint though. PostgreSQL allows individual queries to turn off particular optimizer features. If you disable the type of plan you'd prefer not to see, that's effectively hinting towards the plan you want. The list of features you can toggle off as of PostgreSQL 9.0 are:
- enable_bitmapscan
- enable_hashagg
- enable_hashjoin
- enable_indexscan
- enable_mergejoin
- enable_nestloop
- enable_seqscan
- enable_sort
- enable_tidscan
- enable_material
Note that turning these off doesn't actually disable the feature, it just increases its estimate cost so it's much less likely to be executed. There are some queries that it's only possible to execute with a Nested Loop, so even if you turn off enable_nestloop you'll still get one. And if you have no index on something, you can turn off enable_seqscan, but a Seq Scan will nonetheless happen.
Let's see what happens if Hash Joins are disabled on this query:
Now we get a Merge Join between the two tables, which turns out to be as or more efficient in practice (even though the cost is slightly higher). What about if neither hash nor Merge Joins are allowed?
Now the old Nested Loop shows up, with a plan the optimizer considers much more expensive than either of the other two. Because this is using the inner-index scan variation, the actual runtime isn't that much worse on this small dataset, but on a seriously large query this could be a disastrous plan.
Here's another example that you can try that will show the various ways another pair of tables can be joined as you disable optimizer options, presuming you start with a fresh session with the default settings (not just after the preceding, where some of these were already disabled):
EXPLAIN ANALYZE SELECT * FROM inventory,products WHERE inventory.prod_id=products.prod_id;
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT * FROM inventory,products WHERE inventory.prod_id=products.prod_id;
SET enable_mergejoin = off;
EXPLAIN ANALYZE SELECT * FROM inventory,products WHERE inventory.prod_id=products.prod_id;
SET enable_bitmapscan = off;
EXPLAIN ANALYZE SELECT * FROM inventory,products WHERE inventory.prod_id=products.prod_id;
I find it particularly amusing to watch how creative the optimizer gets at using increasingly less efficient index scans once the better join types go away. In particular, the last join will be much slower than the other ones once all access to the indexes is made impractical; here are some sample runtimes:
|
Join type |
Execution time (milliseconds) |
|
Hash join |
119 |
|
Merge join |
126 |
|
Nested loop with inner-index scan |
171 |
|
Nested loop with inner bitmap index scan |
219 |
|
Nested loop with inner Seq Scan |
292,491 |
Playing with these optimizer options to work around a query problem is tempting, because it can feel like an easy fix to a bad plan issue. It's dangerous though, because the plan that makes sense today can be totally wrong when either the amount or distribution of your data changes in the future. The reason why good tutorials about database queries try to explain how queries execute, including details like how statistics and costs fit together, is because understanding that theory is what can give you the background to make difficult decisions in this area.