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-18T21:01:00Z
Lists: pgsql-hackers 
On Sat, 17 Oct 2020 at 06:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm confused now, because the v2 patch does remove those isnan calls?

I think that was a case of a last-minute change of mind and forgetting
to attach the updated patch.

> I rechecked the archives, and I agree that there's no data about
> exactly how we could have gotten a NaN here.  My guess though is
> infinity-times-zero in some earlier relation size estimate.  So
> hopefully the clamp to 1e100 will make that impossible, or if it
> doesn't then clamp_row_est() should still prevent a NaN from
> propagating to the next level up.
>
> I'm good with the v2 patch.

Thanks a lot for having a look. I'll proceed in getting the v2 which I
sent earlier into master.

For the backbranches, I think I go with something more minimal in the
form of adding:

if (outer_path_rows <= 0 || isnan(outer_path_rows))
    outer_path_rows = 1;
+else if (isinf(outer_path_rows))
+    outer_path_rows = DBL_MAX;

and the same for the inner_path_rows to each area in costsize.c which
has that code.

Wondering your thoughts on that.

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