Re: nested query vs left join: query planner very confused

David Rysdam wrote
> I'd never heard of Materialize before, so I looked into it. Seems to
> make a virtual table of the subquery so repetitions of the parent query
> don't have to re-do the work. Sounds like it should only help, right?

Forgive any inaccuracies but I'm pretty sure about the following:

Materialize is this sense means what you need doesn't fit in memory (likely
work-mem setting) and needs to be saved to disk and streamed from there.
Since IO is expensive this kills. The virtual table concept is mostly
implemented by hash (tables) and not materialize.

The materialize is only running once and creating a 95k record table, then
scanning that table 95k times to locate a potential match for each input
row. Since materialize does not index it has to sequential scan which takes
forever.

The other question, why the difference, is that IN has to accomodate NULLs
in the lookup table; join does not. neither does EXISTS. If you can
replace the NOT IN with NOT EXISTS and write a correlated sub-query you
should get the same plan as the LEFT JOIN version, IIRC.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/nested-query-vs-left-join-query-planner-very-confused-tp5780585p5780596.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.