PostgreSQL 10 High Performance

Genetic query optimizer

Once the number of joins gets large enough, there is really no hope the query optimizer can evaluate all of them exhaustively and still return plans in a reasonable period of time. When this happens, the Genetic Query Optimizer (GEQO) is called in to work on the problem instead. It numbers each of the tables to be joined as an integer, starting with 1, so a possible plan for a five-table join would be encoded as 1-2-3-4-5. GEQO starts by creating some number of such plans essentially at random. It then evaluates each of these for fitness, specifically how large the execution cost is for each of them. The best plans are kept, the worst ones dropped, some changes to the plans are made (mutation), and the process repeats ...