On 21 December 2015 at 23:57, Viktor Leis <leis@in.tum.de> wrote:
Please have a look at Figure 6 (page 6) in http://www.vldb.org/pvldb/vol9/p204-leis.pdf Disabling nested loop joins without index scan (going from (a) to (b)) results in great improvements across the board. And even more importantly, it avoids most of the cases where queries took unreasonably long and timed out. Basically this amounts to the being able to run the query on PostgreSQL or not.
For that data, yes. But you're ignoring other important cases. Small or even 1-element lookup tables can be one where a nestloop over a seqscan turns out to be by far the fastest way to do the job. This can really add up if it's deep in a subplan that's excuted repeatedly, or if it's part of queries that get run very frequently on a busy OLTP system.
That said, these cases are also the ones that land up hurting very badly if the stats are inaccurate or outdated and our expected 3 loops turns into 3000.
