Início / dba / Perguntas / 254268
Accepted
TmTron
Asked: 2019-11-28 09:01:38 +0800 CST

Como usar o índice para junção de intervalo de tempo simples?

  • 772

Eu tenho uma tabela de série temporal grande (~ 100 milhões de linhas) t_16no Postgres 11.5 , onde a chave primária é um campo abs_date_timedo tipo timestamp.

Esta é uma continuação desta pergunta:

Inicialmente pensei que estava relacionado a um CTE. Mas esta consulta é lenta, mesmo sem CTE.

Como posso fazer a consulta a seguir usar a chave primária index , para evitar uma verificação completa da tabela?

tsrange

Esta consulta leva ~ 20 segundos no meu PC de desenvolvimento:

SELECT t_16_gen.*
FROM t_16_gen,
     (VALUES (tsrange('["2019-11-26 12:00:00","2019-11-26 12:00:15")'))
           , (tsrange('["2019-11-26 13:00:00","2019-11-26 13:00:15")'))) as ranges (time_range)
WHERE (abs_date_time >= LOWER(ranges.time_range)
    AND abs_date_time <  UPPER(ranges.time_range));

Explique o plano:

Gather  (cost=1000.00..6185287.15 rows=20571433 width=80)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.00..4127143.85 rows=8571430 width=80)
        Join Filter: ((t_16_gen.abs_date_time >= lower("*VALUES*".column1)) AND (t_16_gen.abs_date_time < upper("*VALUES*".column1)))
        ->  Parallel Seq Scan on t_16_gen  (cost=0.00..1620000.38 rows=38571438 width=80)
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32)

Na produção, o conjunto de tsranges vem de uma UDF - mas sempre haverá apenas alguns intervalos (<200) e cada intervalo terá menos de 1500 linhas e os intervalos não se sobreporão.

Timestamps simples em vez detsrange

Quando usamos timestamps diretamente (ou seja, não usando tsrange LOWER()e UPPER()), a consulta já é mais rápida. Esta consulta leva ~ 7 segundos no meu PC de desenvolvimento:

SELECT t_16_gen.*
FROM t_16_gen,
     (VALUES ('2019-11-26 12:00:00'::timestamp,'2019-11-26 12:00:15'::timestamp)
           , ('2019-11-26 13:00:00','2019-11-26 13:00:15')) as ranges (start_incl, end_excl)
WHERE (abs_date_time >= ranges.start_incl
    AND abs_date_time <  ranges.end_excl);

Explique o plano:

Nested Loop  (cost=0.00..5400001.28 rows=20571433 width=80)
  Join Filter: ((t_16_gen.abs_date_time >= "*VALUES*".column1) AND (t_16_gen.abs_date_time < "*VALUES*".column2))
  ->  Seq Scan on t_16_gen  (cost=0.00..2160000.50 rows=92571450 width=80)
  ->  Materialize  (cost=0.00..0.04 rows=2 width=16)
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=16)

ORcondições = RÁPIDO

Quando reescrevo a consulta para usar condições OR , é rápido. Esta consulta leva ~ 200ms no meu PC de desenvolvimento: 

SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time >= '2019-11-26 12:00:00' AND abs_date_time < '2019-11-26 12:00:15')
   OR (abs_date_time >= '2019-11-26 13:00:00' AND abs_date_time < '2019-11-26 13:00:15');

Explique o plano:

Gather  (cost=13326.98..1533350.92 rows=923400 width=80)
  Workers Planned: 2
  ->  Parallel Bitmap Heap Scan on t_16_gen  (cost=12326.98..1440010.92 rows=384750 width=80)
        Recheck Cond: (((abs_date_time >= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 12:00:15'::timestamp without time zone)) OR ((abs_date_time >= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 13:00:15'::timestamp without time zone)))
        ->  BitmapOr  (cost=12326.98..12326.98 rows=925714 width=0)
              ->  Bitmap Index Scan on t_16_pkey  (cost=0.00..5932.64 rows=462857 width=0)
                    Index Cond: ((abs_date_time >= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 12:00:15'::timestamp without time zone))
              ->  Bitmap Index Scan on t_16_pkey  (cost=0.00..5932.64 rows=462857 width=0)
                    Index Cond: ((abs_date_time >= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 13:00:15'::timestamp without time zone))

UNION= RÁPIDO

Quando reescrevo a consulta para usar condições UNION , também é rápido. Esta consulta leva ~ 220ms no meu PC de desenvolvimento: 

SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time >= '2019-11-26 12:00:00' AND abs_date_time < '2019-11-26 12:00:15')
UNION
SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time >= '2019-11-26 13:00:00' AND abs_date_time < '2019-11-26 13:00:15');

Explique o plano:

Unique  (cost=1032439.64..1069468.20 rows=925714 width=80)
  ->  Sort  (cost=1032439.64..1034753.93 rows=925714 width=80)
"        Sort Key: t_16_gen.abs_date_time, t_16_gen.c_422, t_16_gen.c_423, t_16_gen.c_424, t_16_gen.c_425, t_16_gen.c_426, t_16_gen.c_427, t_16_gen.c_428, t_16_gen.c_429, t_16_gen.c_430, t_16_gen.c_431, t_16_gen.c_432, t_16_gen.c_433, t_16_gen.c_434, t_16_gen.c_435"
        ->  Append  (cost=0.57..892513.13 rows=925714 width=80)
              ->  Index Scan using t_16_pkey on t_16_gen  (cost=0.57..439313.71 rows=462857 width=80)
                    Index Cond: ((abs_date_time >= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 12:00:15'::timestamp without time zone))
              ->  Index Scan using t_16_pkey on t_16_gen t_16_gen_1  (cost=0.57..439313.71 rows=462857 width=80)
                    Index Cond: ((abs_date_time >= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time < '2019-11-26 13:00:15'::timestamp without time zone))

Reproduzindo o problema

Para reproduzir o problema, posso criar uma nova tabela e preenchê-la com dados fictícios. Em seguida, reinicie o banco de dados antes de cada teste, para que os dados não sejam armazenados em cache.
Nota: a consulta de inserção pode ser executada por vários minutos!

create table if not exists t_16_gen (
    abs_date_time timestamp constraint t_16_pkey primary key,
    c_422 bigint,
    c_423 bigint,
    c_424 real,
    c_425 real,
    c_426 real,
    c_427 real,
    c_428 real,
    c_429 real,
    c_430 bigint,
    c_431 real,
    c_432 real,
    c_433 real,
    c_434 bigint,
    c_435 real
);

INSERT INTO t_16_gen
SELECT ts, 1,2,3,4,5,6,7,8,9,10,11,12,13,14
FROM (SELECT generate_series('2019-11-26'::timestamp, '2019-11-27', '1 millisecond') as ts) as gs;
postgresql index

1 respostas

  1. Best Answer

    Sua última consulta (rápida) tem duas condições idênticas WHERE, que o Postgres é capaz de identificar e dobrar para um . Daí o plano mais simples com apenas uma única condição de índice.

    Fica mais caro com várias condições diferentes . Mas o Postgres ainda continua operando com base em estimativas para valores reais de entrada. Tente com um ou mais intervalos grandes na WHEREcláusula incluindo a maior parte ou toda a tabela e você verá uma varredura sequencial.

    Isso é diferente em princípio para suas duas primeiras consultas com base em uma VALUESexpressão. Lá, o Postgres bifurca dois casos:

    • Para uma linha de entrada, o Postgres analisa os valores reais e produz o mesmo plano da terceira consulta com uma única WHEREcondição, com estimativas baseadas nos valores reais de entrada. Você obtém índice / índice de bitmap / varredura sequencial de acordo.
    • Para mais de uma linha de entrada, o Postgres para de olhar para valores individuais e prepara um plano de consulta com base em estimativas genéricas e no número real de linhas de entrada. Você pode fornecer uma VALUESexpressão com 5 linhas resultando em nenhum resultado ou 5 linhas retornando a tabela inteira, será o mesmo plano de consulta.

    Testado no Postgres 11.

    Também esteja ciente de que unir a um conjunto (a VALUESexpressão) é logicamente diferente de adicionar vários ORpredicados de intervalo 'ed. As linhas que correspondem a vários intervalos de tempo no conjunto são retornadas várias vezes, enquanto o segundo formulário retorna apenas uma única instância, mesmo que corresponda a vários predicados.

    Assim, a segunda forma com muitos OR's naturalmente favorece as varreduras de índice de bitmap, que dobram vários hits em um automaticamente. O Postgres não tem ideia de que suas condições nunca se sobrepõem. (Ou eles vão? Então você tem um problema maior.) Se os dados em sua tabela são classificados fisicamente por tempo (correspondendo à sua coluna PK abs_date_time), então isso ainda deve funcionar a seu favor por acidente .

    Mas como suas linhas são bastante largas (menos tuplas por página) e se você tiver muitos intervalos de tempo (até 200?), favorecer indevidamente as varreduras de índice de bitmap pode ser uma desvantagem afinal e as varreduras de índice simples podem ser mais rápidas.

    Solução

    UNION ALLdeve ser superior para você!

    SELECT * FROM s_28.t_16 WHERE abs_date_time >= '2019-11-26 12:00:00' AND abs_date_time < '2019-11-26 12:10:00'
UNION ALL
SELECT * FROM s_28.t_16 WHERE abs_date_time >= '2019-11-26 13:00:00' AND abs_date_time < '2019-11-26 13:10:00'
-- add (many) more
;

    Em primeiro lugar, é a melhor combinação para a lógica em funcionamento aqui. As chances são muito maiores de que versões futuras do Postgres continuem usando bons planos de consulta.

    Dessa forma, o Postgres usa estimativas baseadas na entrada real para cada um SELECT- e dadas suas especificações (todos os intervalos são pequenos), a consulta nunca deve degradar para varreduras sequenciais, desde que as estatísticas da sua tabela não sejam completamente enganosas.

    E as varreduras de índice não estão mais em desvantagem (injusta) em relação às varreduras de índice de bitmap.

    • 2

relate perguntas