Horton

Re: NOT IN subquery optimization

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Li, Zheng" <zhelli@amazon.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Richard Guo <riguo@pivotal.io>, "Finnerty, Jim" <jfinnert@amazon.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2019-03-01T23:13:56Z
Lists: pgsql-hackers 
"Li, Zheng" <zhelli@amazon.com> writes:
> Although adding "or var is NULL" to the anti join condition forces the planner to choose nested loop anti join, it is always faster compared to the original plan.

TBH, I am *really* skeptical of sweeping claims like that.  The existing
code will typically produce a hashed-subplan plan, which ought not be
that awful as long as the subquery result doesn't blow out memory.
It certainly is going to beat a naive nested loop.

			regards, tom lane

Commits

  1. Check we don't misoptimize a NOT IN where the subquery returns no rows.