On 2012-02-17, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 02/16/2012 02:45 PM, John R Pierce wrote:
>> On 02/16/12 2:34 PM, David Salisbury wrote:
>>>
>>> Would it not be advantageous to replicate information in the above
>>> form if you wanted to, say, get all records in the month of May, and
>>> therefore create an index on the month field? I would think that
>>> would be more efficient than creating a functional index on a timestamp.
>>> And if you're not too picky, that would give an index that couldn't
>>> be done on a timestamptz field, as that's mutable.
>>
>> an index on a timestamp will work just fine on date_trunc('month',
>> timestampfield)
>>
>>
>>
> You can try but PostgreSQL will respond:
> ...functions in index expression must be marked IMMUTABLE...
>
> The current month returned by extract or date_trunc depends on the
> current time zone. New York will see March 3-hours ahead of us
> left-coasters.
David is claiming than a funtional index isnt needed at all, perhaps
postgres knows what date_trunc does and knows how to use an ordinary
btree index. (he could be right)
Date_trunc doesn't get you the month, it gets you the year and month,
date_runc on timestamp or date is immutable and can be indexed upon.
the original post was storing month and day (this information could be
useful for finding records having anniversaries eg: birthdays) it was
not storing year, month and day can be got using extract, the same
rules of mutablilty apply.
> Note: storing the month in a separate field does not solve this problem
> - it just shuffles it around and requires additional mechanisms to
> update that field when the timestamp field changes.
yeah, I can't see any way that storing these values separately is more
efficient, the indexes are the same size, but the data records on disk
are larger. I don't think postgres will re-compute the function result
unless one of the function's inputs hase been changed.
--
⚂⚃ 100% natural