Infinity confuses planner (was Re: query plan)
От | Tom Lane |
---|---|
Тема | Infinity confuses planner (was Re: query plan) |
Дата | |
Msg-id | 21005.1003004277@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: Infinity confuses planner (was Re: query plan)
|
Список | pgsql-bugs |
"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
В списке pgsql-bugs по дате отправления: