On Wed, 28 Aug 2019 at 01:02, Ron <[email protected]> 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?"
