Re: Partial index on date column

"Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> writes:
> It's rumoured that Tom Lane once said:
>> The optimizer does not think that "pbx_date = CURRENT_DATE" satisfies
>> the partial index's WHERE condition. I don't see any really good way
>> around this; to improve matters there'd need to be some concept of a
>> plan that is only good for a limited time.

> Oh, OK. Thanks Tom. I can obviously work around this in my PHP code, it
> just struck me as odd. I assume then that the optimizer doesn't execute
> the function, and that that's done later on? Would the same be true of
> simple expressions such as 1 + 2?

No, the optimizer will simplify constant expressions as much as it can.
But CURRENT_DATE is, by definition, not a constant expression.

You could cheat: make a wrapper function for CURRENT_DATE that is marked
IMMUTABLE (or isCachable, pre-7.3). Then given something like "WHERE
pbx_date = my_date()", the optimizer would fold my_date() to a constant,
see that the constant satisfies the index's WHERE clause, and away you
go.

You'd have to be careful where you used this trick --- in a prepared
query or a plpgsql function, the pre-evaluation of my_date() would come
back to haunt you (unless maybe you are careful to end all your client
sessions at midnight). But for interactive queries it'd work well
enough.

regards, tom lane