表格布局

重新设计该表，将营业时间（营业时间）存储为一组tsrange (range of timestamp without time zone )值。需要Postgres9.2或更高版本。
随便挑一个星期来安排你的开放时间。我喜欢这个星期：

• 一九九六年一月一日（星期一）* 至 * 一九九六年一月七日（星期日）*

这是最近的闰年，1月1日恰好是星期一。但在这个案子里可以是任意的一周。保持一致。
首先安装附加模块btree_gist：

CREATE EXTENSION btree_gist;

参见：

• Equivalent to exclusion constraint composed of integer and range

然后像这样创建表格：

CREATE TABLE hoo (
  hoo_id  serial PRIMARY KEY
, shop_id int NOT NULL -- REFERENCES shop(shop_id)     -- reference to shop
, hours   tsrange NOT NULL
, CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id with =, hours WITH &&)
, CONSTRAINT hoo_bounds_inclusive CHECK (lower_inc(hours) AND upper_inc(hours))
, CONSTRAINT hoo_standard_week CHECK (hours <@ tsrange '[1996-01-01 0:0, 1996-01-08 0:0]')
);

• one * 列hours替换所有列：

opens_on, closes_on, opens_at, closes_at

例如，从 * 星期三18：30 * 到 * 星期四05：00 * UTC的工作时间输入为：

'[1996-01-03 18:30, 1996-01-04 05:00]'

排除约束**hoo_no_overlap可防止每个车间的条目重叠。它是用GiST索引实现的，它也恰好支持我们的查询。请考虑下面讨论索引策略的章节“索引和性能”。
check约束hoo_bounds_inclusive**强制执行范围的包含边界，有两个值得注意的结果：

• 总是包括恰好落在下边界或上边界上的时间点。
• 同一商店的相邻条目实际上是不允许的。对于包含边界，这些边界将“重叠”，并且排除约束将引发异常。相邻的条目必须合并到一行中。除非它们 * 在星期天午夜前后环绕 *，在这种情况下，它们必须分成两行。下面的函数f_hoo_hours()处理了这个问题。

check约束**hoo_standard_week使用“range is contained by”运算符<@强制执行分期周的外部边界。
使用包含边界，您必须观察一个角情况**，其中时间在周日午夜结束：

'1996-01-01 00:00+0' = '1996-01-08 00:00+0'
 Mon 00:00 = Sun 24:00 (= next Mon 00:00)

您必须同时搜索两个时间戳。下面是一个相关的例子，它的***互斥***上限不会出现这个缺点：

• 在PostgreSQL中使用EXCLUDE防止相邻/重叠条目

函数f_hoo_time(timestamptz)

要“归一化”任何给定的timestamp with time zone：

CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz)
  RETURNS timestamp
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT timestamp '1996-01-01' + ($1 AT TIME ZONE 'UTC' - date_trunc('week', $1 AT TIME ZONE 'UTC'))
$func$;

PARALLEL SAFE仅适用于Postgres 9.6或更高版本。
该函数接受timestamptz并返回timestamp。它将相应周($1 - date_trunc('week', $1)（UTC时间）的经过时间间隔添加到我们的分期周的起始点。（date + interval产生timestamp。）

函数f_hoo_hours(timestamptz, timestamptz)

标准化范围并分割周一00：00的交叉点。此函数采用任意间隔（两个timestamptz），并生成一个或两个标准化的tsrange值。它涵盖***任何***合法输入，不允许其余的：

CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz)
  RETURNS TABLE (hoo_hours tsrange)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS
$func$
DECLARE
   ts_from timestamp := f_hoo_time(_from);
   ts_to   timestamp := f_hoo_time(_to);
BEGIN
   -- sanity checks (optional)
   IF _to <= _from THEN
      RAISE EXCEPTION '%', '_to must be later than _from!';
   ELSIF _to > _from + interval '1 week' THEN
      RAISE EXCEPTION '%', 'Interval cannot span more than a week!';
   END IF;

   IF ts_from > ts_to THEN  -- split range at Mon 00:00
      RETURN QUERY
      VALUES (tsrange('1996-01-01', ts_to  , '[]'))
           , (tsrange(ts_from, '1996-01-08', '[]'));
   ELSE                     -- simple case: range in standard week
      hoo_hours := tsrange(ts_from, ts_to, '[]');
      RETURN NEXT;
   END IF;

   RETURN;
END
$func$;

要INSERT * 单个 * 输入行：

INSERT INTO hoo(shop_id, hours)
SELECT 123, f_hoo_hours('2016-01-11 00:00+04', '2016-01-11 08:00+04');

对于 * 任意 * 数量的输入行：

INSERT INTO hoo(shop_id, hours)
SELECT id, f_hoo_hours(f, t)
FROM  (
   VALUES (7, timestamptz '2016-01-11 00:00+0', timestamptz '2016-01-11 08:00+0')
        , (8, '2016-01-11 00:00+1', '2016-01-11 08:00+1')
   ) t(id, f, t);

如果一个范围需要在周一00：00 UTC拆分，则每种方法都可以插入两行。

查询

通过调整后的设计，* 您的整个大型、复杂、昂贵的查询 * 可以替换为……这一点：

• • SELECT ***
• • FROM hoo**
• • WHERE hours @> f_hoo_time(now());**

为了一点悬念，我在解决方案上放了一个扰流板。将鼠标移到上面。
该查询由所述GiST索引支持，并且速度快，即使对于大表也是如此。
fiddle *（更多示例）
老麻雀
如果你想计算总营业时间（每家商店），这里有一个食谱：

• 在PostgreSQL中计算两个日期之间的工作时间

指标和性能

范围类型的包含操作符可以通过GiST或SP-GiST索引来支持。两者都可以用来实现排除约束，但只有GiST支持multicolumn indexes：
目前，只有B-tree、GiST、GIN和BRIN索引类型支持多列索引。
order of index columns matters：
多列GiST索引可以与涉及索引列的任何子集的查询条件一起使用。附加列上的条件限制索引返回的条目，但是第一列上的条件对于确定需要扫描索引的多少是最重要的。如果GiST索引的第一列只有几个不同的值，即使其他列中有许多不同的值，GiST索引也将相对无效。
所以我们有利益冲突。对于大表，shop_id的不同值要比hours多得多。

• 以shop_id开头的GiST索引写入和强制执行排除约束的速度更快。
• 但是我们在查询中搜索hours。先有这个专栏会更好。
• 如果我们需要在其他查询中查找shop_id，普通的btree索引会更快。
• 最重要的是，我发现hours上的SP-GiST索引对于查询来说是最快的。

基准测试

在旧笔记本电脑上使用Postgres 12进行新测试。我的生成虚拟数据的脚本：

INSERT INTO hoo(shop_id, hours)
SELECT id
     , f_hoo_hours(((date '1996-01-01' + d) + interval  '4h' + interval '15 min' * trunc(32 * random()))            AT TIME ZONE 'UTC'
                 , ((date '1996-01-01' + d) + interval '12h' + interval '15 min' * trunc(64 * random() * random())) AT TIME ZONE 'UTC')
FROM   generate_series(1, 30000) id
JOIN   generate_series(0, 6) d ON random() > .33;

结果约141k随机生成的行，约30k不同的shop_id，约12k不同的hours。表格大小8 MB。
我删除并重新创建了排除约束：

ALTER TABLE hoo
  DROP CONSTRAINT hoo_no_overlap
, ADD CONSTRAINT hoo_no_overlap  EXCLUDE USING gist (shop_id WITH =, hours WITH &&);  -- 3.5 sec; index 8 MB
    
ALTER TABLE hoo
  DROP CONSTRAINT hoo_no_overlap
, ADD CONSTRAINT hoo_no_overlap  EXCLUDE USING gist (hours WITH &&, shop_id WITH =);  -- 13.6 sec; index 12 MB

shop_id第一个比这个分布快4倍。
此外，我还测试了另外两个读取性能：

CREATE INDEX hoo_hours_gist_idx   on hoo USING gist (hours);
CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);  -- !!

在VACUUM FULL ANALYZE hoo;之后，我运行了两个查询：

• • • Q1**：深夜，只找到35行
• • • Q2**：下午，找到4547行。

结果

对每一个都进行了 * 仅索引扫描 *（当然，“无索引”除外）：

index                 idx size  Q1        Q2
------------------------------------------------
no index                        38.5 ms   38.5 ms 
gist (shop_id, hours)    8MB    17.5 ms   18.4 ms
gist (hours, shop_id)   12MB     0.6 ms    3.4 ms
gist (hours)            11MB     0.3 ms    3.1 ms
spgist (hours)           9MB     0.7 ms    1.8 ms  -- !

• SP-GiST和GiST对于查找很少结果的查询是一样的（GiST对于 * 非常 * 少的查询甚至更快）。
• SP-GiST的扩展性更好，结果越来越多，而且体积也更小。

如果读的比写的多得多（典型的用例），请按照开始时的建议保留排除约束，并创建一个额外的SP-GiST索引来优化读性能。