Re: Experimental evaluation of PostgreSQL's query optimizer

On 21 December 2015 at 23:57, Viktor Leis <leis(at)in(dot)tum(dot)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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services