Обсуждение: Infinity confuses planner (was Re: query plan)

"Mike Quinn" <mquinn@co.merced.ca.us> writes:
[ query behaves okay as
    WHERE  Crops.change_e > '10/1/2001'
but not as
    WHERE  '10/1/2001' < Crops.change_e
]

Ah-hah, I see it.  The critical factor is that you have some +infinity
values in that timestamp column, so that the column data range recorded
by VACUUM ANALYZE is some-finite-value to +infinity.  When scalarltsel
tries to estimate the fraction of rows that this WHERE clause matches,
it does

            denominator = high - low;
            if (flag & SEL_RIGHT)
                numerator = val - low;
            else
                numerator = high - val;
            result = numerator / denominator;

which in one case computes infinity/infinity (yielding NAN) and in the
other case computes some-finite-value/infinity (yielding zero).  So we
get a NAN for the selectivity and then all the subsequent computations
in the planner are infected with NANs, leading it to select some random
plan or other as the "cheapest".

The reason I didn't see it here is that on my platform, the infinity
timestamp values aren't represented as real IEEE infinities, and so the
result isn't NAN.

Seems like we could fix this either by forbidding use of real infinity
for timestamp and float8 values ... probably not workable for float8,
even if it's okay for timestamp ... or by trying to defend against
infinity and NAN results in the selectivity subroutines.

Comments anyone?

            regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Shouldn't infinity be treated similar to NULL.

Nope.  It has a definite position in the sort order.

            regards, tom lane

> The reason I didn't see it here is that on my platform, the infinity
> timestamp values aren't represented as real IEEE infinities, and so the
> result isn't NAN.
>
> Seems like we could fix this either by forbidding use of real infinity
> for timestamp and float8 values ... probably not workable for float8,
> even if it's okay for timestamp ... or by trying to defend against
> infinity and NAN results in the selectivity subroutines.

Shouldn't infinity be treated similar to NULL.  I know they are
different but they are clearly off-the-scale type values.  The problem
is that it is type-specific and not general enough for the optimizer.
Tough one.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Shouldn't infinity be treated similar to NULL.
>
> Nope.  It has a definite position in the sort order.

I was thinking about the optimizer's histogram, not the index ordering.
Anyway, seems you got it fixed already.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026