テーブルを分析する

ANALYZE オペレーションは、クエリプランナーで最適な計画の選択に使用される統計メタデータを更新します。

多くの場合、ANALYZE コマンドを明示的に実行する必要はありません。Amazon Redshift は、ワークロードの変更をモニタリングし、統計をバックグラウンドで自動的に更新します。さらに、COPY コマンドは空のテーブルにデータをロードした際に分析を自動で実行します。

テーブルまたはデータベース全体を明示的に分析するには、ANALYZE コマンドを実行します。

自動分析

Amazon Redshift はデータベースを継続的にモニタリングし、バックグラウンドで自動的に分析オペレーションを実行します。システムパフォーマンスへの影響を最小限にするために、自動分析はワークロードが軽い期間に実行されます。

自動分析はデフォルトで有効になっています。自動分析を無効にするには、クラスターのパラメータグループを変更して auto_analyze パラメータを false に設定します。

処理時間を短縮し、システムの全体的なパフォーマンスを向上させるために、Amazon Redshift は、変更の程度が低いテーブルの自動分析を省略します。

分析オペレーションは、最新の統計を含むテーブルを省略します。抽出およびロード (ETL) ワークフローの一環として ANALYZE を実行する場合、自動分析は最新の統計を含むテーブルを省略します。同様に、自動分析によりテーブルの統計が更新された場合、明示的な ANALYZE はテーブルを省略します。

新しいテーブルデータの分析

デフォルトでは、COPY コマンドは空のテーブルにデータをロードした後に ANALYZE を実行します。STATUPDATE を ON に設定すれば、テーブルが空であるかどうかに関係なく、ANALYZE を強制できます。STATUPDATE に OFF を指定した場合、ANALYZE は実行されません。テーブルの所有者とスーパーユーザーのみが ANALYZE コマンドまたは STATUPDATE を ON に設定した COPY コマンドを実行できます。

Amazon Redshift では、以下のコマンドを使用して作成したテーブルが分析されます。

データの初回ロード後に分析されなかった新しいテーブルにクエリを実行する場合、Amazon Redshift は警告メッセージを返します。その後の更新またはロードの後にテーブルにクエリを実行しても警告は発生しません。分析されていないテーブルを参照するクエリに EXPLAIN コマンドを実行すると、同じ警告メッセージが返されます。

データを空ではないテーブルに追加するとテーブルのサイズが大きく変化する場合はいつでも、統計を明示的に更新することができます。これは、ANALYZE コマンドを実行するか、STATUPDATE オプションを ON にした COPY コマンドを使用することで実行できます。最後の ANALYZE 以降に挿入または削除された行数の詳細を表示するには、PG_STATISTIC_INDICATOR システムカタログテーブルに対してクエリを実行します。

ANALYZE コマンドの範囲を次のいずれかに指定できます。

ANALYZE コマンドを実行すると、テーブルからサンプルの行が取得され、いくつかの計算が行われ、結果的に生成される列の統計が保存されます。デフォルトでは、Amazon Redshift は DISTKEY 列にサンプルパスを実行し、テーブルのその他すべての列に別のサンプルパスを実行します。列のサブセットの統計を生成するには、カンマ区切りの列リストを指定します。PREDICATE COLUMNS 句を使用して ANALYZE を実行して、述語として使用されていない列を省略できます。

ANALYZE 操作はリソースを集中的に使います。そのため、実際に統計更新を必要とするテーブルと列にのみ実行します。定期的に、または同じスケジュールですべてのテーブルのすべての行を分析する必要はありません。データが大幅に変更される場合、次で頻繁に使用される列を分析します。

処理時間を短縮し、システム全体のパフォーマンスを向上させるために、Amazon Redshift は、変更された行の割合が低いテーブルの ANALYZE をスキップします。この動作は analyze_threshold_percent パラメータで決定されます。デフォルトでは、分析のしきい値は 10 パーセントに設定されます。SET コマンドを実行して、現在のセッションの分析しきい値を変更できます。

頻繁に分析する必要のない列は、大きな VARCHAR 列など、実際に問い合わされることがない事実、単位、関連属性を表す列です。例えば、TICKIT データベースの LISTING テーブルについて考えてみます。

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       

このテーブルに大量の新しいレコードが毎日ロードされる場合、結合キーとしてクエリで頻繁に使用される LISTID 列を定期的に分析する必要があります。TOTALPRICE と LISTTIME が頻繁に使用されるクエリの制約である場合、平日は毎日、それらの列と分散キーを分析できます。

analyze listing(listid, totalprice, listtime);

アプリケーションの販売者とイベントが非常に静的であり、日付 ID がわずか 2 年または 3 年をカバーする固定日数セットを参照するとします。この場合、これらの列の一意の値は大きく変更されません。ただし、一意の各値のインスタンス数は着実に増加します。

さらに、NUMTICKETS および PRICEPERTICKET メジャーが TOTALPRICE 列とまれに比較され、クエリされるという場合を考えてみてください。この場合、毎週末に 1 回、テーブル全体で ANALYZE コマンドを実行して、毎日分析されていない 5 つの列の統計を更新することができます。

述語列

列リストを指定する、便利な代替方法として、述語として使用される可能性が高い列のみを分析するように選択できます。クエリを実行すると、結合、フィルタ条件、または GROUP BY 句で使用される列は、システムカタログの述語列としてマークされます。PREDICATE COLUMNS 句を指定して ANALYZE を実行すると、ANALYZE 操作には次の基準を満たす列のみが含まれます。

表の列のいずれも述部としてマークされていない場合、PREDICATE COLUMNS が指定されていても、ANALYZE にはすべての列が含まれます。述語の列としてマークされている列がない場合は、表がまだクエリされていない可能性があります。

ワークロードのクエリパターンが比較的安定している場合は、PREDICATE COLUMNS の使用を選択できます。クエリパターンが可変で、さまざまな列が頻繁に述語として使用される場合、PREDICATE COLUMNS を使用すると一時的に古い統計が返される場合があります。古い統計により、最適でないクエリランタイムプランや長いランタイムにつながる可能性があります。ただし、次に PREDICATE COLUMNS を使用して ANALYZE を実行すると、新しい述語の列が含まれます。

述語の列の詳細を表示するには、次の SQL を使用して 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;

LISTING テーブルに対して次のクエリを実行するとします。LISTID、LISTTIME、および EVENTID は、結合、フィルタ、および 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;

次の例に示すように、PREDICATE_COLUMNS ビューをクエリすると、LISTID、EVENTID、および LISTTIME が述語の列としてマークされていることが分かります。

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

統計を最新状態に保つことで、クエリプランナーが最適なプランを選択できるようになるため、クエリのパフォーマンスが向上します。Amazon Redshift は、統計をバックグラウンドで自動的に更新します。また、明示的に ANALYZE コマンドを実行することもできます。ANALYZE を明示的に実行することを選択した場合は、以下を実行する必要があります。

スナップショットをプロビジョニング済みクラスターまたはサーバーレス名前空間に復元した後や、一時停止中のプロビジョニング済みクラスターを再開した後で、ANALYZE コマンドを明示的に実行する必要はありません。Amazon Redshift は、このような場合でもシステムテーブル情報を保持するため、手動の ANALYZE コマンドは不要です。Amazon Redshift は、必要に応じて引き続き自動分析オペレーションを実行します。

分析オペレーションは、最新の統計を含むテーブルを省略します。抽出およびロード (ETL) ワークフローの一環として ANALYZE を実行する場合、自動分析は最新の統計を含むテーブルを省略します。同様に、自動分析によりテーブルの統計が更新された場合、明示的な ANALYZE はテーブルを省略します。

ANALYZE コマンド履歴

最後の ANALYZE コマンドがテーブルまたはデータベースで実行された日時を知っておくと役立ちます。ANALYZE コマンドが実行されると、Amazon Redshift は以下のような複数のクエリを実行します。

padb_fetch_sample: select * from table_name
               

STL_ANALYZE をクエリして、分析操作の履歴を表示します。Amazon Redshift が、自動分析でテーブルを分析する場合、is_background 列は t (true) に設定されます。それ以外の場合は、f (false) に設定されます。次の例では、STV_TBL_PERM を結合して、テーブル名とランタイムの詳細を表示します。

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)

または、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
...