Re: designing time dimension for star schema

Mark Wong <markwkm(at)gmail(dot)com> writes:
> On Mon, Feb 10, 2014 at 9:20 AM, CS DBA <cs_dba(at)consistentstate(dot)com> wrote:
>> In the case of this being a timestamp I suspect the performance would
>> take a hit, depending on the size of your fact table and the
>> scope/volume of your DSS queries this could easily be a show stopper
>> based on the assumption that the database can do a numeric binary search
>> much faster than a timestamp search

> I guess I was hoping the extra 4 bytes from a timestamp, compared to a
> bigint, wouldn't be too significant yet I didn't consider postgres
> might do a binary search faster on an integer type than a timestamp.

Actually, Postgres timestamps *are* bigints under the hood, and
comparisons will be about the same speed as for bigints. Now, I/O is a
lot more expensive for timestamps ... but not index searches.

regards, tom lane