0

I'm preparing an 'ideal' events fact table to hand off to our engineers to build. My question is, if we have an event timestamp field event_time and I want to index the dates only, is it possible to index event_time::date or must I request a separate date field based on the event_time for indexing purposes?

Doug Fir
  • 19,971
  • 47
  • 169
  • 299
  • 3
    You can do `create index on ideal ((event_time::date));`. Whether it will be a benefit is something you would have to test. My suspicion is not, as querying on the `timestamp` means querying on the `date` anyway. – Adrian Klaver Dec 12 '21 at 17:36
  • 1
    You can create a secondary [auto generated] column for it or you can index the expression as shown by @AdrianKlavier. However, if you index the expression, make sure your queries use the exact same expression in the search predicates. Slight variations could prevent the index to be used. – The Impaler Dec 12 '21 at 18:19
  • Got it, thanks for the info – Doug Fir Dec 12 '21 at 18:36
  • Why have you got a timestamp in your fact table - how are you linking it to a dimension table? – NickW Dec 12 '21 at 20:25

0 Answers0