メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012年12月1日)

クエリプランの例

この例では、クエリプランを評価して分散最適化の機会を特定する方法を示します。

EXPLAIN コマンドを使用して次のクエリを実行し、クエリプランを作成します。

Copy
explain select lastname, catname, venuename, venuecity, venuestate, eventname, month, sum(pricepaid) as buyercost, max(totalprice) as maxtotalprice from category join event on category.catid = event.catid join venue on venue.venueid = event.venueid join sales on sales.eventid = event.eventid join listing on sales.listid = listing.listid join date on sales.dateid = date.dateid join users on users.userid = sales.buyerid group by lastname, catname, venuename, venuecity, venuestate, eventname, month having sum(pricepaid)>9999 order by catname, buyercost desc;

TICKIT データベースでは、SALES がファクトテーブルであり、LISTING がその最大のディメンションです。テーブルをコロケーションするために、SALES は LISTING の外部キーである LISTID を使用して分散され、LISTING はそのプライマリキーである LISTID を使用して分散されます。次の例は、SALES および LISTID の CREATE TABLE コマンドを示しています。

Copy
create table sales( salesid integer not null, listid integer not null distkey, sellerid integer not null, buyerid integer not null, eventid integer not null encode mostly16, dateid smallint not null, qtysold smallint not null encode mostly8, pricepaid decimal(8,2) encode delta32k, commission decimal(8,2) encode delta32k, saletime timestamp, primary key(salesid), foreign key(listid) references listing(listid), foreign key(sellerid) references users(userid), foreign key(buyerid) references users(userid), foreign key(dateid) references date(dateid)) sortkey(listid,sellerid); create table listing( listid integer not null distkey sortkey, sellerid integer not null, eventid integer not null encode mostly16, dateid smallint not null, numtickets smallint not null encode mostly8, priceperticket decimal(8,2) encode bytedict, totalprice decimal(8,2) encode mostly32, listtime timestamp, primary key(listid), foreign key(sellerid) references users(userid), foreign key(eventid) references event(eventid), foreign key(dateid) references date(dateid));

次のクエリプランでは、SALES および LISTING を使用した結合のマージ結合ステップに、そのステップで再分散が必要とならないことを意味する DS_DIST_NONE が示されています。ただし、クエリプランを上に移動するとわかるとおり、他の内部結合には、クエリ実行の一環として内部テーブルがブロードキャストされることを意味する DS_BCAST_INNER が示されています。キー分散を使用してコロケーションできるテーブルは 1 ペアのみであるため、5 つのテーブルを再ブロードキャストする必要があります。

Copy
QUERY PLAN XN Merge (cost=1015345167117.54..1015345167544.46 rows=1000 width=103) Merge Key: category.catname, sum(sales.pricepaid) -> XN Network (cost=1015345167117.54..1015345167544.46 rows=170771 width=103) Send to leader -> XN Sort (cost=1015345167117.54..1015345167544.46 rows=170771 width=103) Sort Key: category.catname, sum(sales.pricepaid) -> XN HashAggregate (cost=15345150568.37..15345152276.08 rows=170771 width=103) Filter: (sum(pricepaid) > 9999.00) -> XN Hash Join DS_BCAST_INNER (cost=742.08..15345146299.10 rows=170771 width=103) Hash Cond: ("outer".catid = "inner".catid) -> XN Hash Join DS_BCAST_INNER (cost=741.94..15342942456.61 rows=170771 width=97) Hash Cond: ("outer".dateid = "inner".dateid) -> XN Hash Join DS_BCAST_INNER (cost=737.38..15269938609.81 rows=170766 width=90) Hash Cond: ("outer".buyerid = "inner".userid) -> XN Hash Join DS_BCAST_INNER (cost=112.50..3272334142.59 rows=170771 width=84) Hash Cond: ("outer".venueid = "inner".venueid) -> XN Hash Join DS_BCAST_INNER (cost=109.98..3167290276.71 rows=172456 width=47) Hash Cond: ("outer".eventid = "inner".eventid) -> XN Merge Join DS_DIST_NONE (cost=0.00..6286.47 rows=172456 width=30) Merge Cond: ("outer".listid = "inner".listid) -> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=14) -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=24) -> XN Hash (cost=87.98..87.98 rows=8798 width=25) -> XN Seq Scan on event (cost=0.00..87.98 rows=8798 width=25) -> XN Hash (cost=2.02..2.02 rows=202 width=41) -> XN Seq Scan on venue (cost=0.00..2.02 rows=202 width=41) -> XN Hash (cost=499.90..499.90 rows=49990 width=14) -> XN Seq Scan on users (cost=0.00..499.90 rows=49990 width=14) -> XN Hash (cost=3.65..3.65 rows=365 width=11) -> XN Seq Scan on date (cost=0.00..3.65 rows=365 width=11) -> XN Hash (cost=0.11..0.11 rows=11 width=10) -> XN Seq Scan on category (cost=0.00..0.11 rows=11 width=10)

DISTSTYLE ALL を使用してテーブルを再作成することは 1 つの解決策です。テーブルを作成した後でそのテーブルの分散スタイルを変更することはできません。別の分散スタイルを指定してテーブルを再作成するには、ディープコピーを使用します。

まず、テーブルの名前を変更します。

Copy
alter table users rename to userscopy; alter table venue rename to venuecopy; alter table category rename to categorycopy; alter table date rename to datecopy; alter table event rename to eventcopy;

次のスクリプトを実行して USERS、VENUE、CATEGORY、DATE、EVENT を再作成します。SALES と LISTING には変更を加えないでください。

Copy
create table users( userid integer not null sortkey, username char(8), firstname varchar(30), lastname varchar(30), city varchar(30), state char(2), email varchar(100), phone char(14), likesports boolean, liketheatre boolean, likeconcerts boolean, likejazz boolean, likeclassical boolean, likeopera boolean, likerock boolean, likevegas boolean, likebroadway boolean, likemusicals boolean, primary key(userid)) diststyle all; create table venue( venueid smallint not null sortkey, venuename varchar(100), venuecity varchar(30), venuestate char(2), venueseats integer, primary key(venueid)) diststyle all; create table category( catid smallint not null, catgroup varchar(10), catname varchar(10), catdesc varchar(50), primary key(catid)) diststyle all; create table date( dateid smallint not null sortkey, caldate date not null, day character(3) not null, week smallint not null, month character(5) not null, qtr character(5) not null, year smallint not null, holiday boolean default('N'), primary key (dateid)) diststyle all; create table event( eventid integer not null sortkey, venueid smallint not null, catid smallint not null, dateid smallint not null, eventname varchar(200), starttime timestamp, primary key(eventid), foreign key(venueid) references venue(venueid), foreign key(catid) references category(catid), foreign key(dateid) references date(dateid)) diststyle all;

テーブルにデータを再び挿入して、ANALYZE コマンドを実行して統計を更新します。

Copy
insert into users select * from userscopy; insert into venue select * from venuecopy; insert into category select * from categorycopy; insert into date select * from datecopy; insert into event select * from eventcopy; analyze;

最後にコピーを削除します。

Copy
drop table userscopy; drop table venuecopy; drop table categorycopy; drop table datecopy; drop table eventcopy;

EXPLAIN を使用して同じクエリを再実行し、新しいクエリプランを検証します。DISTSTYLE ALL を使用して全ノードにデータが分散されたため再分散が必要とならないことを意味する DS_DIST_ALL_NONE が、結合に表示されるようになります。

Copy
QUERY PLAN XN Merge (cost=1000000047117.54..1000000047544.46 rows=1000 width=103) Merge Key: category.catname, sum(sales.pricepaid) -> XN Network (cost=1000000047117.54..1000000047544.46 rows=170771 width=103) Send to leader -> XN Sort (cost=1000000047117.54..1000000047544.46 rows=170771 width=103) Sort Key: category.catname, sum(sales.pricepaid) -> XN HashAggregate (cost=30568.37..32276.08 rows=170771 width=103) Filter: (sum(pricepaid) > 9999.00) -> XN Hash Join DS_DIST_ALL_NONE (cost=742.08..26299.10 rows=170771 width=103) Hash Cond: ("outer".buyerid = "inner".userid) -> XN Hash Join DS_DIST_ALL_NONE (cost=117.20..21831.99 rows=170766 width=97) Hash Cond: ("outer".dateid = "inner".dateid) -> XN Hash Join DS_DIST_ALL_NONE (cost=112.64..17985.08 rows=170771 width=90) Hash Cond: ("outer".catid = "inner".catid) -> XN Hash Join DS_DIST_ALL_NONE (cost=112.50..14142.59 rows=170771 width=84) Hash Cond: ("outer".venueid = "inner".venueid) -> XN Hash Join DS_DIST_ALL_NONE (cost=109.98..10276.71 rows=172456 width=47) Hash Cond: ("outer".eventid = "inner".eventid) -> XN Merge Join DS_DIST_NONE (cost=0.00..6286.47 rows=172456 width=30) Merge Cond: ("outer".listid = "inner".listid) -> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=14) -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=24) -> XN Hash (cost=87.98..87.98 rows=8798 width=25) -> XN Seq Scan on event (cost=0.00..87.98 rows=8798 width=25) -> XN Hash (cost=2.02..2.02 rows=202 width=41) -> XN Seq Scan on venue (cost=0.00..2.02 rows=202 width=41) -> XN Hash (cost=0.11..0.11 rows=11 width=10) -> XN Seq Scan on category (cost=0.00..0.11 rows=11 width=10) -> XN Hash (cost=3.65..3.65 rows=365 width=11) -> XN Seq Scan on date (cost=0.00..3.65 rows=365 width=11) -> XN Hash (cost=499.90..499.90 rows=49990 width=14) -> XN Seq Scan on users (cost=0.00..499.90 rows=49990 width=14)