I experienced the same thing. On the same platform. Any calculation on
the timestamp caused the planner to not use the index on the timestamp
field and fall back to a seqscan of the table. If I just put a timestamp
value there (no calculation) it used the index. I tried casting the
results of the calc to timestamp too and that did not help. I resorted to
calculating the dates in my code and just using a date with no calculation
in the query.
Anyone know if this has been fixed on 7.2?
>
> I was noticing something odd about the query planner. I'm using
> postgresql-7.1.3-2 so if this has been fixed in a more recent version
> please let me know...
>
> Ok here's a plan for my query. The meat is not so import its just that
> I am pulling data out of a really big table from March 11th to present.
> Now -- the query planner does the smart thing and uses the date index.
> So far so good.
[snip]
> Now the only difference here is that I add "+ 0" to the rval of the date.
> This makes the database lose its ability to use the date index. In this
> case it fails over to a much less useful index. But the question being...
> why does it not use the index here. One thing that crossed my mind was that
> perhaps the addition makes it into a Date type... well casting it back to
> Timestamp fixes nothing. Once I arithmetic on the timestamp I just cant
> find any way to use the index. Maybe it's the math that messes it up...
> for reason thinking the result of a "+" is not cachable. ( yes I'm sure I
> can get around this by making a "iscachable" function and passing the
> timestamp through that...
[snip]
