Análisis de tablas

La operación ANALYZE actualiza los metadatos estadísticos que el planificador de consultas usa para seleccionar planes óptimos.

En la mayoría de los casos, no necesita ejecutar de forma explícita el comando ANALYZE. Amazon Redshift monitorea los cambios realizados en la carga de trabajo y, de forma automática, actualiza las estadísticas en segundo plano. Además, el comando COPY realiza un análisis automáticamente cuando carga datos en una tabla vacía.

Para analizar una tabla o toda la base de datos de manera explícita, ejecute el comando ANALYZE.

Análisis automático

Amazon Redshift monitorea constantemente la base de datos y, de forma automática, realiza operaciones de análisis en segundo plano. Para minimizar el impacto en el rendimiento del sistema, el análisis automático se ejecuta durante periodos en los que hay poca carga de trabajo.

El análisis automático está habilitado de forma predeterminada. Para desactivar el análisis automático, establezca el parámetro auto_analyze en false modificando el grupo de parámetros del clúster.

Para reducir el tiempo de procesamiento y mejorar el rendimiento general del sistema, Amazon Redshift omite el análisis automático de cualquier tabla con pocas modificaciones.

Una operación de análisis omite las tablas que tienen estadísticas actualizadas. Si ejecuta ANALYZE como parte del flujo de trabajo de extracción, transformación y carga (ETL), el análisis automático omite las tablas con estadísticas actuales. De forma similar, un comando ANALYZE explícito omite tablas cuando el análisis automático ha actualizado las estadísticas de la tabla.

Análisis de los datos nuevos de las tablas

De manera predeterminada, el comando COPY realiza una operación ANALYZE una vez que carga datos en una tabla vacía. Puede forzar una operación ANALYZE sin importar si una tabla se encuentra vacía estableciendo STATUPDATE en ON. Si especifica STATUPDATE en OFF, no se realiza la operación ANALYZE. Solo el propietario de la tabla o un super usuario pueden ejecutar el comando ANALYZE o el comando COPY con STATUPDATE establecido en ON.

Amazon Redshift también analiza las tablas nuevas que se crean con los siguientes comandos:

Amazon Redshift devuelve un mensaje de advertencia si ejecuta una consulta en una tabla nueva que no se analizó después de la carga de datos inicial. No se producen avisos al ejecutar una consulta en una tabla tras una carga o actualización posterior. El mismo mensaje de aviso se devuelve al ejecutar el comando EXPLAIN en una consulta que hace referencia a tablas que no se han analizado.

Si al agregar datos a una tabla que no está vacía cambia significativamente el tamaño de la tabla, puede actualizar de forma explícita las estadísticas. Para ello, debe ejecutar un comando ANALYZE o usar la opción STATUPDATE ON con el comando COPY. Para ver los detalles de la cantidad de filas que se han insertado o eliminado desde la última vez que se ejecutó ANALYZE, ejecute una consulta en la tabla de catálogo del sistema PG_STATISTIC_INDICATOR.

Puede especificar el ámbito del comando ANALYZE en uno de los siguientes lugares:

El comando ANALYZE obtiene una muestra de filas desde la tabla, realiza cálculos y guarda las estadísticas de columna resultantes. De manera predeterminada, Amazon Redshift ejecuta un pase de muestra en la columna DISTKEY y otro pase de muestra en todas las demás columnas de la tabla. Si desea generar estadísticas para un subconjunto de columnas, puede especificar una lista de nombres de columnas separada por comas. Puede ejecutar ANALYZE con la cláusula PREDICATE COLUMNS para omitir las columnas que no se usan como predicados.

Las operaciones ANALYZE consumen muchos recursos, por lo que debe ejecutarlas solamente en tablas y columnas que realmente requieran actualizaciones de las estadísticas. No es necesario que analice todas las columnas de las tablas de manera regular ni en el mismo programa. Si los datos cambian considerablemente, analice las columnas que se usan con frecuencia en los siguientes:

Para reducir el tiempo de procesamiento y mejorar el rendimiento general del sistema, Amazon Redshift omite ANALYZE en cualquier tabla que tenga un porcentaje bajo de filas modificadas, en función de lo que determine el parámetro analyze_threshold_percent. De manera predeterminada, el umbral de análisis está establecido en 10 por ciento. Puede cambiar el umbral de análisis para la sesión actual al ejecutar un comando SET.

Es menos probable que las columnas que representan hechos, medidas y cualquier atributo relacionado que en realidad nunca se consulta, como las columnas VARCHAR grandes, requieran análisis frecuentes. Por ejemplo, veamos el caso de la tabla LISTING en la base de datos TICKIT.

select "column", type, encoding, distkey, sortkey
from pg_table_def where tablename = 'listing';


column         |        type        | encoding | distkey | sortkey 
---------------+--------------------+----------+---------+---------
listid         | integer            | none     | t       | 1       
sellerid       | integer            | none     | f       | 0       
eventid        | integer            | mostly16 | f       | 0       
dateid         | smallint           | none     | f       | 0       
numtickets     | smallint           | mostly8  | f       | 0       
priceperticket | numeric(8,2)       | bytedict | f       | 0       
totalprice     | numeric(8,2)       | mostly32 | f       | 0       
listtime       | timestamp with...  | none     | f       | 0       

Si esta tabla se carga diariamente con una gran cantidad de registros nuevos, la columna LISTID, que frecuentemente se usa en consultas como una clave de combinación, se debe analizar con regularidad. Si TOTALPRICE y LISTTIME se usan con frecuencia como restricciones en consultas, puede analizar esas columnas y la clave de distribución todos los días de la semana.

analyze listing(listid, totalprice, listtime);

Supongamos que los vendedores y los eventos en la aplicación son mucho más estáticos y los ID de fecha se refieren a un conjunto de días fijo que cubre solo dos o tres años. En este caso, los valores únicos de estas columnas no cambian de manera significativa. No obstante, la cantidad de instancias de cada valor único aumentará de manera constante.

Además, veamos el caso en que se ejecutan consultas sobre las medidas NUMTICKETS y PRICEPERTICKET de manera poco frecuente, en comparación con la columna TOTALPRICE. En este caso, puede ejecutar el comando ANALYZE sobre la tabla completa una vez por fin de semana para actualizar las estadísticas correspondientes a las cinco columnas que no se analizan a diario:

Columnas de predicados

Como alternativa conveniente a especificar una lista de columnas, puede optar por analizar solo las columnas que probablemente utilice como predicados. Al ejecutar una consulta, cualquier columna usada en una combinación, condición de filtro o cláusula de agrupación se marca como columna de predicado en el catálogo de sistema. Al ejecutar ANALYZE con la cláusula PREDICATE COLUMNS, la operación de análisis incluye solo las columnas que cumplen con los siguientes criterios:

Si ninguna de las columnas de una tabla se marca como predicado, ANALYZE incluye todas las columnas, incluso si se especifica PREDICATE COLUMNS. Si no se marca ninguna columna como columna de predicado, podría ser porque la tabla aún no se ha consultado.

Podría elegir usar PREDICATE COLUMNS una vez que el patrón de consultas de su carga de trabajo esté relativamente estable. Cuando el patrón de consultas es variable, con el uso frecuente de columnas diferentes como predicados, el uso de PREDICATE COLUMNS podría dar como resultado temporario estadísticas obsoletas. Las estadísticas obsoletas pueden llevar a planes de tiempo de ejecución de consultas poco óptimos y tiempos de ejecución prolongados. No obstante, la próxima vez que ejecute ANALYZE y use PREDICATE COLUMNS, se incluirán las columnas de predicado nuevas.

Para ver detalles de las columnas de predicado, use el siguiente SQL para crear una vista denominada PREDICATE_COLUMNS.

CREATE VIEW predicate_columns AS
WITH predicate_column_info as (
SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num,  a.attname as col_name,
        CASE
            WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') 
            WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||')
            WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||')
            WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||')
            ELSE NULL::varchar
        END AS pred_ts
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_namespace ns ON c.relnamespace = ns.oid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum)
SELECT schema_name, table_name, col_num, col_name,
       pred_ts NOT LIKE '2000-01-01%' AS is_predicate,
       CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use,
       CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze
FROM predicate_column_info;

Supongamos que ejecuta la siguiente consulta en la tabla LISTING. Observe que LISTID, LISTTIME y EVENTID se usan en la combinación, el filtro y las cláusulas de agrupamiento.

select s.buyerid,l.eventid, sum(l.totalprice)
from listing l
join sales s on l.listid = s.listid
where l.listtime > '2008-12-01'
group by l.eventid, s.buyerid;

Al ejecutar consultas en la vista PREDICATE_COLUMNS, como se observa en el siguiente ejemplo, se ve que LISTID, EVENTID y LISTTIME se marcan como columnas de predicado.

select * from predicate_columns 
where table_name = 'listing';

schema_name | table_name | col_num | col_name       | is_predicate | first_predicate_use | last_analyze       
------------+------------+---------+----------------+--------------+---------------------+--------------------
public      | listing    |       1 | listid         | true         | 2017-05-05 19:27:59 | 2017-05-03 18:27:41
public      | listing    |       2 | sellerid       | false        |                     | 2017-05-03 18:27:41
public      | listing    |       3 | eventid        | true         | 2017-05-16 20:54:32 | 2017-05-03 18:27:41
public      | listing    |       4 | dateid         | false        |                     | 2017-05-03 18:27:41
public      | listing    |       5 | numtickets     | false        |                     | 2017-05-03 18:27:41
public      | listing    |       6 | priceperticket | false        |                     | 2017-05-03 18:27:41
public      | listing    |       7 | totalprice     | false        |                     | 2017-05-03 18:27:41
public      | listing    |       8 | listtime       | true         | 2017-05-16 20:54:32 | 2017-05-03 18:27:41

Mantener las estadísticas actualizadas mejora el rendimiento de las consultas, ya que permite que el planificador de consultas elija los planes óptimos. Amazon Redshift actualiza las estadísticas de forma automática en segundo plano, y usted también puede ejecutar de forma explícita el comando ANALYZE. Si decide ejecutar explícitamente el comando ANALYZE, haga lo siguiente:

No tiene que ejecutar explícitamente el comando ANALYZE después de restaurar una instantánea en un clúster aprovisionado o en un espacio de nombres sin servidor, ni después de reanudar un clúster aprovisionado en pausa. Amazon Redshift conserva la información de la tabla del sistema en estos casos, lo que hace innecesarios los comandos ANALYZE manuales. Amazon Redshift seguirá ejecutando operaciones de análisis automáticas según sea necesario.

Una operación de análisis omite las tablas que tienen estadísticas actualizadas. Si ejecuta ANALYZE como parte del flujo de trabajo de extracción, transformación y carga (ETL), el análisis automático omite las tablas con estadísticas actuales. De forma similar, un comando ANALYZE explícito omite tablas cuando el análisis automático ha actualizado las estadísticas de la tabla.

Historial del comando ANALYZE

Saber cuándo fue la última vez que el comando ANALYZE se ejecutó en una tabla o base de datos es útil. Cuando se ejecuta el comando ANALYZE, Amazon Redshift ejecuta distintas consultas que se ven de la siguiente manera:

padb_fetch_sample: select * from table_name
               

Ejecute la consulta STL_ANALYZE para ver el historial de las operaciones ANALYZE. Si Amazon Redshift analiza una tabla mediante el análisis automático, la columna is_background se establece en t (true). De lo contrario, se establece en f (falso). En el siguiente ejemplo, se combina STV_TBL_PERM para mostrar el nombre de la tabla y los detalles de tiempo de ejecución.

select distinct a.xid, trim(t.name) as name, a.status, a.rows, a.modified_rows, a.starttime, a.endtime
from stl_analyze a 
join stv_tbl_perm t  on t.id=a.table_id
where name = 'users'
order by starttime;


xid    | name  | status          | rows  | modified_rows | starttime           | endtime            
-------+-------+-----------------+-------+---------------+---------------------+--------------------
  1582 | users | Full            | 49990 |         49990 | 2016-09-22 22:02:23 | 2016-09-22 22:02:28
244287 | users | Full            | 24992 |         74988 | 2016-10-04 22:50:58 | 2016-10-04 22:51:01
244712 | users | Full            | 49984 |         24992 | 2016-10-04 22:56:07 | 2016-10-04 22:56:07
245071 | users | Skipped         | 49984 |             0 | 2016-10-04 22:58:17 | 2016-10-04 22:58:17
245439 | users | Skipped         | 49984 |          1982 | 2016-10-04 23:00:13 | 2016-10-04 23:00:13
(5 rows)

De manera alternativa, puede ejecutar una consulta más compleja que devuelva todas las instrucciones que se ejecutaron en cada transacción completada que incluyó un comando ANALYZE:

select xid, to_char(starttime, 'HH24:MM:SS.MS') as starttime,
datediff(sec,starttime,endtime ) as secs, substring(text, 1, 40)
from svl_statementtext
where sequence = 0
and xid in (select xid from svl_statementtext s where s.text like 'padb_fetch_sample%' )
order by xid desc, starttime;

xid  |  starttime   | secs |                  substring
-----+--------------+------+------------------------------------------
1338 | 12:04:28.511 |    4 | Analyze date
1338 | 12:04:28.511 |    1 | padb_fetch_sample: select count(*) from
1338 | 12:04:29.443 |    2 | padb_fetch_sample: select * from date
1338 | 12:04:31.456 |    1 | padb_fetch_sample: select * from date
1337 | 12:04:24.388 |    1 | padb_fetch_sample: select count(*) from
1337 | 12:04:24.388 |    4 | Analyze sales
1337 | 12:04:25.322 |    2 | padb_fetch_sample: select * from sales
1337 | 12:04:27.363 |    1 | padb_fetch_sample: select * from sales
...