Re: Quick Date/Time Index Question

On Sep 22, 2011, at 2:13 PM, David Johnston wrote:

> Hey,
>
> On 9.0.4
>
> I have a database field that stores a timestamp to second+ precision; however, I want to search against it only to day precision. If I leave the field in second precision and try to “WHERE field BETWEEN date0 AND date0” I get no results (OK, fine) but then I cast the field to date “WHERE field::date BETWEEN date0 AND date0” and get the expected results. So now I want to index “field::date” by I cannot create a functional index on “field::date” OR “CAST(field AS date)” OR “date_trunc(‘day’,field)” due to either syntax (::) or non-IMMUTABLE function errors (cast; date_trunc).
>
> Is there some other way to create an index on only the “date” portion of the field? Is it even necessary since any index ordered on timestamp is also, by definition, order on date as well?

It seems to me that it wouldn't be necessarily, but maybe there are some cases where if you had just the date in the index and could therefore test for equality instead of range, it would allow the planner to do more efficient plans. Maybe.

I suspect in most cases, the performance differences would be negligible.