Horton

Re: Assertion failure with LEFT JOINs among >500 relations

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Onder Kalaci <onderk@microsoft.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2020-10-09T00:36:18Z
Lists: pgsql-hackers 
On Fri, 9 Oct 2020 at 12:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> If we did want to do something here, I'd consider something like
>
>         if (isnan(outer_skip_rows))
>             outer_skip_rows = 0;
>         if (isnan(inner_skip_rows))
>             inner_skip_rows = 0;

Are you worried about the costs above the join that triggers that
coming out as NaN with that fix? It appears that's the case. Cost
comparisons of paths with that are not going to do anything along the
lines of sane.

I guess whether or not that matters depends on if we expect any real
queries to hit this, or if we just want to stop the Assert failure.

... 500 joins. I'm willing to listen to the explanation use case, but
in absence of that explanation, I'd be leaning towards "you're doing
it wrong".  If that turns out to be true, then perhaps your proposed
fix is okay.

David

Commits

  1. Relax some asserts in merge join costing code

  2. Prevent overly large and NaN row estimates in relations

  3. Avoid a couple of zero-divide scenarios in the planner.

  4. Guard against incoming rowcount estimate of NaN in cost_mergejoin().

  5. When a relation has been proven empty by constraint exclusion, propagate that