The query optimizer performs an exhaustive search of all possible plans for executing a query. It computes a cost estimate for each possible plan and chooses the cheapest plan according to the cost algorithms.

Such a process is very fast for relatively simple queries. For queries involving large numbers of tables, however, the number of possible plans can be very large and the time spent enumerating the plans and associating costs with them can be significant.

While the Optimizer Timeout (see Optimizer Timeout) feature is useful in shortening processing time, the optimizer can take an unacceptable length of time to identify an efficient query plan in the case of very large queries, especially for queries with large numbers of potentially useful secondary indexes and queries whose tables have large numbers of rows (leading to very expensive plans that do not time out quickly).

The query optimizer includes an alternative mechanism for generating query plans, called greedy optimization, that can greatly reduce the length of compile time. Rather than enumerate all possible query plans (including all permutations of table join order, all combinations of tables and useful secondary indexes, and all “shapes” of query plans), the “greedy” enumeration heuristic starts with small plan fragments, using them as building blocks to construct the eventual query plan. The chosen fragments are always the lowest cost at that stage of plan construction, so even though large numbers of potential plans are not considered, those that are chosen are also based on cost estimation techniques.