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-08T23:59:15Z
Lists: pgsql-hackers 
David Rowley <dgrowleyml@gmail.com> writes:
> I admit it's annoying to add cycles to clamp_row_est() for such insane cases.

I poked at this a bit more closely, and noted that the actual problem is
that when we do this:

	outer_skip_rows = rint(outer_path_rows * outerstartsel);

we have outer_path_rows = inf, outerstartsel = 0, and of course inf times
zero is NaN.  So we end up asserting "NaN <= Inf", not "Inf <= Inf"
(which wouldn't have caused a problem).

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;

(We shouldn't need that for outer_rows/inner_rows, since the endsel
values can't be 0.)  Messing with clamp_row_est would be a much more
indirect way of fixing it, as well as having more widespread effects.

In the end though, I'm still not terribly excited about this.

			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