Análise de tabelas

A operação ANALYZE atualiza os metadados estatísticos que o planejador de consulta utiliza para escolher os planos ideais.

Na maioria dos casos, não é preciso executar explicitamente o comando ANALYZE. O Amazon Redshift monitora as alterações no workload e atualiza automaticamente as estatísticas em segundo plano. Além disso, o comando COPY executa uma análise automaticamente quando carrega dados em uma tabela vazia.

Para analisar explicitamente uma tabela ou o banco de dados inteiro, execute o comando ANALYZE.

Análise automática

O Amazon Redshift monitora continuamente seu banco de dados e executa automaticamente as operações de análise em segundo plano. Para minimizar o impacto na performance do sistema, a análise automática é executada durante os períodos em que os workloads são leves.

A análise automática está habilitada por padrão. Para desativar a análise automática, defina o parâmetro auto_analyze como false modificando o grupo de parâmetros de seu cluster.

Para reduzir o tempo de processamento e melhorar a performance geral do sistema, o Amazon Redshift ignora a análise automática de qualquer tabela em que a extensão das modificações seja pequena.

Uma operação de análise ignora tabelas com estatísticas atualizadas. Se você executar ANALYZE como parte de seu fluxo de trabalho de extração, transformação e carregamento (ETL), a análise automática ignorará as tabelas com estatísticas atuais. Da mesma forma, um ANALYZE explícito ignorará as tabelas quando a análise automática atualizar as estatísticas da tabela.

Análise de novos dados da tabela

Por padrão, o comando COPY executa um ANALYZE após carregar dados em uma tabela vazia. Você pode forçar um ANALYZE mesmo se uma tabela estiver vazia definindo STATUPDATE ON. Se você especificar STATUPDATE OFF, nenhum ANALYZE será realizado. Somente o proprietário da tabela ou um superusuário podem executar o comando ANALYZE ou executar o comando COPY com STATUPDATE definida como ON.

O Amazon Redshift também analisa novas tabelas que você cria com os seguintes comandos:

O Amazon Redshift retorna uma mensagem de aviso quando você executa uma consulta em uma nova tabela que não foi analisada depois que seus dados foram carregados inicialmente. Nenhum aviso ocorre quando você consulta uma tabela após uma atualização ou carregamento subsequente. A mesma mensagem de aviso é retornada quando você executa o comando EXPLAIN em uma consulta que se refere a tabelas que não foram analisadas.

Sempre que a adição de dados a uma tabela não vazia alterar significativamente o tamanho da tabela, é possível atualizar estatísticas explicitamente. Isso é feito com a execução de um comando ANALYZE ou usando a opção STATUPDATE ON com o comando COPY. Para visualizar detalhes sobre o número de linhas que foram inseridas ou excluídas desde o último ANALYZE, consulte a tabela de catálogo do sistema PG_STATISTIC_INDICATOR.

Você pode especificar o escopo do comando ANALYZE para um dos seguintes:

O comando ANALYZE obtém um exemplo de linhas da tabela, faz alguns cálculos e salva as estatísticas de coluna resultantes. Por padrão, o Amazon Redshift executa uma passagem de amostra para a coluna DISTKEY e outra passagem de amostra para todas as outras colunas na tabela. Se você quiser gerar estatísticas para um subconjunto de colunas, você pode especificar uma lista de colunas separadas por vírgula. É possível executar ANALYZE com a cláusula PREDICATE COLUMNS para ignorar colunas que não são usadas como predicados.

ANALYZE são operações com uso intenso de recursos, portanto execute as operações somente em tabelas e colunas que realmente exigem atualizações de estatísticas. Você não precisa analisar regularmente todas as colunas em todas as tabelas ou no mesmo agendamento. Se os dados são alterados substancialmente, analise as colunas que são usadas frequentemente para o seguinte:

Para reduzir o tempo de processamento e melhorar a performance geral do sistema, o Amazon Redshift ignora ANALYZE para qualquer tabela que tenha uma baixa porcentagem de linhas alteradas, conforme determinado pelo parâmetro analyze_threshold_percent. Por padrão, o limite de análise é definido como 10 por cento. Você pode alterar o limite de análise para a sessão atual executando um comando SET.

Colunas menos propensas a exigir análises frequentes são aquelas que representam fatos e medidas e quaisquer atributos relacionado que nunca são realmente consultados, tais como grandes colunas VARCHAR. Por exemplo, considere a tabela LISTING no banco de dados 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       

Se essa tabela for carregada todos os dias com um grande número de novos registros, a coluna LISTID, que é usada frequentemente em consultas como uma chave de junção, deve ser analisada regularmente. Se TOTALPRICE e LISTTIME forem restrições frequentemente usadas em consultas, você pode analisar essas colunas e a chave de distribuição todos os dias úteis.

analyze listing(listid, totalprice, listtime);

Suponha que os vendedores e eventos no aplicativo sejam muito mais estáticos e os IDs de data se refiram a um conjunto fixo de dias que abrangem apenas dois ou três anos. Nesse caso, os valores exclusivos dessas colunas não são alterados significativamente. Contudo, o número de instâncias de cada valor exclusivo aumentará continuamente.

Além disso, considere o caso em que as medidas NUMTICKETS e PRICEPERTICKET são consultadas raramente em comparação à coluna TOTALPRICE. Nesse caso, você pode executar o comando ANALYZE em toda a tabela todos os finais de semana para atualizar as estatísticas para as cinco colunas que não são analisadas diariamente:

Colunas de predicados

Como uma alternativa conveniente para a especificação de uma lista de colunas, você poderá escolher analisar apenas as colunas que provavelmente serão usadas como predicados. Quando você executa uma consulta, todas as colunas que são usadas em uma cláusula de junção, condição de filtro ou group by são marcadas como colunas de predicados no catálogo de sistema. Quando você executa ANALYZE com a cláusula PREDICATE COLUMNS, a operação de análise inclui somente as colunas que atendem aos seguintes critérios:

Se nenhuma das colunas de uma tabela está marcada como predicado, ANALYZE inclui todas as colunas, mesmo quando PREDICATE COLUMNS é especificado. Se nenhuma coluna está marcada como coluna de predicado, pode ser que a tabela ainda não tenha sido consultada.

Você pode optar por usar PREDICATE COLUMNS quando o padrão de consulta de seu workload é relativamente estável. Quando o padrão de consulta é variável, com diferentes colunas sendo frequentemente usadas como predicados, o uso de PREDICATE COLUMNS pode temporariamente resultar em estatísticas obsoletas. Estatísticas obsoletas podem ocasionar planos de ambiente de tempo de execução de consulta pouco satisfatórios e tempos de ambiente de tempo de execução longos. Contudo, na próxima vez que você executar ANALYZE usando PREDICATE COLUMNS, as novas colunas de predicado são incluídas.

Para visualizar detalhes de colunas de predicado, use o seguinte SQL para criar uma exibição chamada 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;

Suponha que você execute a consulta a seguir na tabela LISTING. Observe que LISTID, LISTTIME e EVENTID são usados nas cláusulas de junção, filtro e group by.

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;

Quando você consulta a exibição PREDICATE_COLUMNS, conforme exibido no seguinte exemplo, você vê que LISTID, EVENTID e LISTTIME, estão marcadas como colunas 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

Manter as estatísticas atualizadas melhora a performance das consultas, permitindo que o planejador de consultas escolha os planos ideais. O Amazon Redshift atualiza as estatísticas automaticamente em segundo plano, e você pode executar explicitamente o comando ANALYZE. Se você optar por executar explicitamente ANALYZE, faça o seguinte:

Você não precisa executar explicitamente o comando ANALYZE depois de restaurar um snapshot em um cluster provisionado ou namespace sem servidor, nem depois de retomar um cluster provisionado pausado. O Amazon Redshift preserva as informações da tabela do sistema nesses casos, tornando desnecessários os comandos manuais ANALYZE. O Amazon Redshift continuará a executar operações de análise automática conforme necessário.

Uma operação de análise ignora tabelas com estatísticas atualizadas. Se você executar ANALYZE como parte de seu fluxo de trabalho de extração, transformação e carregamento (ETL), a análise automática ignorará as tabelas com estatísticas atuais. Da mesma forma, um ANALYZE explícito ignorará as tabelas quando a análise automática atualizar as estatísticas da tabela.

Histórico do comando ANALYZE

É útil saber quando o último comando ANALYZE foi executado em uma tabela ou banco de dados. Quando um comando ANALYZE é executado, o Amazon Redshift executa várias consultas semelhantes a esta:

padb_fetch_sample: select * from table_name
               

Consultar STL_ANALYZE para visualizar o histórico de operações de análise. Se o Amazon Redshift analisa uma tabela usando análise automática, a coluna is_background é definida como t (true). Caso contrário, ele será definido como f (falso). O exemplo a seguir une a STV_TBL_PERM para mostrar o nome da tabela e os detalhes do ambiente de tempo de execução.

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)

Como alternativa, você pode executar uma consulta mais complexa que retorna todas as instruções executadas em todas as transações concluídas que incluíram um 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
...