Teste de codificações de compactação

Se você decidir especificar codificações de coluna manualmente, talvez queira testar diferentes codificações com seus dados.

nota

Recomendamos que você use o comando COPY para carregar dados sempre que possível, permitindo que o comando COPY escolha as codificações ideais com base em seus dados. Como alternativa, você pode usar o comando ANALYZE COMPRESSION para visualizar as codificações sugeridas para os dados existentes. Para obter detalhes sobre a aplicação de compactação automática, consulte Carregamento de tabelas com compactação automática.

Para executar um teste significativo de compactação de dados, é necessário ter um grande número de linhas. Para este exemplo, criamos uma tabela e inserimos linhas usando uma instrução que seleciona a partir de duas tabelas; VENUE e LISTING. Deixamos de fora a cláusula WHERE que normalmente uniria as duas tabelas. O resultado é que cada linha da tabela VENUE é unida a todas as linhas da tabela LISTING, para um total de mais de 32 milhões de linhas. Isso é conhecido como uma junção cartesiana e não é normalmente recomendado. No entanto, para esta finalidade, é um método conveniente para criar muitas linhas. Se você tiver uma tabela existente com dados que deseja testar, poderá ignorar esta etapa.

Depois de termos uma tabela com dados de amostra, criamos uma tabela com sete colunas. Cada um tem uma codificação de compactação diferente: raw, bytedict, lzo, run length, text255, text32k e zstd. Preenchemos cada coluna com exatamente os mesmos dados, executando um comando INSERT que seleciona os dados da primeira tabela.

Para testar codificações de compactação, faça o seguinte:

1. (Opcional) Primeiro, use uma junção cartesiana para criar uma tabela com um grande número de linhas. Ignore esta etapa se você deseja testar uma tabela existente.

   create table cartesian_venue(
   venueid smallint not null distkey sortkey,
   venuename varchar(100),
   venuecity varchar(30),
   venuestate char(2),
   venueseats integer);
   
   insert into cartesian_venue
   select venueid, venuename, venuecity, venuestate, venueseats
   from venue, listing;

2. Em seguida, crie uma tabela com as codificações que deseja comparar.

   create table encodingvenue (
   venueraw varchar(100) encode raw,
   venuebytedict varchar(100) encode bytedict,
   venuelzo varchar(100) encode lzo,
   venuerunlength varchar(100) encode runlength,
   venuetext255 varchar(100) encode text255,
   venuetext32k varchar(100) encode text32k,
   venuezstd varchar(100) encode zstd);

3. Insira os mesmos dados em todas as colunas usando uma instrução INSERT com uma cláusula SELECT.

   insert into encodingvenue
   select venuename as venueraw, venuename as venuebytedict, venuename as venuelzo, venuename as venuerunlength, venuename as  venuetext32k, venuename as  venuetext255, venuename as venuezstd
   from cartesian_venue;

4. Verifique o número de linhas na nova tabela.

   select count(*) from encodingvenue
   
     count
   ----------
    38884394
   (1 row)

5. Consulte a tabela de sistema STV_BLOCKLIST para comparar o número de blocos de disco de 1 MB usados por cada coluna.

   A função de agregação MAX retorna o número de bloco mais alto para cada coluna. A tabela STV_BLOCKLIST inclui detalhes para as três colunas geradas pelo sistema. Este exemplo usa col < 6 na cláusula WHERE para excluir as colunas geradas pelo sistema.

   select col, max(blocknum)
   from stv_blocklist b, stv_tbl_perm p
   where (b.tbl=p.id) and name ='encodingvenue'
   and col < 7
   group by name, col
   order by col;

   A consulta retorna os seguintes resultados. As colunas são numeradas a partir de zero. Dependendo de como seu cluster está configurado, o resultado pode ter números diferentes, mas os tamanhos relativos devem ser similares. Você pode ver que a codificação BYTEDICT na segunda coluna produziu os melhores resultados para este conjunto de dados. Essa abordagem tem uma taxa de compactação de melhor que 20:1. As codificações LZO e ZSTD também produziram excelentes resultados. É evidente que diferentes conjuntos de dados produzem resultados diferentes. Quando uma coluna contém strings de texto mais longas, a codificação LZO frequentemente produz os melhores resultados de compactação.

    col | max
   -----+-----
      0 | 203
      1 |  10
      2 |  22
      3 | 204
      4 |  56
      5 |  72
      6 |  20
   (7 rows)

Se você tiver dados em uma tabela existente, será possível usar o comando ANALYZE COMPRESSION para visualizar as codificações sugeridas para a tabela. Por exemplo, o seguinte exemplo mostra a codificação recomendada para uma cópia da tabela VENUE, CARTESIAN_VENUE, que contém 38 milhões de linhas. Observe que ANALYZE COMPRESSION recomenda a codificação LZO para a coluna VENUENAME. ANALYZE COMPRESSION escolhe a compactação ideal com base em diversos fatores, incluindo a porcentagem de redução. Neste caso específico, BYTEDICT fornece a melhor compactação, mas LZO também produz uma compactação maior que 90 por cento.

analyze compression cartesian_venue;

Table          | Column     | Encoding | Est_reduction_pct
---------------+------------+----------+------------------
reallybigvenue | venueid    | lzo      | 97.54            
reallybigvenue | venuename  | lzo      | 91.71            
reallybigvenue | venuecity  | lzo      | 96.01            
reallybigvenue | venuestate | lzo      | 97.68            
reallybigvenue | venueseats | lzo      | 98.21