explain 명령

explain 명령

explain 명령은 쿼리의 실행 계획을 표시해요. 쿼리 번역과 문제 해결에 자주 사용돼요. explain 명령은 PPL 쿼리에서 첫 번째 명령으로만 사용할 수 있어요.

출처: 문서

본문

구문(Syntax)

explain 명령은 다음과 같은 구문을 가져요.

explain <mode> queryStatement

매개변수(Parameters)

explain 명령은 다음 매개변수를 지원해요.

예시 1: v2 엔진에서 PPL 쿼리 설명하기

Apache Calcite가 비활성화되면(plugins.calcite.enabled가 false로 설정), explain은 v2 엔진에서 물리적 계획과 push-down 정보를 가져와요.

explain source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

이 쿼리는 다음과 같은 결과를 반환해요.

{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[count(), country]"
    },
    "children": [
      {
        "name": "OpenSearchIndexScan",
        "description": {
          "request": "\"\"\"OpenSearchQueryRequest(indexName=state_country, sourceBuilder={\"from\":0,\"size\":10000,\"timeout\":\"1m\",\"query\":{\"bool\":{\"should\":[{\"term\":{\"country\":{\"value\":\"USA\",\"boost\":1.0}}},{\"term\":{\"country\":{\"value\":\"England\",\"boost\":1.0}}}],\"adjust_pure_negative\":true,\"boost\":1.0}},\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":1000,\"sources\":[{\"country\":{\"terms\":{\"field\":\"country\",\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]},\"aggregations\":{\"count()\":{\"value_count\":{\"field\":\"_index\"}}}}}}, pitId=null, cursorKeepAlive=null, searchAfter=null, searchResponse=null)\"\"\"
        },
        "children": []
      }
    ]
  }
}

예시 2: v3 엔진에서 PPL 쿼리 설명하기

Apache Calcite가 활성화되면(plugins.calcite.enabled가 true로 설정), explain은 v3 엔진에서 논리적 및 물리적 계획과 push-down 정보를 가져와요.

explain source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

이 쿼리는 다음과 같은 결과를 반환해요.

{
  "calcite": {
    "logical": "\"\"\"LogicalProject(count()=[$1], country=[$0])
  LogicalAggregate(group=[{1}], count()=[COUNT()])
    LogicalFilter(condition=[SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7))])
      CalciteLogicalIndexScan(table=[[OpenSearch, state_country]])
\"\"\",
    "physical": "\"\"\"EnumerableCalc(expr#0..1=[{inputs}], count()=[$t1], country=[$t0])
  CalciteEnumerableIndexScan(table=[[OpenSearch, state_country]], PushDownContext=[[FILTER->SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7)), AGGREGATION->rel#53:LogicalAggregate.NONE.[](https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/input=RelSubset#43,group={1},count()=COUNT())], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"size\":0,\"timeout\":\"1m\",\"query\":{\"terms\":{\"country\":[\"England\",\"USA\"],\"boost\":1.0}},\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":1000,\"sources\":[{\"country\":{\"terms\":{\"field\":\"country\",\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]},\"aggregations\":{\"count()\":{\"value_count\":{\"field\":\"_index\"}}}}}}, requestedTotalSize=2147483647, pageSize=null, startFrom=0)])
\"\"\"
  }
}

예시 3: simple 모드에서 PPL 쿼리 설명하기

다음 쿼리는 simple 모드에서 explain 명령을 사용해 단순화된 논리적 계획 트리를 보여줘요.

explain simple source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

이 쿼리는 다음과 같은 결과를 반환해요.

{
  "calcite": {
    "logical": "\"\"\"LogicalProject
  LogicalAggregate
    LogicalFilter
      CalciteLogicalIndexScan
\"\"\"
  }
}

예시 4: cost 모드에서 PPL 쿼리 설명하기

다음 쿼리는 cost 모드에서 explain 명령을 사용해 계획 비용 속성을 보여줘요.

explain cost source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

이 쿼리는 다음과 같은 결과를 반환해요.

{
  "calcite": {
    "logical": "\"\"\"LogicalProject(count()=[$1], country=[$0]): rowcount = 2.5, cumulative cost = {130.3125 rows, 206.0 cpu, 0.0 io}, id = 75
  LogicalAggregate(group=[{1}], count()=[COUNT()]): rowcount = 2.5, cumulative cost = {127.8125 rows, 201.0 cpu, 0.0 io}, id = 74
    LogicalFilter(condition=[SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7))]): rowcount = 25.0, cumulative cost = {125.0 rows, 201.0 cpu, 0.0 io}, id = 73
      CalciteLogicalIndexScan(table=[[OpenSearch, state_country]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 72
\"\"\",
    "physical": "\"\"\"EnumerableCalc(expr#0..1=[{inputs}], count()=[$t1], country=[$t0]): rowcount = 100.0, cumulative cost = {200.0 rows, 501.0 cpu, 0.0 io}, id = 138
  CalciteEnumerableIndexScan(table=[[OpenSearch, state_country]], PushDownContext=[[FILTER->SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7)), AGGREGATION->rel#125:LogicalAggregate.NONE.[](https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/input=RelSubset#115,group={1},count()=COUNT())], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"size\":0,\"timeout\":\"1m\",\"query\":{\"terms\":{\"country\":[\"England\",\"USA\"],\"boost\":1.0}},\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":1000,\"sources\":[{\"country\":{\"terms\":{\"field\":\"country\",\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]},\"aggregations\":{\"count()\":{\"value_count\":{\"field\":\"_index\"}}}}}}, requestedTotalSize=2147483647, pageSize=null, startFrom=0)]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 133
\"\"\"
  }
}

예시 5: extended 모드에서 PPL 쿼리 설명하기

다음 쿼리는 extended 모드에서 explain 명령을 사용해 생성된 코드를 보여줘요.

explain extended source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

이 쿼리는 다음과 같은 결과를 반환해요.

{
  "calcite": {
    "logical": "\"\"\"LogicalProject(count()=[$1], country=[$0])
  LogicalAggregate(group=[{1}], count()=[COUNT()])
    LogicalFilter(condition=[SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7))])
      CalciteLogicalIndexScan(table=[[OpenSearch, state_country]])
\"\"\",
    "physical": "\"\"\"EnumerableCalc(expr#0..1=[{inputs}], count()=[$t1], country=[$t0])
  CalciteEnumerableIndexScan(table=[[OpenSearch, state_country]], PushDownContext=[[FILTER->SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7)), AGGREGATION->rel#193:LogicalAggregate.NONE.[](https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/input=RelSubset#183,group={1},count()=COUNT())], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"size\":0,\"timeout\":\"1m\",\"query\":{\"terms\":{\"country\":[\"England\",\"USA\"],\"boost\":1.0}},\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":1000,\"sources\":[{\"country\":{\"terms\":{\"field\":\"country\",\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]},\"aggregations\":{\"count()\":{\"value_count\":{\"field\":\"_index\"}}}}}}, requestedTotalSize=2147483647, pageSize=null, startFrom=0)])
\"\"\",
    "extended": "\"\"\"public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root) {
  final org.opensearch.sql.opensearch.storage.scan.CalciteEnumerableIndexScan v1stashed = (org.opensearch.sql.opensearch.storage.scan.CalciteEnumerableIndexScan) root.get(\"v1stashed\");
  final org.apache.calcite.linq4j.Enumerable _inputEnumerable = v1stashed.scan();
  return new org.apache.calcite.linq4j.AbstractEnumerable(){
      public org.apache.calcite.linq4j.Enumerator enumerator() {
        return new org.apache.calcite.linq4j.Enumerator(){
            public final org.apache.calcite.linq4j.Enumerator inputEnumerator = _inputEnumerable.enumerator();
            public void reset() {
              inputEnumerator.reset();
            }
            public boolean moveNext() {
              return inputEnumerator.moveNext();
            }
            public void close() {
              inputEnumerator.close();
            }
            public Object current() {
              final Object[] current = (Object[]) inputEnumerator.current();
              final Object input_value = current[1];
              final Object input_value0 = current[0];
              return new Object[] {
                  input_value,
                  input_value0};
            }
          };
      }
    };
}
public Class getElementType() {
  return java.lang.Object[].class;
}
\"\"\"
  }
}

더 알아보기 (Learn more)

• WHERE 명령
• stats 명령
• PPL 개요