On Wed, 28 Aug 2019 at 01:02, Ron <ronljohnso...@gmail.com> wrote: > We did something similar to that, except all the columns were in one > single table. It wasn't a data warehouse, though: the RDBMS we used could > be coerced into using a date index when large ranges were needed in detail > tables by joining it to T_CALENDAR, and doing the range filter on > T_CALENDAR. >
Ah, interesting! I like it, mostly... The one bad thing would be that this sorta mis-matches timestamp with timezone which is a more or less continuous data type (rather than discrete, like date). I could see an argument, in that environment, to put a DATE type onto detail tables if they are inevitably being joined to T_CALENDAR. I recall we had a case where some reports were ridiculously inefficient because a query involved effectively a "where date_part(something, column)" clause that made that into a Seq Scan. Alternatively (and I'm thinking out loud here), I wonder if putting a range type with a pair of timestamps would help with matching, as the range type would put the full range of each day into the table; you could have full date/time stamps match the calendar table via the range type... select [stuff] from tz_table t, t_calendar tc where [various stuff] and tc.t_workday and tc.t_date between '2017-01-01' and '2017-02-01' and (to get the range bit) t.original_tstz <@ tc.t_range; -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"