Обсуждение: 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