Horton

Re: Assertion failure with LEFT JOINs among >500 relations

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Onder Kalaci <onderk@microsoft.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2020-10-16T17:00:29Z
Lists: pgsql-hackers 
I wrote:
> David Rowley <dgrowleyml@gmail.com> writes:
>> I've ended up leaving the NaN checks in the join costing functions.
>> There was no case mentioned in [1] that showed how we hit that
>> reported test case, so I'm not really confident enough to know I'm not
>> just reintroducing the same problem again by removing that.  The path
>> row estimate that had the NaN might not have been through
>> clamp_row_est(). Many don't.

> Hmm, I will try to find some time tomorrow to reconstruct that.

I'm confused now, because the v2 patch does remove those isnan calls?

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.

			regards, tom lane

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