UNNEST句を使用したネスト解除問合せの最適化

ネスト解除されている配列またはマップに索引がある場合は、UNNEST句をお薦めします。UNNEST句では、含まれる式の種類に制限があります。これらの制限によって、問合せプロセッサでネスト解除された配列/マップ上の適切な索引を選択できるため、パフォーマンスが大幅に向上します。

ノート:

UNNEST句によって適用される制限の詳細は、UNNEST句での式の使用の制限を参照してください。ここで、ネスト解除問合せを使用するための表の作成およびデータのロード用のSQL文を使用できます。

例1: 米国で放送された様々な番組と、それらを視聴する人の数をフェッチします

問合せは国(米国)および特定の番組に固有です。そのため、stream_acct表のcountryおよびshowidフィールドに索引を作成します。
```
CREATE INDEX idx_country_showid ON stream_acct( acct_data.country AS string,
                                                acct_data.contentStreamed[].showId AS integer)
WITH UNIQUE KEYS PER ROW
```
ノート:
索引と同じ配列/マップをネスト解除する問合せで索引を使用できるようにするには、行プロパティごとに一意のキーを使用して索引を作成する必要があります。
UNNEST句を使用して、米国で放送された様々な番組と、それを視聴する人の数をフェッチする問合せ。
```
SELECT $show.showId, count(*) as cnt
FROM stream_acct $s, unnest($s.acct_data.contentStreamed[] as $show)
WHERE $s.acct_data.country = "USA"
GROUP BY $show.showId ORDER BY count(*) desc

{"showId":12,"cnt":2}
{"showId":13,"cnt":1}
{"showId":16,"cnt":1}
```
前述の問合せはidx_country_showid索引を使用します。国の条件は索引にプッシュされ、グループ化は索引ベースになり、索引はこの問合せのカバー索引です。問合せを解決するために必要なすべての情報を含む索引は、カバー索引と呼ばれ、問合せを完全にカバーします。カバー索引にはすべての列が含まれ、問合せはSELECT句、JOIN句およびWHERE句で参照します。UNNEST句を使用しない場合、索引は考慮されません。

例2: アプリケーションで放送されたすべての番組についての、すべてのユーザーの合計視聴時間:

次の問合せは、各番組について、ユーザーがその番組の視聴に費やした合計時間を返します。

SELECT $show.showId, sum($show.seriesInfo.episodes.minWatched) AS total_time
FROM stream_acct $s, unnest($s.acct_data.contentStreamed[] AS $show) GROUP BY $show.showId
ORDER BY SUM($show.seriesInfo.episodes.minWatched)

showidに基づいてデータがグループ化されているため、acct_data.contentStreamed[].showIdに索引を作成するという効果を検討します。

UNNEST句の使用に関係なく、この問合せではidx_showid索引を使用できません。これは、sum()関数の引数によるものです。idx_showid索引には、showid (および主キー)のみが含まれます。そのため、式$show.seriesInfo.episodes.minWatchedは索引から評価できません。この問合せを最適化するには、2つの方法があります。

オプション1: 追加索引の作成:

showIdフィールドとminWatchedフィールドの両方を問合せで使用するため、それらに追加のコンポジット索引を作成します。

CREATE INDEX idx_showid_minWatched ON
      stream_acct(acct_data.contentStreamed[].showId AS integer,
      acct_data.contentStreamed[].seriesInfo[].episodes[].minWatched AS integer,
      acct_data.contentStreamed[].seriesInfo[].episodes[].episodeID as integer)
WITH UNIQUE KEYS PER ROW

ノート:

行ごとに一意のキーの制約を満たすには、この索引でepisodeIDを最後の索引パスとして追加する必要があります。この索引は、問合せによってカバー索引として使用されます。

オプション2: 追加索引のコストの回避:

idx_showid索引を使用するように問合せをリライトできます。次のリライトされた問合せではidx_showid索引が使用されますが、索引はカバーしていません。

SELECT $show.showId, sum($s.acct_data.contentStreamed[$element.showId = $show.showId].
                        seriesInfo.episodes.minWatched) AS total_time
FROM stream_acct $s, unnest($s.acct_data.contentStreamed[] AS $show) GROUP BY $show.showId
ORDER BY sum($s.acct_data.contentStreamed[$element.showId = $show.showId].seriesInfo.episodes.minWatched)

{"showId":13,"total_time":125}
{"showId":15,"total_time":125}
{"showId":16,"total_time":125}
{"showId":14,"total_time":178}
{"showId":12,"total_time":350}

例3: 番組およびシーズンごとのユーザーの合計視聴時間

SELECT $show.showId, $seriesInfo.seasonNum, 
sum($seriesInfo.episodes.minWatched) AS length
FROM stream_acct n,
unnest(n.acct_data.contentStreamed[] AS $show, $show.seriesInfo[] as $seriesInfo)
GROUP BY $show.showId, $seriesInfo.seasonNum
ORDER BY sum($seriesInfo.episodes.minWatched)

{"showId":14,"seasonNum":1,"length":82}
{"showId":14,"seasonNum":2,"length":96}
{"showId":13,"seasonNum":1,"length":125}
{"showId":15,"seasonNum":1,"length":125}
{"showId":16,"seasonNum":1,"length":125}
{"showId":12,"seasonNum":1,"length":158}
{"showId":12,"seasonNum":2,"length":192}

前述の問合せのパフォーマンスを最大化するには、次の索引を作成します。索引idx_showid_seasonNum_minWatchedは複数キー索引です。行ごとに一意のキーの制約を満たすには、この索引でepisodeIDを最後の索引パスとして追加する必要があります。

CREATE INDEX idx_showid_seasonNum_minWatched ON
        stream_acct(acct_data.contentStreamed[].showId as integer,
        acct_data.contentStreamed[].seriesInfo[].seasonNum as integer,
        acct_data.contentStreamed[].seriesInfo[].episodes[].minWatched as integer,
        acct_data.contentStreamed[].seriesInfo[].episodes[].episodeID as integer)
WITH UNIQUE KEYS PER ROW

ノート:

索引は、表のデータ行ごとに索引に複数のエントリが作成される場合、複数キー索引と呼ばれます。複数キー索引には、.keys()、.values()または[]ステップを使用する索引パスが少なくとも1つあります。このような索引パスはすべて、複数キー索引パスと呼ばれます。

問合せで、ネスト解除している配列/マップの索引を使用する場合、UNNEST句の各パス式は、索引内の索引パスのmultikey_path_prefixと一致する必要があります。CREATE INDEX文の構文に示すように、multikey_path_prefixは、最後の複数キー・ステップまで(そのステップを含む)の索引パスの一部です。たとえば、最初の索引パスのmultikey_path_prefixは、UNNEST句の最初のパス式に一致する必要があります。そうでない場合、索引は使用されません。

前述の問合せで、式n.value.contentStreamed[]はidx_showid_seasonNum_minWatchedの最初の索引パスのmultikey_path_prefixと一致し、式$show.seriesInfo[]は、$show変数がドメイン式に置き換えられた後の2番目の索引パスのmultikey_path_prefixと一致します。そのため、この索引は問合せで使用されます。