Filtre no array text[] e classifique no carimbo de data/hora

Considerações gerais

A otimização do índice sempre depende da imagem completa . Tamanho da tabela, tamanho da linha, cardinalidades, frequências de valor, seletividade de consultas típicas, versão do Postgres, padrões de acesso típicos, etc.

Seu caso é particularmente difícil por dois motivos:

1. Colunas diferentes usadas em WHEREe ORDER BY.

2. O filtro na matriz é mais eficiente com o índice GIN ou GiST, mas nenhum dos tipos de índice produz uma saída classificada. O manual:

   Dos tipos de índice atualmente suportados pelo PostgreSQL, apenas a árvore B pode produzir saída classificada — os outros tipos de índice retornam linhas correspondentes em uma ordem não especificada e dependente da implementação.

Você pode criar um índice GIN de várias colunas em (tags, maker_date)ou até mais colunas (a ordem das colunas do índice é irrelevante para índices GIN). Mas você precisa ter o módulo adicional btree_gininstalado. Instruções:

• Junção interna usando uma coluna de matriz

E não vai ajudar na parte ORDER BYdo seu problema.

Mais um esclarecimento: OFFSET m LIMIT ngeralmente é quase tão caro quanto LIMIT m+n.

Solução para especificações adicionadas

Você esclareceu: apenas 6 tags distintas são possíveis. Isso é crucial.

Sua mesa é grande e sua definição de mesa deixa espaço para melhorias. O tamanho é importante para mesas grandes. Seus números (30 GB, 10 milhões de linhas) também sugerem uma grande média. tamanho da linha de ~ 3 KB. Ou você tem mais colunas do que mostra ou a tabela incha e precisa de uma VACUUM FULLexecução (ou similar) ou sua valuecoluna é grande e TOASTada, o que tornaria minhas melhorias ainda mais eficazes, pois a relação principal é reduzida à metade de seu tamanho ou menos com isso :

CREATE TABLE tags_tmp (
  id         int PRIMARY KEY -- assuming PK
, tags       int NOT NULL    -- also assuming NOT NULL
, value      text
, maker_date timestamp NOT NULL  -- NOT NULL!
);

A ordem das colunas é relevante devido ao preenchimento de alinhamento. Detalhes:

• Configurando o PostgreSQL para desempenho de leitura

Mais importante, isto: tags int. Por quê?

As matrizes têm uma sobrecarga considerável de 24 bytes (semelhante a uma linha), mais itens reais.

• Calculando e economizando espaço no PostgreSQL

Portanto, um text[]com 1-6 itens como você demonstra ('engraçado', 'inspiração', ...) ocupa ~ 56 bytes em avg . E 6 valores distintos podem ser representados por apenas 6 bits de informação (supondo que a ordem de classificação do array seja irrelevante). Poderíamos comprimir ainda mais, mas escolhi o integertipo conveniente (ocupa 4 bytes ), que oferece espaço para até 31 tags distintas. Isso deixa espaço para adições posteriores sem alterações no esquema da tabela. Justificativa detalhada:

• Devo usar a string de bits do PostgreSQL?

Suas tags são mapeadas para bits em um bitmap, 'a'sendo o bit menos significativo (à direita):

tag:       a | b | c | d |  e |  f
position:  0 | 1 | 2 | 3 |  4 |  5
int value: 1 | 2 | 4 | 8 | 16 | 32

Portanto, a matriz de tags '{a,d,f}'é mapeada para 41. Você pode usar qualquer string arbitrária em vez de 'a'-'f', não importa.

Para encapsular a lógica sugiro duas funções auxiliares , facilmente expansíveis:

tags -> inteiro:

CREATE OR REPLACE FUNCTION f_tags2int(text[])
  RETURNS int AS
$func$
SELECT bit_or(CASE x
            WHEN 'a' THEN  1
            WHEN 'b' THEN  2
            WHEN 'c' THEN  4
            WHEN 'd' THEN  8
            WHEN 'e' THEN 16
            WHEN 'f' THEN 32
            -- more?
           END)
FROM    unnest ($1) x
$func$  LANGUAGE SQL IMMUTABLE;

inteiro -> tags:

CREATE OR REPLACE FUNCTION f_int2tags(int)
  RETURNS text[] AS
$func$
SELECT array_remove(ARRAY [CASE WHEN $1 &  1 > 0 THEN 'a' END
                         , CASE WHEN $1 &  2 > 0 THEN 'b' END
                         , CASE WHEN $1 &  4 > 0 THEN 'c' END
                         , CASE WHEN $1 &  8 > 0 THEN 'd' END
                         , CASE WHEN $1 & 16 > 0 THEN 'e' END
                         , CASE WHEN $1 & 32 > 0 THEN 'f' END], NULL)
                         -- more? 
$func$  LANGUAGE SQL IMMUTABLE;

Básico aqui:

• Posso converter um monte de colunas booleanas em um único bitmap no PostgreSQL?

Por conveniência, você pode adicionar uma visualização para exibir tags como matriz de texto como você tinha:

CREATE VIEW tags_tmp_pretty AS
SELECT id, tags
     , f_int2tags(tags) AS tags_pretty
     , maker_date, value
FROM   tags_tmp;

Agora sua consulta básica pode ser:

SELECT id, tags, maker_date, value
FROM   tags_tmp
WHERE  tags & f_tags2int('{a,b}') > 0  -- any of the tags matched
ORDER  by maker_date DESC
LIMIT  5;

Usando o operador AND binário& . Existem mais operadores para manipular a coluna. get_bit()e set_bit()dos operadores de cadeia binária também são convenientes.

A consulta acima já deveria ser mais rápida, apenas para as operadoras de menor porte e mais baratas, mas nada revolucionário ainda. Para torná-lo rápido , precisamos de índices, e o acima não pode usar um índice ainda.

Um índice parcial para cada tag:

CREATE INDEX foo_tag_a ON tags_tmp(maker_date DESC) WHERE tags & 1 > 0;
CREATE INDEX foo_tag_b ON tags_tmp(maker_date DESC) WHERE tags & 2 > 0;
...
CREATE INDEX foo_tag_f ON tags_tmp(maker_date DESC) WHERE tags & 32 > 0;

Esta consulta é equivalente à anterior, mas pode utilizar os índices:

SELECT *
FROM   tags_tmp_pretty
WHERE (tags & f_tags2int('{a}') > 0   -- same as tags & 1
    OR tags & f_tags2int('{e}') > 0)  -- same as tags & 32
ORDER  BY maker_date DESC
LIMIT  10;

Postgres pode combinar várias varreduras de índice de bitmap em uma BitmapOretapa de forma muito eficiente, como demonstrado neste SQL Fiddle .

Você pode adicionar outra condição de índice para limitar os índices a maker_date> algum registro de data e hora constante (e repetir a condição literal nas consultas) para reduzir seu tamanho (massivamente). Exemplo relacionado:

• Otimização do índice com datas

Mais sofisticado:

• Adicionar restrição de data e hora a um índice parcial de várias colunas do PostgreSQL

Outras respostas relacionadas:

• Como acelerar a classificação ORDER BY ao usar o índice GIN no PostgreSQL?

• O índice espacial pode ajudar uma consulta "range - order by - limit"

Ou apenas 6 booleancolunas ...

Apenas 6 colunas booleanas podem ser uma escolha ainda melhor. Existem alguns prós e contras para qualquer solução ...

CREATE TABLE tags_tmp (
  id         int PRIMARY KEY -- assuming PK
, tag_a      bool 
, tag_b      bool 
  ...
, tag_f      bool 
, value      text
, maker_date timestamp NOT NULL  -- NOT NULL!
);

Você pode definir os sinalizadores NOT NULL, dependendo do seu caso de uso completo.

Considerar:

• Devo usar a string de bits do PostgreSQL?

Índices parciais simplesmente:

CREATE INDEX foo_tag_a ON tags_tmp(maker_date DESC) WHERE tag_a;
CREATE INDEX foo_tag_b ON tags_tmp(maker_date DESC) WHERE tag_b;

etc.

Alternativa para o seu caso especial

Pensando um pouco mais, já que todas as suas poucas tags são tão comuns , e combinar várias tags com OR é ainda menos seletivo, será mais rápido ter apenas um índice btree em maker_date DESC. O Postgres pode percorrer o índice e filtrar as linhas qualificadas nas tags. Isso funcionará em combinação com colunas booleanas separadas em vez da matriz ou inteiro codificado, porque o Postgres tem estatísticas de colunas mais úteis para colunas separadas.

CREATE INDEX tags_tmp_date ON tags_tmp(maker_date DESC);

E depois:

SELECT *
FROM   tags_tmp_pretty
WHERE  tag_a
   OR  tag_b
ORDER  BY maker_date DESC
LIMIT  10;

Paginação

Você precisa de uma ordem de classificação inequívoca para conjuntos de resultados, para fazer a paginação funcionar. Não me preocupei com esta resposta, já é muito longa. Normalmente, você adicionaria mais colunas a ORDER BY. Como fazer a paginação funcionar de forma eficiente com isso:

• Otimize a consulta com OFFSET em uma tabela grande

Vários problemas com seu caso de teste:

1. idé int8agora. Você declarou como intna sua pergunta original Não é uma grande diferença, mas por que a confusão para começar? É importante para o tamanho da linha e preenchimento de alinhamento. Lembre-se de declarar sua definição de tabela real, exata e completa nas perguntas.

2. A distribuição de dados em seus dados de teste não é realista. Você tem apenas 6 combinações distintas de tags e *todas as linhas têm tag '1'. Presumo que você tenha todas as 63 combinações possíveis em sua mesa ao vivo, com tags distribuídas como você adicionou na pergunta.

3. Sua tabela de teste inclui colunas de tags antigas e novas, o que anula o efeito no tamanho de armazenamento que eu procurava. Agora o tamanho da linha é ainda maior. O tamanho da sua linha é de 124 a 164 bytes, contra apenas 68 bytes no meu teste (incluindo preenchimento e identificador de item). Mais de duas vezes o tamanho faz a diferença.

4. Você escreve tamanho = 4163 MB . Que tamanho?

5. Você tem order by random()para os dados de teste. Sua mesa produtiva é tão aleatória assim? Normalmente, você teria dados aproximadamente classificados por carimbo de data/hora. Qual é a sua situação real ?

6. Para ver qual plano será escolhido, teste EXPLAINapenas para ver o plano de consulta antes de realmente executar a consulta. Economiza muito tempo com mesas grandes. Mas sempre forneça a saída EXPLAIN (ANALYZE, BUFFERS)daqui. Em sua resposta (ao contrário da pergunta), cost=faltam estimativas. Isso torna difícil adivinhar o problema.

Mas nenhum desses problemas pode explicar por que você vê uma varredura sequencial mesmo com enable_seqscan = off; Um teste rápido no meu laptop com Postgres 9.5 funciona . O mesmo deve ser verdade para a página 9.6.

CREATE TABLE tags_tmp(
   id         bigserial PRIMARY KEY, 
   maker_date timestamp NOT NULL,
   tags       int NOT NULL,
   value      text
);

INSERT INTO tags_tmp (tags, maker_date, value)
SELECT EXTRACT('minute' FROM ts)::int    -- int between 1 and 60 (no 61,62,63), pretty good.
     , ts + random() * interval '5 min'  -- some limited randomness
     , 'This is test on ' || EXTRACT('minute' FROM ts)
FROM   generate_series(timestamp '2016-01-01 00:00'
                     , timestamp '2016-01-13 00:00', '10 second') ts;
-- 103681 rows affected, 836 msec execution time.

-- create adapted function f_tags2int
-- create adapted function f_int2tags

CREATE INDEX tags_tmp_1 ON tags_tmp(maker_date DESC) WHERE tags &  1 > 0;
CREATE INDEX tags_tmp_2 ON tags_tmp(maker_date DESC) WHERE tags &  2 > 0;
CREATE INDEX tags_tmp_3 ON tags_tmp(maker_date DESC) WHERE tags &  4 > 0;
CREATE INDEX tags_tmp_4 ON tags_tmp(maker_date DESC) WHERE tags &  8 > 0;
CREATE INDEX tags_tmp_5 ON tags_tmp(maker_date DESC) WHERE tags & 16 > 0;
CREATE INDEX tags_tmp_6 ON tags_tmp(maker_date DESC) WHERE tags & 32 > 0;

SELECT id, tags, maker_date, value
FROM   tags_tmp
WHERE (tags & f_tags2int(array['5']) > 0 OR
       tags & f_tags2int(array['6']) > 0)
ORDER  BY maker_date DESC
LIMIT  5;

QUERY PLAN
Limit  (cost=3811.93..3811.94 rows=5 width=38) (actual time=46.586..46.586 rows=5 loops=1)
  Buffers: shared hit=1132
  ->  Sort  (cost=3811.93..3955.93 rows=57601 width=38) (actual time=46.584..46.585 rows=5 loops=1)
        Sort Key: maker_date DESC
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=1132
        ->  Bitmap Heap Scan on tags_tmp  (cost=607.78..2855.20 rows=57601 width=38) (actual time=13.699..27.674 rows=76032 loops=1)
              Recheck Cond: (((tags & 16) > 0) OR ((tags & 32) > 0))
              Heap Blocks: exact=864
              Buffers: shared hit=1132
              ->  BitmapOr  (cost=607.78..607.78 rows=69121 width=0) (actual time=13.549..13.549 rows=0 loops=1)
                    Buffers: shared hit=268
                    ->  Bitmap Index Scan on tags_tmp_5 cost=0.00..289.49 rows=34560 width=0) (actual time=8.745..8.745 rows=48384 loops=1)
                          Buffers: shared hit=134
                    ->  Bitmap Index Scan on tags_tmp_6 (cost=0.00..289.49 rows=34560 width=0) (actual time=4.800..4.800 rows=48384 loops=1)
                          Buffers: shared hit=134
Planning time: 3.976 ms
Execution time: 46.653 ms

Assim como já demonstrei no SQL Fiddle .

Tem certeza de que criou todos os índices corretamente?