Início / dba / Perguntas / 39589
Accepted
Stephane Rolland
Asked: 2013-04-10 11:07:03 +0800 CST

Otimizando consultas em um intervalo de carimbos de data/hora (duas colunas)

  • 772

Eu uso o PostgreSQL 9.1 no Ubuntu 12.04.

Preciso selecionar registros dentro de um intervalo de tempo: minha tabela time_limitstem dois timestampcampos e uma integerpropriedade. Existem colunas adicionais na minha tabela real que não estão envolvidas com esta consulta.

create table (
   start_date_time timestamp,
   end_date_time timestamp, 
   id_phi integer, 
   primary key(start_date_time, end_date_time,id_phi);

Esta tabela contém aproximadamente 2 milhões de registros.

Consultas como as seguintes levavam muito tempo:

select * from time_limits as t 
where t.id_phi=0 
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

Então tentei adicionar outro índice - o inverso do PK:

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

Fiquei com a impressão de que o desempenho melhorou: O tempo de acesso aos registros no meio da tabela parece ser mais razoável: algo entre 40 e 90 segundos.

Mas ainda são várias dezenas de segundos para valores no meio do intervalo de tempo. E mais duas vezes ao mirar no final da mesa (cronologicamente falando).

Tentei explain analyzepela primeira vez obter este plano de consulta:

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

Veja os resultados em depesz.com.

O que eu poderia fazer para otimizar a pesquisa? Você pode ver todo o tempo gasto verificando as duas colunas de carimbos de data/hora uma vez id_phidefinido como 0. E não entendo a grande varredura (60 mil linhas!) nos carimbos de data e hora. Eles não são indexados pela chave primária e idx_inversedeu adicionei?

Devo mudar de tipos de carimbo de data/hora para outra coisa?

Li um pouco sobre os índices GIST e GIN. Eu entendo que eles podem ser mais eficientes em certas condições para tipos personalizados. É uma opção viável para o meu caso de uso?

postgresql index

4 respostas

  1. Best Answer

    Para Postgres 9.1 ou posterior:

    CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);

    Na maioria dos casos, a ordem de classificação de um índice é pouco relevante. O Postgres pode escanear para trás praticamente com a mesma rapidez. Mas para consultas de intervalo em várias colunas, isso pode fazer uma enorme diferença. Intimamente relacionado:

    Considere sua consulta:

    SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

    A ordem de classificação da primeira coluna id_phino índice é irrelevante. Como é verificado quanto à igualdade ( =), ele deve vir primeiro. Você acertou. Mais nesta resposta relacionada:

    O Postgres pode pular rapidamente id_phi = 0e considerar as duas colunas a seguir do índice correspondente. Eles são consultados com condições de intervalo de ordem de classificação invertida ( <=, >=). No meu índice, as linhas de qualificação vêm primeiro. Deve ser o caminho mais rápido possível com um índice B-Tree 1 :

    • Você quer start_date_time <= something: o índice tem o carimbo de data/hora mais antigo primeiro.
    • Se ele se qualificar, verifique também a coluna 3.
      Recorra até que a primeira linha não seja qualificada (super rápido).
    • Você quer end_date_time >= something: o índice tem o carimbo de data/hora mais recente primeiro.
    • Se ele se qualificar, continue buscando linhas até que o primeiro não o faça (super rápido).
      Continue com o próximo valor para a coluna 2 ..

    O Postgres pode varrer para frente ou para trás. Do jeito que você tinha o índice, ele tem que ler todas as linhas correspondentes nas duas primeiras colunas e depois filtrar na terceira. Certifique-se de ler o capítulo Índices eORDER BY no manual. Se encaixa muito bem na sua pergunta.

    Quantas linhas correspondem nas duas primeiras colunas?
    Apenas alguns com um start_date_timepróximo ao início do intervalo de tempo da tabela. Mas quase todas as linhas estão id_phi = 0no final cronológico da tabela! Portanto, o desempenho se deteriora com tempos de início posteriores.

    Estimativas do planejador

    O planejador estima rows=62682para sua consulta de exemplo. Desses, nenhum se qualifica ( rows=0). Você pode obter estimativas melhores se aumentar a meta de estatísticas da tabela. Para 2.000.000 linhas...

    ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

    ... pode pagar. Ou ainda mais alto. Mais nesta resposta relacionada:

    Eu acho que você não precisa disso para id_phi(apenas alguns valores distintos, distribuídos uniformemente), mas para os carimbos de data/hora (muitos valores distintos, distribuídos de forma desigual).
    Eu também não acho que isso importe muito com o índice aprimorado.

    CLUSTER/ pg_repack / pg_squeeze

    Se você quiser mais rápido, você pode simplificar a ordem física das linhas em sua tabela. Se você puder bloquear sua tabela exclusivamente (em horários de folga, por exemplo), reescreva sua tabela e ordene as linhas de acordo com o índice com CLUSTER:

    CLUSTER time_limits USING idx_time_limits_inversed;

    Ou considere pg_repack ou o pg_squeeze posterior , que pode fazer o mesmo sem bloqueio exclusivo na tabela.

    De qualquer forma, o efeito é que menos blocos precisam ser lidos da tabela e tudo é pré-ordenado. É um efeito único que se deteriora com o tempo com gravações na tabela fragmentando a ordem de classificação física.

    Índice GiST no Postgres 9.2+

    1 Com a página 9.2+ existe outra opção, possivelmente mais rápida: um índice GiST para uma coluna de intervalo.

    • Existem tipos de intervalo integrados para timestampe timestamp with time zone: tsrange,tstzrange . Um índice btree normalmente é mais rápido para uma integercoluna adicional como id_phi. Menor e mais barato de manter também. Mas a consulta provavelmente ainda será mais rápida no geral com o índice combinado.

    • Altere sua definição de tabela ou use um índice de expressão .

    • Para o índice GiST de várias colunas em mãos, você também precisa do módulo adicional btree_gistinstalado (uma vez por banco de dados) que fornece as classes de operador para incluir um arquivo integer.

    O trio! Um índice GiST funcional de várias colunas :

    CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

    Use o operador "contém intervalo"@> em sua consulta agora:

    SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

    Índice SP-GiST no Postgres 9.3+

    Um índice SP-GiST pode ser ainda mais rápido para esse tipo de consulta - exceto isso, citando o manual :

    Atualmente, apenas os tipos de índice B-tree, GiST, GIN e BRIN suportam índices de várias colunas.

    Ainda é verdade no Postgres 12.
    Você teria que combinar um spgistíndice em apenas (tsrange(...))com um segundo btreeíndice em (id_phi). Com a sobrecarga adicional, não tenho certeza se isso pode competir.
    Resposta relacionada com um benchmark para apenas uma tsrangecoluna:

    • 241

  2. A resposta de Erwin já é abrangente, no entanto:

    Tipos de intervalo para timestamps estão disponíveis no PostgreSQL 9.1 com a extensão Temporal de Jeff Davis: https://github.com/jeff-davis/PostgreSQL-Temporal

    Nota: possui recursos limitados (usa Timestamptz, e você só pode ter o estilo '[)' sobreposto afaik). Além disso, há muitas outras razões para atualizar para o PostgreSQL 9.2.

    • 5

  3. Você pode tentar criar o índice de várias colunas em uma ordem diferente:

    primary key(id_phi, start_date_time,end_date_time);

    Eu postei uma vez uma pergunta semelhante também relacionada à ordenação de índices em um índice de várias colunas. A chave é tentar usar primeiro as condições mais restritivas para reduzir o espaço de busca.

    Editado : Erro meu. Agora vejo que você já tem esse índice definido.

    • 3

  4. Consegui aumentar rapidamente (de 1 seg para 70ms)

    Eu tenho uma tabela com agregações de muitas medidas e muitos níveis ( lcoluna) (30s, 1m, 1h, etc) existem duas colunas vinculadas ao intervalo: $spara início e $efim.

    Criei dois índices de várias colunas: um para início e outro para fim.

    Eu ajustei a consulta de seleção: selecione os intervalos em que o limite inicial está em um determinado intervalo. além disso, selecione intervalos em que seu limite final esteja em um determinado intervalo.

    O Explain mostra dois fluxos de linhas usando nossos índices de forma eficiente.

    Índices:

    drop index if exists agg_search_a;
CREATE INDEX agg_search_a
ON agg (measurement_id, l, "$s");

drop index if exists agg_search_b;
CREATE INDEX agg_search_b
ON agg (measurement_id, l, "$e");

    Selecione a consulta:

    select "$s", "$e", a, t, b, c from agg
where 
    measurement_id=0 
    and l =  '30s'
    and (
        (
            "$s" > '2013-05-01 02:05:05'
            and "$s" < '2013-05-01 02:18:15'
        )
        or 
        (
             "$e" > '2013-05-01 02:00:05'
            and "$e" < '2013-05-01 02:18:05'
        )
    )

;

    Explique:

    [
  {
    "Execution Time": 0.058,
    "Planning Time": 0.112,
    "Plan": {
      "Startup Cost": 10.18,
      "Rows Removed by Index Recheck": 0,
      "Actual Rows": 37,
      "Plans": [
    {
      "Startup Cost": 10.18,
      "Actual Rows": 0,
      "Plans": [
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 26,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone))",
          "Plan Rows": 29,
          "Parallel Aware": false,
          "Actual Total Time": 0.016,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.016,
          "Total Cost": 5,
          "Actual Loops": 1,
          "Index Name": "agg_search_a"
        },
        {
          "Startup Cost": 0,
          "Plan Width": 0,
          "Actual Rows": 36,
          "Node Type": "Bitmap Index Scan",
          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone))",
          "Plan Rows": 39,
          "Parallel Aware": false,
          "Actual Total Time": 0.011,
          "Parent Relationship": "Member",
          "Actual Startup Time": 0.011,
          "Total Cost": 5.15,
          "Actual Loops": 1,
          "Index Name": "agg_search_b"
        }
      ],
      "Node Type": "BitmapOr",
      "Plan Rows": 68,
      "Parallel Aware": false,
      "Actual Total Time": 0.027,
      "Parent Relationship": "Outer",
      "Actual Startup Time": 0.027,
      "Plan Width": 0,
      "Actual Loops": 1,
      "Total Cost": 10.18
    }
      ],
      "Exact Heap Blocks": 1,
      "Node Type": "Bitmap Heap Scan",
      "Plan Rows": 68,
      "Relation Name": "agg",
      "Alias": "agg",
      "Parallel Aware": false,
      "Actual Total Time": 0.037,
      "Recheck Cond": "(((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$s\" > '2013-05-01 02:05:05'::timestamp without time zone) AND (\"$s\" < '2013-05-01 02:18:15'::timestamp without time zone)) OR ((measurement_id = 0) AND ((l)::text = '30s'::text) AND (\"$e\" > '2013-05-01 02:00:05'::timestamp without time zone) AND (\"$e\" < '2013-05-01 02:18:05'::timestamp without time zone)))",
      "Lossy Heap Blocks": 0,
      "Actual Startup Time": 0.033,
      "Plan Width": 44,
      "Actual Loops": 1,
      "Total Cost": 280.95
    },
    "Triggers": []
  }
]

    O truque é que os nós do plano contêm apenas as linhas desejadas. Anteriormente, tínhamos milhares de linhas no nó do plano porque ele selecionou all points from some point in time to the very end, e o próximo nó removeu as linhas desnecessárias.

    • 1

relate perguntas