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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
