Exemplo de plano de consulta
Este exemplo mostra como avaliar um plano de consulta para encontrar oportunidades de otimização da distribuição.
Execute a seguinte consulta com um comando EXPLAIN para produzir um plano de consulta.
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;
No banco de dados TICKIT, SALES é uma tabela de fatos e LISTING é a maior dimensão. Para posicionar as tabelas, SALES é distribuída em LISTID, que é a chave estrangeira para LISTING, e LISTING é distribuída em sua chave primária, LISTID. O exemplo a seguir mostra os comandos CREATE TABLE para SALES e LISTING.
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));
No seguinte plano de consulta, a etapa merge join para a junção em SALES e LISTING mostra DS_DIST_NONE, que indica que nenhuma redistribuição é necessária para a etapa. No entanto, mais acima no plano de consulta, outras junções internas exibem DS_BCAST_INNER, que indica que a tabela interna é transmitida como parte da execução da consulta. Como somente um par de tabelas pode ser posicionado usando a distribuição de chaves, cinco tabelas precisam ser retransmitidas.
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)
Uma solução é alterar as tabelas para que tenham DISTSTYLE ALL.
ALTER TABLE users ALTER DISTSTYLE ALL; ALTER TABLE venue ALTER DISTSTYLE ALL; ALTER TABLE category ALTER DISTSTYLE ALL; ALTER TABLE date ALTER DISTSTYLE ALL; ALTER TABLE event ALTER DISTSTYLE ALL;
Execute a mesma consulta com EXPLAIN novamente e examine o novo plano de consulta. As junções agora exibem DS_DIST_ALL_NONE, indicando que nenhuma redistribuição é necessária, pois os dados foram distribuídos para todos os nós usando DISTSTYLE ALL.
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)
