Início / dba / Perguntas / 140520
Accepted
Dean MacGregor
Asked: 2016-06-07 06:25:33 +0800 CST

É possível ter duas cópias de uma tabela agrupadas em arranjos diferentes sem problemas?

  • 772

Eu tenho uma tabela com cerca de 100 milhões de linhas. Ele só obtém dados inseridos uma vez por dia, mas precisamos fazer selectmuito. Os selects são geralmente simples, mas às vezes precisam retornar centenas de milhares de linhas.

É único com base em três colunas node_id, pricedate, hourque são integer, timestamp, integer respectivamente. Era lento para a maioria das consultas, mas eu o agrupei em node_id, pricedatee isso corrigiu a lentidão da maioria das consultas. Essas consultas eram do tipo:

select * from mytable where node_id in (1,2,3,4)

Ainda ocasionalmente precisamos fazer consultas como:

select * from mytable where pricedate>='2016-05-01'

Estes ainda são lentos porque são agrupados node_idprimeiro. Já temos um índice pricedate. O problema é que os usuários geralmente precisam de dados suficientes para que o mecanismo de consulta lance o índice e use uma varredura de sequência. Depois de usar uma varredura sequencial, ele se beneficia muito de ter os dados agrupados da maneira como estão sendo consultados. Isso leva ao problema que tenho, onde algumas consultas se beneficiam de um agrupamento e outras consultas do outro:

Seria bom se houvesse uma maneira de ter duas cópias físicas da tabela em que uma cópia é agrupada de uma maneira e a outra é agrupada de outra, mas o acesso do usuário a ela aparece como se houvesse apenas 1 tabela e o mecanismo de banco de dados garantiria que eles está em sincronia. Obviamente, haveria penalidades de gravação ao fazer isso, mas isso é irrelevante para nosso uso.

Algo assim seria possível?

Acho que não existe uma maneira integrada de fazer o que descrevo. Para fazer isso de qualquer maneira, acho que criaria uma tabela chamada mytable_dupcom a mesma restrição de chave exclusiva, mas com o agrupamento alternativo e, em seguida, configuraria gatilhos para inserir nela sempre que o mestre fosse inserido/atualizado/excluído. Isso parece factível, mas a partir daqui, haveria uma maneira razoável de selectpartir da tabela duplicada que seria eficiente?

Estou executando o PostgreSQL 9.4 em casa e 9.5 no Google.

postgresql performance

3 respostas

  1. Best Answer

    Para manter os dados em duas sequências físicas diferentes, é preciso armazenar os dados duas vezes. Isso pode ser obtido definindo um segundo índice de cobertura. Um índice de cobertura contém todas as colunas exigidas por uma consulta. Dessa forma, o otimizador não precisa consultar a tabela base para ler valores adicionais e é improvável que reverta para uma varredura da tabela base para o plano de consulta. O otimizador executa uma varredura somente de índice . Como a escolha do índice é feita pelo otimizador e não pelo programador, nenhum código de aplicativo precisa ser alterado para aproveitar durante as leituras. Nenhum outro objeto é necessário para manter a consistência durante as gravações.

    As colunas usadas na cláusula WHERE serão as colunas iniciais do índice. A sequência das outras colunas não é importante. Como e quando o PostgreSQL suporta a sintaxe INCLUDE, este índice pode ser alterado para usá-lo.

    As desvantagens incluem a) disco extra para armazenar esses dados b) latência adicional durante as gravações para manter o índice c) mais manutenção do sistema necessária para reorganizações e outros, e d) conforme as consultas mudam, o(s) índice(s) de cobertura precisa(m) mudar para corresponder e ) backups e restaurações correspondentemente maiores e mais longos.

    • 5

  2. Você pode criar uma visualização materializada em sua tabela:

    CREATE MATERIALIZED VIEW thecopy AS SELECT * FROM mytable;

    Em seguida, adicione um índice exclusivo que corresponda ao seu PK mytable(você não pode adicionar um PK 'real' lá, pois não é uma tabela 'real'):

    CREATE UNIQUE INDEX ON thecopy (node_id, pricedate, hour);

    Então sua cópia está lá. Se você deseja agrupá-lo, precisa de um índice para ele:

    CREATE INDEX ON thecopy (pricedate);
CLUSTER thecopy USING thecopy_pricedate_idx;
-- You can also do 
-- ALTER MATERIALIZED VIEW thecopy CLUSTER ON thecopy_pricedate_idx;
-- https://www.postgresql.org/docs/current/static/sql-altermaterializedview.html

    Então, sempre que precisar (basicamente após o término da carga diária de dados), faça um

    REFRESH MATERIALIZED VIEW [CONCURRENTLY] thecopy;

    E então altere o segundo tipo de consultas para ir para o MV em vez da tabela.

    • 3

  3. Portanto, supondo que você queira simplificar para os usuários da cláusula select e não saber como eles a estão executando ...

    Que tal usar uma função?

    Uma alternativa:

    Passe toda a cláusula select como parâmetro(s) para uma função,

    analisá-lo para a coluna da cláusula where

    e depois direcioná-lo para a tabela ou visualização materializada conforme sugerido por @dezso?

    • -1

relate perguntas