"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