Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> It's possible that MS-SQL is doing something analogous to the
>> hashed-subplan approach (hopefully with suitable tweaking for the NULL
>> case) but even then it's hard to see how it could take only 9 sec.
>> The cartesian product is too big.
> Fwiw it seems MS-SQL is doing something funny. The three plans posted in the
> screenshots for the "small", "mediu", and "large" cases are:
> ...
> Postgres is doing something equivalent to the first plan.
Hmm. I think the second plan is probably equivalent to the
hashed-subplan behavior that you can get in PG by rewriting the query to
NOT IN as I illustrated. The third plan looks to be the same thing plus
some parallelization frammishes.
I'm not clear on what "small/medium/large" means, in particular not on
which of these corresponds to the OP's report of 9-second execution.
regards, tom lane