背景・目的

しばらく前ですが、AthenaでGlueのパーティションインデックスが利用できるようになったので効果を確認したいと思います。
以下の観点で検証を行います。

観点1:パーティションインデックスを使った場合と、使わなかった場合の違い
観点2:パーティションインデックスが効かないクエリを調べる

まとめ

パーティションインデックスは有効と考えます。
- 特にパーティション数が多くなるほど、効果は絶大です。
- 対して絞り込まない、クエリの場合は差異は少ないですが、絞り込みが多いほどクエリの性能改善に繋がります。
パーティションのバッドプラクティスは確かに存在します。
- 一般的なRDBと同じ考え方で、NOTやORなどでは、パーティションインデックスは効きません。
- クエリを書く際には注意が必要です。
結果は考察にまとめました。
その他
- MSCK REPAIRによるパーティション作成では、なんと、6時間9分46秒かかりました。
- Crawlerで作成するほうが実行時間は短いとも聞いたことがあるので、次回は、Crawlerでパーティションを作成し、比較してみたいと思います。

概要

パーティションインデックスとは？

そもそも、「パーティションインデックスとは何か？」について、簡単にまとめます。

課題と対応

パーティション数が極端に多い場合、クエリの処理時間に影響が出ます。
- 例えば、年月日時分のように、毎分S3パーティションがふえていくようなVPCFlowログなどは、一年間で、525,600パーティションになります。(365日＊24時間*60分)
パーティションの取得では、カタログに問い合わせ、テーブルの全てのパーティションを一度取得してからフィルタリングされます。あまりにもパーティションが多い場合、この取得処理がボトルネックによりクエリ全体のパーフォーマンスに影響を及ぼします。
パーティションインデックスを利用した場合は、クエリにマッチしたパーティションのみ取得する（場合によっては、取得後にフィルタリングも行われる。）ため、このパーティション取得のオーバヘッドが下がります。そのため、数が多いパーティションではパーティションインデックスを積極的に採用していくことが重要になります。

注意点

パーティションインデックスは、一つのテーブルあたり最大3つまで作成が可能です。
パーティションインデックスが効く、クエリには条件があります。以下のクエリの場合、パーティションインデックスは効果がありません。
- データ型が文字列、数値以外。例えば日付型など。
- LIKE、IN、OR、NOTの演算子を使用したクエリ。
パーティションインデックスを作成後に、パーティションキー名を変更することができません。
インデックスに登録されているキーのデータ型、順序は変更できません。

実践

準備

S3ファイルの用意

以下のパーティションを用意するため、あらかじめS3オブジェクトを用意します。
- 年は、2013年〜2022年
- 月は、1月〜12月
- 日は、1日〜31日（※面倒なので、1月〜12月すべて31日分あります。。。）
- 時間は、0時〜23時
パーティションキーは、year,month,day,hourとします。
以下のデータとコマンドで、実行します。

// 対象のファイル
$ cat test.json 
{"key":"a","value":1}

// これを10回(10年分)実行します。
// 最初から、yearもループにしておけばよかったと後悔。
$ 
for i in `seq 1 12`;do for j in `seq 1 31`;do for k in `seq 0 23`;do MONTH=`printf "%02d" $i`; DAY=`printf "%02d" $j`; HOUR=`printf "%02d" $k `;aws s3api put-object --bucket  {バケット名} --key "year=2021/month=$MONTH/day=$DAY/hour=$HOUR/test.json" --body ./test.json   ;done;done;done
$

実行結果は、以下の通りです。
件数が若干想定外でしたが、まぁいいでしょう。

$ aws s3 ls {バケット名}  --recursive  | grep json | wc -l
   89279
$

Athenaのテーブルを用意

パーティションインデックスを作成するには、以下の対応が必要です。
パーティションキーに加えて、TBLPROPERTIESに、以下を追加します。

partition_filtering.enabled'='true'

以下のDDLを実行し、テーブルを作成します。

CREATE EXTERNAL TABLE part_db.`test`(
  `key` string,
  `value` bigint
)
PARTITIONED BY (
  year int,
  month int,
  day int,
  hour int)
ROW FORMAT SERDE
  "org.openx.data.jsonserde.JsonSerDe"
STORED AS INPUTFORMAT
  "org.apache.hadoop.mapred.TextInputFormat"
OUTPUTFORMAT
  "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
LOCATION
  "s3://{バケット名}/"
TBLPROPERTIES (
  'partition_filtering.enabled'='true'
)

実行結果は、以下の通りです。
- この時点では、パーティションもパーティションインデックスも作成されていません。

SHOW CREATE TABLE `test`;

CREATE EXTERNAL TABLE `test`(
  `key` string COMMENT 'from deserializer', 
  `value` bigint COMMENT 'from deserializer')
PARTITIONED BY ( 
  `year` int, 
  `month` int, 
  `day` int, 
  `hour` int)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://{バケット名}/'
TBLPROPERTIES (
  'partition_filtering.enabled'='true', 
  'transient_lastDdlTime'='1650438649')

パーティションの作成

以下のコマンドで、パーティションをカタログに登録します。
なんと、6時間9分46秒かかりました。
以下のクエリで実行結果を確認します。


-- 値の確認
aws glue get-partitions --database-name part_db --table-name test  --max-items 3 | jq -r '.Partitions[].Values| @csv ' | tr -d  "\""

===

-- 件数の確認
aws glue get-partitions --database-name part_db --table-name test  | jq -r '.Partitions[].Values| @csv ' | tr -d  "\"" | wc -l

結果は、以下の様になります。
89,279件でした。S3のパーティション数と同様なので問題ありません。

-- 値の確認

aws glue get-partitions --database-name part_db --table-name test  --max-items 3 | jq -r '.Partitions[].Values| @csv ' | tr -d  "\""

===

$ aws glue get-partitions --database-name part_db --table-name test  --max-items 3 | jq -r '.Partitions[].Values| @csv ' | tr -d  "\""
2013,01,03,17
2013,01,06,10
2013,01,07,11
$

-- 結果の確認

$ aws glue get-partitions --database-name part_db --table-name test  | jq -r '.Partitions[].Values| @csv ' | tr -d  "\"" | wc -l
   89279
$

準備ができましたので、次よりクエリを実行したいと思います。

パーティションインデックスの確認

以下のクエリにより、この時点で、パーティションインデックスが存在しないことを確認します。

aws glue get-partition-indexes --database-name part_db --table-name test

実行結果は、以下のとおりです。

$ aws glue get-partition-indexes --database-name part_db --table-name test
{
    "PartitionIndexDescriptorList": []
}
$

観点1:パーティションインデックスを使った場合と、使わなかった場合の違い

まずは、パーティションインデックスがない状態で、クエリを実行します。

パーティションインデックスがない状態

クエリ1（指定なし）

以下のクエリを実行します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary
{
    "QueryExecutionId": "0331f1f8-a035-4a15-aa5d-6c94f7159062"
}

$ aws athena get-query-execution --query-execution-id 0331f1f8-a035-4a15-aa5d-6c94f7159062 --query QueryExecution.Statistics --output table  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  1964138           |  99286                       |  23353                     |  176                    |  37                             |  99499                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+

クエリ2（yearのみ指定）

以下のクエリを実行します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary

{
    "QueryExecutionId": "216b9c98-68f3-4cd6-8935-75d297f20c42"
}

$ aws athena get-query-execution --query-execution-id 216b9c98-68f3-4cd6-8935-75d297f20c42 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  196416            |  11991                       |  7701                      |  137                    |  20                             |  12148                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ3（yearとmonthを指定）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary

{
    "QueryExecutionId": "1f8f0238-1958-488d-ab67-bf00a7146e26"
}

$ aws athena get-query-execution --query-execution-id 1f8f0238-1958-488d-ab67-bf00a7146e26 --query QueryExecution.Statistics --output table

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  16368             |  18193                       |  14011                     |  197                    |  20                             |  18410                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ4（yearとmonth、dayを指定）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary

結果は、以下のとおりです。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary

{
    "QueryExecutionId": "ee12a138-cf0c-4019-a9c0-05c6ad643e37"
}

$ aws athena get-query-execution --query-execution-id ee12a138-cf0c-4019-a9c0-05c6ad643e37 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  528               |  14696                       |  14304                     |  176                    |  54                             |  14926                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ5（yearとmonthとday、hourを指定）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary

{
    "QueryExecutionId": "ba5ea9ba-3d48-4342-add3-80cc2f784870"
}

$ aws athena get-query-execution --query-execution-id ba5ea9ba-3d48-4342-add3-80cc2f784870 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  22                |  13018                       |  12656                     |  187                    |  30                             |  13235                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ6（範囲検索）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary

{
    "QueryExecutionId": "48959f44-f135-4e53-96a4-5a6aae9a39c5"
}
$ 

$ aws athena get-query-execution --query-execution-id 48959f44-f135-4e53-96a4-5a6aae9a39c5 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  506               |  16742                       |  13069                     |  173                    |  30                             |  16945                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

パーティションインデックスの作成

以下のコマンドで、パーティションインデックスを作成します。
作成に要した時間は、不明ですが15分程度でActiveになりました。

$ aws glue create-partition-index --database-name part_db --table-name test --partition-index Keys=year,month,day,hour,IndexName=year-month-day-hour

実行結果は、以下のとおりです。

$ aws glue create-partition-index --database-name part_db --table-name test --partition-index Keys=year,month,day,hour,IndexName=year-month-day-hour
$

-- 最初はCREATINGになります。
$ aws glue get-partition-indexes --database-name part_db --table-name test
{
    "PartitionIndexDescriptorList": [
        {
            "IndexName": "year-month-day-hour",
            "Keys": [
                {
                    "Name": "year",
                    "Type": "int"
                },
                {
                    "Name": "month",
                    "Type": "int"
                },
                {
                    "Name": "day",
                    "Type": "int"
                },
                {
                    "Name": "hour",
                    "Type": "int"
                }
            ],
            "IndexStatus": "CREATING"
        }
    ]
}
$ 

-- ACTIVEになると完了です。
$ aws glue get-partition-indexes --database-name part_db --table-name test                                                                 
{
    "PartitionIndexDescriptorList": [
        {
            "IndexName": "year-month-day-hour",
            "Keys": [
                {
                    "Name": "year",
                    "Type": "int"
                },
                {
                    "Name": "month",
                    "Type": "int"
                },
                {
                    "Name": "day",
                    "Type": "int"
                },
                {
                    "Name": "hour",
                    "Type": "int"
                }
            ],
            "IndexStatus": "ACTIVE"
        }
    ]
}
$

パーティションインデックスがある状態

クエリ1（指定なし）

以下のクエリを実行します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test" --work-group primary
{
    "QueryExecutionId": "27d08a76-194f-46ad-95bb-0b0d841d7911"
}
$ aws athena get-query-execution --query-execution-id 27d08a76-194f-46ad-95bb-0b0d841d7911 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  1964138           |  106746                      |  23691                     |  280                    |  79                             |  107105                      |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ2（yearのみ指定）

以下のクエリを実行します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022" --work-group primary
{
    "QueryExecutionId": "7887a578-7dc3-4ae6-8727-ed8fdaad6a77"
}

aws athena get-query-execution --query-execution-id 7887a578-7dc3-4ae6-8727-ed8fdaad6a77 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  196416            |  10979                       |  7532                      |  135                    |  192                            |  11306                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ3（yearとmonthを指定）

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01" --work-group primary
{
    "QueryExecutionId": "65a0ad4f-41cc-4768-b0f2-131a7e5f76a8"
}

$ aws athena get-query-execution --query-execution-id 65a0ad4f-41cc-4768-b0f2-131a7e5f76a8 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  16368             |  2419                        |  1575                      |  173                    |  25                             |  2617                        |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ4（yearとmonth、dayを指定）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01" --work-group primary
{
    "QueryExecutionId": "2a2a9e11-201d-4d65-9471-8a4d2c5a7864"
}

$ aws athena get-query-execution --query-execution-id 2a2a9e11-201d-4d65-9471-8a4d2c5a7864 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  528               |  1300                        |  529                       |  259                    |  105                            |  1664                        |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ5（yearとmonthとday、hourを指定）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour=01" --work-group primary
{
    "QueryExecutionId": "5b48240b-eb51-4799-b470-2aee63fb36e5"
}

$ aws athena get-query-execution --query-execution-id 5b48240b-eb51-4799-b470-2aee63fb36e5 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  22                |  562                         |  260                       |  184                    |  28                             |  774                         |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ6（範囲検索）

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2022 AND month=01 AND day=01 AND hour >= 01 AND hour <= 23" --work-group primary
{
    "QueryExecutionId": "2a4f48eb-e1d9-4c74-b210-9735be389626"
}

$ aws athena get-query-execution --query-execution-id 2a4f48eb-e1d9-4c74-b210-9735be389626 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  506               |  1139                        |  506                       |  257                    |  93                             |  1489                        |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

観点2:パーティションインデックスが効くクエリと、効かないクエリの違い

次に、パーティションインデックスがある状態で、クエリの指定による違いを確認します。

クエリ1.インデックスを順番通り使わない（month,day,hourを指定）

以下のクエリを実行します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE month=01 AND day=01 AND hour=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE month=01 AND day=01 AND hour=01" --work-group primary
{
    "QueryExecutionId": "c9da4008-dbc3-41dc-ba89-36b23eae2f27"
}
$

$ aws athena get-query-execution --query-execution-id c9da4008-dbc3-41dc-ba89-36b23eae2f27 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  220               |  20473                       |  20037                     |  196                    |  63                             |  20732                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ2.ORを使う場合

以下のクエリを実行します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2021 AND month=01 AND day=01 AND hour=01 OR hour=02" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year=2021 AND month=01 AND day=01 AND hour=01 OR hour=02" --work-group primary
{
    "QueryExecutionId": "c719e1d8-985d-4a62-9946-2918592db99a"
}
$ 

$ aws athena get-query-execution --query-execution-id c719e1d8-985d-4a62-9946-2918592db99a --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  81862             |  26795                       |  24914                     |  262                    |  74                             |  27131                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ3.INを使う場合

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year IN(2021,2022) AND month=01 AND day=01 AND hour=01" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year IN(2021,2022) AND month=01 AND day=01 AND hour=01" --work-group primary

{
    "QueryExecutionId": "ab5fb26c-6845-445c-968c-b45e00f36409"
}
$

$ aws athena get-query-execution --query-execution-id ab5fb26c-6845-445c-968c-b45e00f36409 --query QueryExecution.Statistics --output table
$ aws athena get-query-execution --query-execution-id ab5fb26c-6845-445c-968c-b45e00f36409 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  44                |  14321                       |  13974                     |  202                    |  34                             |  14557                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ4.NOTを使う場合

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year= 2020  AND month=01 AND day=01 AND hour NOT BETWEEN 01 AND 02" --work-group primary

結果は、以下のとおりです。

$ aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year NOT IN(2022,2020,2019,2018,2017,2016,2015,2014,2013)  AND month=01 AND day=01 AND hour =01" --work-group primary

{
    "QueryExecutionId": "56d9445f-34bf-40fb-a552-988c5491de01"
}
$ 
$ aws athena get-query-execution --query-execution-id 56d9445f-34bf-40fb-a552-988c5491de01 --query QueryExecution.Statistics --output table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                             GetQueryExecution                                                                             |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
| DataScannedInBytes | EngineExecutionTimeInMillis  | QueryPlanningTimeInMillis  | QueryQueueTimeInMillis  |  ServiceProcessingTimeInMillis  | TotalExecutionTimeInMillis   |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
|  22                |  16605                       |  16273                     |  167                    |  15                             |  16787                       |
+--------------------+------------------------------+----------------------------+-------------------------+---------------------------------+------------------------------+
$

クエリ5.LIKEを使う場合

今回、パーティションをindexで設定したため確認できず。。後ほど確認します。

aws athena start-query-execution --query-string "SELECT SUM(1) FROM part_db.test WHERE year like '202%' AND month=01 AND day=01 AND hour=01" --work-group primary

結果確認用のクエリ

aws athena get-query-execution --query-execution-id {} --query QueryExecution.Statistics --output table

考察

観点1の結果
- パーティションインデックスで絞り込める量が大きいほど、効果はみられました。（観点1の#4.QueryPlanningTimeInMillisで、98.18%の削減効果）
観点2の結果
- マニュアルにあるパーティションインデックスが使用されないクエリのケースでは、いずれも全体に対して90%以上QueryPlannningTimeInMills(パーティション取得の時間と考えられる。)が占めていた。

結果の整理

今回、検証した結果を以下のように整理します。

観点1:パーティションインデックスを使った場合と、使わなかった場合の違い

#	クエリのパターン	内訳	PartIndexなしの時間(ms)	PartIndexありの時間（ms）	QueryPlanningTimeInMillis 削減時間(削減率)
1.	指定なし	DataScannedInBytes	1964138	1964138
		EngineExecutionTimeInMillis	99286	106746
		QueryPlanningTimeInMillis	23353	23691	-338（-1.44%）
		QueryQueueTimeInMillis	176	280
		ServiceProcessingTimeInMillis	37	79
		TotalExecutionTimeInMillis	99499	107105
2.	yearのみ	DataScannedInBytes	196416	196416
		EngineExecutionTimeInMillis	11991	10979
		QueryPlanningTimeInMillis	7701	7532	169(0.08%)
		QueryQueueTimeInMillis	137	135
		ServiceProcessingTimeInMillis	20	192
		TotalExecutionTimeInMillis	12148	11306
3.	yearとmonthを指定	DataScannedInBytes	16368	16368
		EngineExecutionTimeInMillis	18193	2419
		QueryPlanningTimeInMillis	14011	1575	12436(88.75%)
		QueryQueueTimeInMillis	197	173
		ServiceProcessingTimeInMillis	20	25
		TotalExecutionTimeInMillis	18410	2617
4.	yearとmonthとdayを指定	DataScannedInBytes	528	528
		EngineExecutionTimeInMillis	14696	1300
		QueryPlanningTimeInMillis	14304	260	14044(98.18%)
		QueryQueueTimeInMillis	176	259
		ServiceProcessingTimeInMillis	54	105
		TotalExecutionTimeInMillis	14926	1664
5.	year,month,day,hourを指定	DataScannedInBytes	22	22
		EngineExecutionTimeInMillis	13018	562
		QueryPlanningTimeInMillis	12656	260	12,396(97.94%)
		QueryQueueTimeInMillis	187	184
		ServiceProcessingTimeInMillis	30	28
		TotalExecutionTimeInMillis	13235	774
6.	範囲検索	DataScannedInBytes	506	506
		EngineExecutionTimeInMillis	16742	1139
		QueryPlanningTimeInMillis	13069	506	12,563(96.12%)
		QueryQueueTimeInMillis	173	257
		ServiceProcessingTimeInMillis	30	93
		TotalExecutionTimeInMillis	16945	1489

観点2:パーティションインデックスが効かないクエリの時間

#	クエリのパターン	内訳	時間(ms)	QueryPlanningTimeInMillisが全体の実行時間に占める割合
1.	month,day,hourのみ指定	DataScannedInBytes	220
		EngineExecutionTimeInMillis	20473
		QueryPlanningTimeInMillis	20037	96.64%
		QueryQueueTimeInMillis	196
		ServiceProcessingTimeInMillis	63
		TotalExecutionTimeInMillis	20732
2.	ORを指定	DataScannedInBytes	81862
		EngineExecutionTimeInMillis	26795
		QueryPlanningTimeInMillis	24914	91.82%
		QueryQueueTimeInMillis	262
		ServiceProcessingTimeInMillis	74
		TotalExecutionTimeInMillis	27131
3.	INを指定	DataScannedInBytes	44
		EngineExecutionTimeInMillis	14321
		QueryPlanningTimeInMillis	13974	95.99%
		QueryQueueTimeInMillis	202
		ServiceProcessingTimeInMillis	34
		TotalExecutionTimeInMillis	14557
4.	NOTを指定	DataScannedInBytes	22
		EngineExecutionTimeInMillis	16605
		QueryPlanningTimeInMillis	16273	96.93%
		QueryQueueTimeInMillis	167
		ServiceProcessingTimeInMillis	15
		TotalExecutionTimeInMillis	16787

参考