主页 / dba / 问题 / 326311
Accepted
Leon
Asked: 2023-04-23 14:23:42 +0800 CST

如何在 Postgresql 中有效地获取时间间隔的绝对值？

  • 772

我在 Postgresql-11 中有一个巨大的表，如下所示：

CREATE TABLE my_huge_table(
    tick_time timestamp(6) with time zone NOT NULL,
    brok_time timestamp(6) with time zone,
    trade_day date NOT NULL,

    --other fields ...
    ...

    CONSTRAINT my_huge_table_pkey PRIMARY KEY (tick_time)
);

CREATE INDEX idx_my_huge_table_td_time ON my_huge_table USING brin
    ( trade_day, abs(tick_time - brok_time) );

然后我进行查询并希望它利用索引idx_my_huge_table_td_time，如下所示：

SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') 
  AND ABS(tick_time - brok_time) < INTERVAL '10 s';

但是 PostgreSQL 拒绝执行它，并说：

错误：函数 abs(interval) 不存在

第 3 行：AND ABS(tick_time - brok_time) < INTERVAL '10 s'

           ^

提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。

SQL 状态：42883 字符：525

看起来 funcabs()不能接受区间值作为参数。

然后，我改变了我的查询：

SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') 
  AND GREATEST(tick_time - brok_time, brok_time - tick_time) < INTERVAL '10 s';

这次可以执行了，但是没有利用到索引。

我的问题：

1.索引表达式应该怎么写？事实上，我希望它记录两个时间戳字段之间的距离（绝对间隔值）；

2.我应该如何编写可以使用上面索引的查询？

3.实际上GREATEST(tick_time - brok_time, brok_time - tick_time)不是一个好主意，因为它调用了两次计算。不是吗？

4.创建索引后，我注意到PostgreSQL上报的索引真正的DDL SQL是：

CREATE INDEX idx_my_huge_table_td_time ON public.my_huge_table USING brin
    (trade_day, abs(date_part('epoch'::text, tick_time - brok_time)));

表达式的值是否已转换为类型text？这显然不是我的期望！

postgresql

1 个回答

  1. Best Answer

    答案是创建一个生成的列，如下所示（下面的所有代码都可以在此处的小提琴上找到）：

    我有一个原始答案（显示在答案末尾），但我修改了它以使用Generated Column（又名“计算”或“虚拟”列）而不是Expression Index（又名“功能索引”）。

    这样做的好处是：

    • a）它是在插入时计算的，不必每次都重新计算并且

    • b) 它使 SQL更清晰 - 请参阅下面的原始答案。

    有一个缺点是它占用了更多空间，但我发现这通常不是一个关键问题（我自己从未见过）。不幸的是，PostgreSQL 还没有虚拟生成的列 - 请参阅链接。

    您的表定义应如下所示：

    CREATE TABLE t 
(
  ticktime TIMESTAMPTZ, 
  broktime TIMESTAMPTZ,
  trade_day DATE,
  -- 
  -- other fields
  --
  abs_b_minus_t INTERVAL GENERATED ALWAYS AS (GREATEST(broktime, ticktime) - LEAST(broktime, ticktime)) STORED
);

    然后创建一个索引abs_b_minus_t：

    CREATE INDEX t_ix ON t 
USING BRIN (trade_day, abs_b_minus_t );

    填充：

    INSERT INTO t VALUES
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE),
('2022-03-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-03-14'::DATE),
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-05-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE);

    然后我们运行：

    SELECT 
  ticktime - broktime AS t_minus_b,
  abs_b_minus_t
FROM t;

    结果：

    t_minus_b             abs_b_minus_t
02:00:00                   02:00:00
28 days 02:00:00    28 days 02:00:00
-88 days -21:00:00  88 days 21:00:00

    broktime所以，我们看到它在起作用——我们正在获取和之间的差值的绝对值tradtime。

    现在，我们可以检查索引使用情况——我们运行SET enable_seqscan = OFF;然后：

    EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT 
  broktime - ticktime
FROM t
WHERE abs_b_minus_t  < INTERVAL '30 DAYS';

    结果：

    QUERY PLAN
Bitmap Heap Scan on public.t  (cost=12.14..39.07 rows=423 width=16) (actual time=0.022..0.025 rows=2 loops=1)
  Output: (broktime - ticktime)
  Recheck Cond: (t.abs_b_minus_t < '30 days'::interval)
  Rows Removed by Index Recheck: 1
  Heap Blocks: lossy=1
  Buffers: shared hit=3
  ->  Bitmap Index Scan on t_ix  (cost=0.00..12.03 rows=1270 width=0) (actual time=0.017..0.017 rows=10 loops=1)
        Index Cond: (t.abs_b_minus_t < '30 days'::interval)
        Buffers: shared hit=2
Planning:
  Buffers: shared hit=1
Planning Time: 0.042 ms
Execution Time: 0.052 ms

    因此，我们t_ix在生成的字段上使用 BRIN 索引。

    原答案：

    CREATE TABLE t 
(
  ticktime TIMESTAMPTZ, 
  broktime TIMESTAMPTZ,
  trade_day DATE
  -- 
  -- other fields
  --
);

    现在，我们创建我们的功能索引如下：

    CREATE INDEX t_ix ON t 
USING BRIN (trade_day,  (GREATEST(broktime, ticktime) - LEAST(broktime, ticktime)));

    填充表格：

    INSERT INTO t VALUES
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE),
('2022-03-14 14:43:55'::TIMESTAMPTZ, '2022-02-14 12:43:55'::TIMESTAMPTZ, '2022-03-14'::DATE),
('2022-02-14 14:43:55'::TIMESTAMPTZ, '2022-05-14 12:43:55'::TIMESTAMPTZ, '2022-02-14'::DATE);

    现在我们测试：

    SELECT 
  ticktime - broktime AS t_minus_b,
  GREATEST(broktime, ticktime) - LEAST(broktime, ticktime) AS abs_b_minus_t
FROM t;

    结果：

    t_minus_b             abs_b_minus_t
02:00:00                   02:00:00
28 days 02:00:00    28 days 02:00:00
-88 days -21:00:00  88 days 21:00:00

    所以，我们有价值观和他们的绝对值。

    SELECT 
  broktime - ticktime
FROM t
WHERE GREATEST(broktime, ticktime) - LEAST(broktime, ticktime) < INTERVAL '30 DAYS';

    结果：

    ?column?
-02:00:00
-28 days -02:00:00

    要检查索引使用情况，我们禁用 seqscans：

    然后，我们运行：

    EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT 
  broktime - ticktime
FROM t
WHERE GREATEST(broktime, ticktime) - LEAST(broktime, ticktime) < INTERVAL '30 DAYS';

    结果：

    QUERY PLAN
Bitmap Heap Scan on public.t  (cost=12.17..57.59 rows=567 width=16) (actual time=0.041..0.044 rows=2 loops=1)
  Output: (broktime - ticktime)
  Recheck Cond: ((GREATEST(t.broktime, t.ticktime) - LEAST(t.broktime, t.ticktime)) < '30 days'::interval)
  Rows Removed by Index Recheck: 1
  Heap Blocks: lossy=1
  Buffers: shared hit=3
  ->  Bitmap Index Scan on t_ix  (cost=0.00..12.03 rows=1700 width=0) (actual time=0.027..0.027 rows=10 loops=1)
        Index Cond: ((GREATEST(t.broktime, t.ticktime) - LEAST(t.broktime, t.ticktime)) < '30 days'::interval)
        Buffers: shared hit=2
Planning:
  Buffers: shared hit=1
Planning Time: 0.044 ms
Execution Time: 0.096 ms

    所以，我们看到t_ix是配合比较高效的Bitmap使用

    • 2

相关问题