On Wed, 27 Nov 2013 11:21:09 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> DBMS-agnostic frequently means "sucks equally badly on all DBMSes" :-(
We've generally been OK (cf the ~50ms runtime for the same query at our
site), but we also notice problems sooner than our client sometimes does
and can make algorithm improvements where we don't know how to make DB
ones.
> This query is hard to optimize because of the weird behavior of NOT IN
> when nulls are involved. Since you aren't complaining that the query
> fails entirely, I'm supposing that lp.Sigs.signum contains no nulls,
> but the planner doesn't know that. If you can transform it to a NOT
> EXISTS, you'll likely get a much better plan:
>
> select signum from lp.Mags where signum is not null and
> not exists (select 1 from lp.Sigs where lp.Sigs.signum = lp.Mags.signum)
We've already shipped to the client, but I'm looking at how extensive a
patch would have to be. Very surprising we haven't hit this issue
before.
> What you want is an "anti join" plan, or at least a plan that mentions
> a "hashed subplan". Plain subplans are death performance-wise, because
> they amount to being nestloop joins rather than anything smarter. (In
> this case it's likely not choosing a hashed subplan because work_mem is
> too small to allow that.)
I've got the client looking for this and other values already. We'll
soon know...