Início / dba / Perguntas / 326311
Accepted
Leon
Asked: 2023-04-23 14:23:42 +0800 CST

Como obter com eficiência o valor absoluto de um intervalo de tempo no Postgresql?

  • 772

Eu tenho uma tabela enorme no Postgresql-11 da seguinte forma:

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) );

Aí eu faço uma query e quero que ela aproveite o index idx_my_huge_table_td_time, assim:

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';

Mas o PostgreSQL se recusou a executá-lo e disse:

ERRO: a função abs(intervalo) não existe

LINHA 3: AND ABS(tick_time - brok_time) < INTERVAL '10 s'

           ^

DICA: Nenhuma função corresponde ao nome e aos tipos de argumento fornecidos. Pode ser necessário adicionar conversões de tipo explícito.

Estado SQL: 42883 Caractere: 525

Parece que o func NÃOabs() pode aceitar um valor de intervalo como argumento.

Então, mudei minha consulta:

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';

Desta vez pode ser executado, mas não aproveitou o índice.

Minhas perguntas:

1.Como devo compor a expressão do índice? Na verdade, quero registrar uma distância (valor de intervalo absoluto) entre dois campos de carimbo de data/hora;

2.Como devo codificar a consulta que pode usar o índice acima?

3.Na verdade , NÃOGREATEST(tick_time - brok_time, brok_time - tick_time) é uma boa ideia, pois invocou a computação duas vezes. Não é?

4.Após criado o índice, observo que a DDL SQL real do índice reportado pelo PostgreSQL é:

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)));

O valor da expressão foi convertido em um texttipo? Aparentemente NÃO é minha expectativa!

postgresql

1 respostas

  1. Best Answer

    A resposta é criar uma coluna gerada da seguinte maneira (todo o código abaixo está disponível no violino aqui ):

    Eu tinha uma resposta original (mostrada no final da resposta), mas a revisei para usar uma Generated Column(também conhecida como coluna "Computada" ou "Virtual") em vez de uma Expression Index(também conhecida como "Índice Funcional").

    Isto tem as vantagens de:

    • a) É calculado na inserção e não precisa ser recalculado toda vez e

    • b) torna o SQL muito mais claro - veja a resposta original abaixo.

    Há uma desvantagem em que ele usa mais espaço, mas descobri que isso normalmente não é um problema crítico (nunca vi isso pessoalmente). Infelizmente, o PostgreSQL ainda não possui colunas virtuais geradas - veja o link.

    A definição da sua tabela deve ser a seguinte:

    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
);

    Em seguida, crie um índice em abs_b_minus_t:

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

    Preencher:

    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);

    Então corremos:

    SELECT 
  ticktime - broktime AS t_minus_b,
  abs_b_minus_t
FROM t;

    Resultado:

    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

    Então, vemos que está funcionando - estamos obtendo valores absolutos da diferença entre broktimee tradtime.

    Agora, podemos verificar o uso do índice - executamos SET enable_seqscan = OFF;e então:

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

    Resultado:

    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

    Então, estamos usando t_ixcom o índice BRIN em nosso campo gerado.

    Resposta original:

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

    Agora, criamos nosso índice funcional da seguinte maneira:

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

    Preencha a tabela:

    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);

    Agora testamos:

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

    Resultado:

    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

    Assim, temos os valores e seus absolutos.

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

    Resultado:

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

    Para verificar o uso do índice, desabilitamos os seqscans:

    Em seguida, executamos:

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

    Resultado:

    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

    Então, vemos que t_ixé usado com o bitmap relativamente eficiente

    • 2

relate perguntas