Analisi automatica Analisi dei dati di nuove tabelle Cronologia del comando ANALYZE

Analisi delle tabelle

L'operazione ANALYZE aggiorna i metadati statistici utilizzati dal pianificatore di query per scegliere i piani ottimali.

Nella maggior parte dei casi, non è necessario eseguire esplicitamente il comando ANALYZE. Amazon Redshift monitora le modifiche al carico di lavoro e aggiorna automaticamente le statistiche in background. Inoltre, il comando COPY esegue automaticamente un'analisi quando carica i dati in una tabella vuota.

Per analizzare in modo esplicito una tabella o l'intero database, esegui il comando ANALYZE.

Analisi automatica

Amazon Redshift monitora ininterrottamente il database ed esegue automaticamente le operazioni di analisi in background. Per ridurre l'impatto sulle prestazioni del sistema, l'analisi automatica viene eseguita quando i carichi di lavoro sono più leggeri.

L'analisi automatica è abilitata per impostazione predefinita. Per disattivare l'analisi automatica, imposta il parametro auto_analyze su false modificando il gruppo di parametri del cluster.

Per ridurre il tempo di elaborazione e migliorare le prestazioni generali del sistema, Amazon Redshift ignora l'analisi automatica per qualsiasi tabella che abbia una percentuale di modifiche limitata.

Un'operazione di analisi ignora le tabelle che contengono up-to-date statistiche. Se esegui ANALYZE nel flusso di lavoro ETL (Extract, Transform and Load, estrazione, trasformazione e caricamento), l'analisi automatica ignora le tabelle con statistiche aggiornate. Analogamente, un comando ANALYZE esplicito ignora le tabelle se l'analisi automatica ha aggiornato le statistiche della tabella.

Analisi dei dati di nuove tabelle

Per impostazione predefinita, il comando COPY esegue un comando ANALYZE dopo aver caricato i dati in una tabella vuota. Puoi forzare un comando ANALYZE indipendentemente dal fatto che una tabella sia vuota impostando STATUPDATE ON. Se specifichi STATUPDATE OFF, ANALYZE non viene eseguito. Solo il proprietario della tabella o un utente con privilegi avanzati può eseguire il comando ANALYZE o eseguire il comando COPY con STATUPDATE impostata su ON.

Amazon Redshift analizza anche le nuove tabelle create con i seguenti comandi:

CREATE TABLE AS (CTAS)
CREATE TEMP TABLE AS
SELECT INTO

Auando si esegue una query su una nuova tabella che non è stata analizzata dopo il caricamento iniziale dei dati, Amazon Redshift restituisce un messaggio di avviso. Non vengono visualizzati avvisi quando esegui una query su una tabella dopo un successivo aggiornamento o caricamento. Lo stesso messaggio di avviso viene restituito quando esegui il comando EXPLAIN su una query che fa riferimento a tabelle non analizzate.

Se l'aggiunta di dati a una tabella non vuota modifica significativamente le dimensioni della tabella, puoi aggiornare le statistiche in modo esplicito. Per farlo, esegui il comando ANALYZE o l'opzione STATUPDATE ON con il comando COPY. Per visualizzare i dettagli sul numero di righe che sono state inserite o eliminate dall'ultimo ANALYZE, esegui una query sulla tabella del catalogo di sistema PG_STATISTIC_INDICATOR.

Puoi specificare l'ambito del comando ANALYZE su una delle opzioni seguenti:

L'intero database attuale
Una tabella singola
Una o più colonne specifiche in una singola tabella
Colonne che possono essere utilizzate come predicati nelle query

Il comando ANALYZE ottiene un campione di righe dalla tabella, esegue alcuni calcoli e salva le statistiche delle colonne risultanti. Per impostazione predefinita, Amazon Redshift esegue un passaggio di esempio per la colonna DISTKEY e un altro passaggio di esempio per tutte le altre colonne nella tabella. Se desideri generare statistiche per un sottoinsieme di colonne, è possibile specificare un elenco di colonne separate da virgola. Puoi eseguire ANALYZE con la clausola PREDICATE COLUMNS per ignorare le colonne non utilizzate come predicati.

Le operazioni di ANALYZE richiedono molte risorse, quindi eseguile solo su tabelle e colonne che richiedono effettivamente aggiornamenti delle statistiche. Non è necessario analizzare tutte le colonne in tutte le tabelle regolarmente o nella stessa pianificazione. Se i dati cambiano in modo sostanziale, analizza le colonne utilizzate frequentemente nel modo seguente:

Operazioni di raggruppamento e ordinamento
Join
Predicati di query

Per ridurre i tempi di elaborazione e migliorare le prestazioni generali del sistema, Amazon Redshift ignora ANALYZE per qualsiasi tabella che abbia una bassa percentuale di righe modificate, come determinato dal parametro analyze_threshold_percent. Per impostazione predefinita, la soglia di analisi è impostata su 10 percento. È possibile modificare la soglia di analisi per la sessione attuale eseguendo un comando SET.

Le colonne che hanno meno probabilità di richiedere analisi frequenti sono quelle che rappresentano fatti e misure e tutti gli attributi correlati su cui non viene mai effettivamente eseguita una query, come le colonne VARCHAR di grandi dimensioni. Ad esempio, prendi in considerazione la tabella LISTING nel database 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 questa tabella viene caricata ogni giorno con un numero elevato di nuovi record, la colonna LISTID, che viene spesso utilizzata nelle query come chiave di join, deve essere analizzata regolarmente. Se TOTALPRICE e LISTTIME sono i vincoli utilizzati frequentemente nelle query, è possibile analizzare tali colonne e la chiave di distribuzione in ogni giorno della settimana.


analyze listing(listid, totalprice, listtime);

Se i venditori e gli eventi nell'applicazione sono molto più statici e gli ID di data si riferiscono a un set fisso di giorni che copre solo due o tre anni, i valori univoci per queste colonne non cambiano significativamente. Tuttavia, il numero di istanze di ogni valore univoco aumenterà costantemente.

Inoltre, prendi in considerazione il caso in cui venga eseguita raramente una query sulle misure NUMTICKETS e PRICEPERTICKET rispetto alla colonna TOTALPRICE. In questo caso, puoi eseguire il comando ANALYZE sull'intera tabella una volta ogni fine settimana per aggiornare le statistiche relative alle cinque colonne non analizzate giornalmente:

Colonne di predicato

Come valida alternativa alla specifica di un elenco di colonne, è possibile scegliere di analizzare solo le colonne che potrebbero essere utilizzate come predicati. Quando si esegue una query, tutte le colonne utilizzate in un join, una condizione del filtro o una clausola di raggruppamento vengono contrassegnate come colonne di predicato nel catalogo di sistema. Quando esegui ANALYZE con la clausola PREDICATE COLUMNS, l'operazione di analisi include solo le colonne che soddisfano i seguenti criteri:

La colonna è contrassegnata come una colonna di predicato.
La colonna è una chiave di distribuzione.
La colonna fa parte di una chiave di ordinamento.

Se nessuna delle colonne di una tabella è contrassegnata come predicato, ANALYZE include tutte le colonne, anche quando viene specificato PREDICATE COLUMNS. Se nessuna colonna è contrassegnata come colonne di predicato, potrebbe essere perché non è stata ancora eseguita una query sulla tabella.

È possibile scegliere di utilizzare PREDICATE COLUMNS quando il modello di query del carico di lavoro è relativamente stabile. Quando il modello di query è variabile, con colonne diverse utilizzate frequentemente come predicati, l'uso di PREDICATE COLUMNS potrebbe temporaneamente portare a statistiche obsolete. Le statistiche obsolete possono portare a piani di runtime delle query non ottimali e tempi di runtime lunghi. Tuttavia, la volta successiva che esegui ANALYZE utilizzando PREDICATE COLUMNS, vengono incluse le nuove colonne del predicato.

Per visualizzare i dettagli delle colonne dei predicati, utilizza il seguente codice SQL per creare una vista denominata 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;

Presumi di eseguire la seguente query sulla tabella LISTING. Tieni presente che LISTID, LISTTIME ed EVENTID si utilizzano in un join, una condizione del filtro e una clausola di raggruppamento.


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 esegui una query sulla vista PREDICATE_COLUMNS, come illustrato nell'esempio seguente, puoi notare che LISTID, EVENTID e LISTTIME sono contrassegnati come colonne del predicato.


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

Mantenere aggiornate le statistiche migliora le prestazioni delle query perché consente al pianificatore di query di scegliere i piani ottimali. Amazon Redshift aggiorna le statistiche automaticamente in background e consente di eseguire esplicitamente il comando ANALYZE. Se scegli di eseguire esplicitamente ANALYZE, procedi come descritto di seguito:

Esegui il comando ANALYZE prima di eseguire le query.
Esegui il comando ANALYZE sul database regolarmente alla fine di ogni normale ciclo di caricamento o aggiornamento.
Esegui il comando ANALYZE su qualsiasi nuova tabella che crei e su qualsiasi tabella o colonna esistente soggetta a modifiche significative.
Prendi in considerazione l'esecuzione di operazioni di ANALYZE su pianificazioni diverse per diversi tipi di tabelle e colonne, a seconda del loro utilizzo nelle query e della loro propensione al cambiamento.
Per risparmiare tempo e risorse cluster, utilizza la clausola PREDICATE COLUMNS quando esegui ANALYZE.

Non è necessario eseguire esplicitamente il comando ANALYZE dopo aver ripristinato un'istantanea in un cluster fornito o in uno spazio dei nomi serverless, né dopo aver ripreso un cluster con provisioning sospeso. Amazon Redshift conserva le informazioni della tabella di sistema in questi casi, rendendo superflui i comandi ANALYZE manuali. Amazon Redshift continuerà a eseguire operazioni di analisi automatiche in base alle esigenze.

Cronologia del comando ANALYZE

È utile sapere quando è stato eseguito l'ultima volta il comando ANALYZE su una tabella o un database. Quando viene eseguito un comando ANALYZE, Amazon Redshift esegue più query simili alla seguente:


padb_fetch_sample: select * from table_name

Esegui una query su STL_ANALYZE per visualizzare la cronologia delle operazioni di analisi. Se Amazon Redshift analizza una tabella con l'analisi automatica, la colonna is_background sarà impostata su t (true). Altrimenti, è impostata su f (false). L'esempio seguente collega STV_TBL_PERM per mostrare il nome della tabella e i dettagli di runtime.


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)

In alternativa, è possibile eseguire una query più complessa che restituisce tutte le istruzioni eseguite in ogni transazione completata che includeva 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
...

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Esecuzione di una copia completa

Vacuum delle tabelle