Prueba de las codificaciones de compresión

Si decide especificar de forma manual las codificaciones de las columnas, posiblemente quiera probar los diferentes tipos de compresión con sus datos.

nota

Le recomendamos utilizar el comando COPY para cargar los datos siempre que sea posible y permitir que el comando COPY seleccione las codificaciones óptimas según sus datos. También puede utilizar el comando ANALYZE COMPRESSION para ver las codificaciones sugeridas para los datos existentes. Para obtener información acerca de la aplicación de la compresión automática, consulte Carga de tablas con compresión automática.

Para que la prueba de compresión de datos sea significativa, debe tener una gran cantidad de filas. Para este ejemplo, se crea una tabla y se insertan filas mediante el uso de una instrucción que selecciona datos de dos tablas, VENUE y LISTING. Se omite la cláusula WHERE que, por lo general, uniría las dos tablas. Como resultado de esto, cada fila de la tabla VENUE se une a todas las filas de la tabla LISTING, lo que supone un total de más de 32 millones de filas. Esta operación se la conoce como unión cartesiana y, por lo general, no se la recomienda. No obstante, a estos efectos, se trata de un método cómodo para crear muchas filas. Puede omitir este paso si ya tiene una tabla existente con datos que desea probar.

Después de tener una tabla con datos de muestra, se crea una tabla con siete columnas. Cada una de ellas cuenta con una codificación de compresión diferente: raw, bytedict, lzo, run length, text255, text32k y zstd. Se rellena cada columna con los mismos datos por medio de la ejecución de un comando INSERT que selecciona los datos de la primera tabla.

Si desea probar las codificaciones de compresión, haga lo siguiente:

1. (Opcional) Primero, utilice una unión cartesiana para crear una tabla con una gran cantidad de filas. Omita este paso si desea realizar la prueba con una tabla 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. Luego, cree una tabla con las codificaciones que desea 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. Inserte los mismos datos en todas las columnas utilizando una instrucción INSERT con una 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. Compruebe la cantidad de filas en la tabla nueva.

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

5. Consulte la tabla de sistema STV_BLOCKLIST para comparar la cantidad de bloques de 1 MB del disco que usa cada columna.

   La función de agregación MAX devuelve la cantidad máxima de bloques para cada columna. La tabla STV_BLOCKLIST incluye detalles de las tres columnas generadas por el sistema. En este ejemplo se utiliza col < 6 en la cláusula WHERE para excluir las columnas generadas por el 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;

   La consulta devuelve los siguientes resultados. Las columnas están numeradas a partir del cero. Los resultados pueden tener números diferentes, en función de cómo esté configurado su clúster, pero los tamaños relativos deben ser similares. Puede observar que la codificación BYTEDICT en la segunda columna produjo los mejores resultados para este conjunto de datos. Este enfoque tiene una relación de compresión de más de 20:1. La codificación LZO y ZSTD también produjeron resultados excelentes. Desde luego, los diferentes conjuntos de datos producen resultados distintos. Cuando una columna tiene cadenas de texto más largas, LZO suele producir los mejores resultados de compresión.

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

Si tiene datos en una tabla existente, puede utilizar el comando ANALYZE COMPRESSION para ver las codificaciones sugeridas para la tabla. Por ejemplo, en el siguiente ejemplo, se muestra la codificación recomendada para una copia de la tabla VENUE, CARTESIAN_VENUE, que tiene 38 millones de filas. Observe que ANALYZE COMPRESSION recomienda utilizar la codificación LZO para la columna VENUENAME. ANALYZE COMPRESSION selecciona la compresión óptima en función de varios factores, incluido el porcentaje de reducción. En este caso en particular, BYTEDICT proporciona una mejor compresión, pero LZO también produce una compresión mayor que el 90 por ciento.

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