OPTIMIZE_PARTITION_KEY_SCANS Query Option (CDH 5.7 or higher only)
Enables a fast code path for queries that apply simple aggregate functions to partition key columns: MIN(key_column), MAX(key_column), or COUNT(DISTINCT key_column).
Type: Boolean; recognized values are 1 and 0, or true and false; any other value interpreted as false
Default: false (shown as 0 in output of SET statement)
Added in: CDH 5.7.0 / Impala 2.5.0
Usage notes:
This optimization speeds up common "introspection" operations over partition key columns, for example determining the distinct values of partition keys.
-
Within a MAX() or MAX() aggregate function or as the argument of any aggregate function with the DISTINCT keyword applied.
-
Within a WHERE, GROUP BY or HAVING clause.
This optimization is enabled by a query option because it skips some consistency checks and therefore can return slightly different partition values if partitions are in the process of being added, dropped, or loaded outside of Impala. Queries might exhibit different behavior depending on the setting of this option in the following cases:
-
If files are removed from a partition using HDFS or other non-Impala operations, there is a period until the next REFRESH of the table where regular queries fail at run time because they detect the missing files. With this optimization enabled, queries that evaluate only the partition key column values (not the contents of the partition itself) succeed, and treat the partition as if it still exists.
-
If a partition contains any data files, but the data files do not contain any rows, a regular query considers that the partition does not exist. With this optimization enabled, the partition is treated as if it exists.
If the partition includes no files at all, this optimization does not change the query behavior: the partition is considered to not exist whether or not this optimization is enabled.
Examples:
The following example shows initial schema setup and the default behavior of queries that return just the partition key column for a table:
-- Make a partitioned table with 3 partitions.
create table t1 (s string) partitioned by (year int);
insert into t1 partition (year=2015) values ('last year');
insert into t1 partition (year=2016) values ('this year');
insert into t1 partition (year=2017) values ('next year');
-- Regardless of the option setting, this query must read the
-- data files to know how many rows to return for each year value.
explain select year from t1;
+-----------------------------------------------------+
| Explain String |
+-----------------------------------------------------+
| Estimated Per-Host Requirements: Memory=0B VCores=0 |
| |
| F00:PLAN FRAGMENT [UNPARTITIONED] |
| 00:SCAN HDFS [key_cols.t1] |
| partitions=3/3 files=4 size=40B |
| table stats: 3 rows total |
| column stats: all |
| hosts=3 per-host-mem=unavailable |
| tuple-ids=0 row-size=4B cardinality=3 |
+-----------------------------------------------------+
-- The aggregation operation means the query does not need to read
-- the data within each partition: the result set contains exactly 1 row
-- per partition, derived from the partition key column value.
-- By default, Impala still includes a 'scan' operation in the query.
explain select distinct year from t1;
+------------------------------------------------------------------------------------+
| Explain String |
+------------------------------------------------------------------------------------+
| Estimated Per-Host Requirements: Memory=0B VCores=0 |
| |
| 01:AGGREGATE [FINALIZE] |
| | group by: year |
| | |
| 00:SCAN HDFS [key_cols.t1] |
| partitions=0/0 files=0 size=0B |
+------------------------------------------------------------------------------------+
The following examples show how the plan is made more efficient when the OPTIMIZE_PARTITION_KEY_SCANS option is enabled:
set optimize_partition_key_scans=1; OPTIMIZE_PARTITION_KEY_SCANS set to 1 -- The aggregation operation is turned into a UNION internally, -- with constant values known in advance based on the metadata -- for the partitioned table. explain select distinct year from t1; +-----------------------------------------------------+ | Explain String | +-----------------------------------------------------+ | Estimated Per-Host Requirements: Memory=0B VCores=0 | | | | F00:PLAN FRAGMENT [UNPARTITIONED] | | 01:AGGREGATE [FINALIZE] | | | group by: year | | | hosts=1 per-host-mem=unavailable | | | tuple-ids=1 row-size=4B cardinality=3 | | | | | 00:UNION | | constant-operands=3 | | hosts=1 per-host-mem=unavailable | | tuple-ids=0 row-size=4B cardinality=3 | +-----------------------------------------------------+ -- The same optimization applies to other aggregation queries -- that only return values based on partition key columns: -- MIN, MAX, COUNT(DISTINCT), and so on. explain select min(year) from t1; +-----------------------------------------------------+ | Explain String | +-----------------------------------------------------+ | Estimated Per-Host Requirements: Memory=0B VCores=0 | | | | F00:PLAN FRAGMENT [UNPARTITIONED] | | 01:AGGREGATE [FINALIZE] | | | output: min(year) | | | hosts=1 per-host-mem=unavailable | | | tuple-ids=1 row-size=4B cardinality=1 | | | | | 00:UNION | | constant-operands=3 | | hosts=1 per-host-mem=unavailable | | tuple-ids=0 row-size=4B cardinality=3 | +-----------------------------------------------------+