이 페이지는 Cloud Translation API를 통해 번역되었습니다.

쿼리 계획 패치 만들기 및 관리

이 페이지에서는 PostgreSQL용 AlloyDB에서 쿼리 계획 패치를 만들고 관리하는 방법을 설명합니다.

쿼리 계획 패치는 쿼리와 힌트 집합 간의 연결로, 쿼리 계획의 세부정보를 지정할 수 있습니다. 힌트는 쿼리에 대해 선호하는 최종 실행 계획에 관한 추가 정보를 지정합니다. 예를 들어 쿼리에서 테이블을 스캔할 때는 순차적 스캔과 같은 다른 유형의 스캔 대신 색인 스캔을 사용합니다.

힌트 사양 내에서 최종 계획 선택을 제한하기 위해 쿼리 플래너는 실행 계획을 생성하는 동안 먼저 쿼리에 힌트를 적용합니다. 그런 다음 쿼리가 이후에 실행될 때마다 힌트가 자동으로 적용됩니다. 이 방법을 사용하면 플래너에서 다른 쿼리 계획을 강제할 수 있습니다. 예를 들어 힌트를 사용하여 특정 테이블에서 색인 스캔을 강제하거나 여러 테이블 간에 특정 조인 순서를 강제할 수 있습니다.

AlloyDB 쿼리 계획 패치는 오픈소스 pg_hint_plan 확장 프로그램의 모든 힌트를 지원합니다.

또한 AlloyDB는 열 형식 엔진에 다음 힌트를 지원합니다.

ColumnarScan(table): 테이블에서 열 형식 스캔을 강제합니다.
NoColumnarScan(table): 테이블에서 열 형식 스캔을 사용 중지합니다.

AlloyDB를 사용하면 파라미터화된 쿼리와 파라미터화되지 않은 쿼리 모두에 대한 계획 패치를 만들 수 있습니다. 이 페이지에서는 매개변수화되지 않은 쿼리를 매개변수 민감한 쿼리라고 합니다.

워크플로

쿼리 계획 패치를 사용하는 방법은 다음과 같습니다.

계획 패치를 만들 쿼리를 식별합니다.
쿼리가 다음에 실행될 때 적용할 힌트가 포함된 계획 패치를 만듭니다.
계획 패치 적용 확인

이 페이지에서는 다음 표와 색인을 예로 사용합니다.

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

이전 버전을 사용하여 만든 쿼리 계획 패치를 계속 사용하려면 이 페이지의 안내에 따라 다시 만드세요.

시작하기 전에

인스턴스에서 쿼리 계획 패치 기능을 사용 설정합니다. alloydb.enable_query_plan_patch 플래그를 on로 설정합니다. 이 플래그는 서버 전체 수준 또는 세션 수준에서 사용 설정할 수 있습니다. 이 기능을 사용하여 발생할 수 있는 오버헤드를 최소화하려면 세션 수준에서만 이 플래그를 사용 설정하세요.

자세한 내용은 인스턴스의 데이터베이스 플래그 구성을 참고하세요.

참고: 읽기 풀 인스턴스에서 동일한 쿼리 계획을 강제 적용하려면 읽기 풀 인스턴스에서 쿼리 계획 패치 기능을 사용 설정하세요.

플래그가 사용 설정되었는지 확인하려면 show alloydb.enable_query_plan_patch; 명령어를 실행합니다. 플래그가 사용 설정되면 출력에 'on'이 반환됩니다.
쿼리 계획 패치를 사용하려는 각 데이터베이스에서 AlloyDB 기본 인스턴스의 데이터베이스에 alloydbsuperuser 또는 postgres 사용자로 확장 프로그램을 만듭니다.
```
CREATE EXTENSION google_auto_hints CASCADE;
```

필요한 역할

쿼리 계획 패치를 만들고 관리하는 데 필요한 권한을 얻으려면 관리자에게 다음 Identity and Access Management (IAM) 역할을 부여해 달라고 요청하세요.

alloydbsuperuser 역할

기본 권한은 alloydbsuperuser 역할이 있는 사용자만 계획 패치를 만들 수 있도록 허용하지만, 다른 데이터베이스 사용자나 역할이 계획 패치를 만들 수 있도록 쓰기 권한을 부여할 수도 있습니다.

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

쿼리 식별

쿼리 ID를 사용하여 기본 계획을 조정해야 하는 쿼리를 식별할 수 있습니다. 쿼리 ID는 쿼리가 한 번 이상 실행된 후에 사용할 수 있습니다.

다음 방법을 사용하여 쿼리 ID를 식별합니다.

다음 예시와 같이 EXPLAIN (VERBOSE) 명령어를 실행하세요.

EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

출력에서 쿼리 ID는 -6875839275481643436입니다.

pg_stat_statements 뷰를 쿼리합니다.

pg_stat_statements 확장 프로그램을 사용 설정한 경우 다음 예와 같이 pg_stat_statements 뷰를 쿼리하여 쿼리 ID를 찾을 수 있습니다.
```
select query, queryid from pg_stat_statements;
```

쿼리 계획 패치 만들기

쿼리 계획 패치를 만들려면 google_create_plan_patch() 함수를 사용하세요. 이 함수는 쿼리와 데이터베이스의 힌트 간의 연결을 만듭니다.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

다음을 바꿉니다.

PLAN_PATCH_NAME: 계획 패치의 이름입니다. 이 값은 데이터베이스 내에서 고유해야 합니다.
SQL_ID (선택사항): 계획 패치를 만들 쿼리의 쿼리 ID입니다.

쿼리 ID 또는 쿼리 텍스트(SQL_TEXT 매개변수)를 사용하여 계획 패치를 만들 수 있습니다. 하지만 AlloyDB는 쿼리 ID를 기반으로 정규화된 쿼리 텍스트를 자동으로 찾으므로 쿼리 ID를 사용하여 계획 패치를 만드는 것이 좋습니다.
SQL_TEXT (선택사항): 계획 패치를 생성할 쿼리의 쿼리 텍스트입니다.

쿼리 텍스트를 사용하는 경우 텍스트는 쿼리의 리터럴 및 상수 값을 제외하고 의도한 쿼리와 동일해야 합니다. 대소문자 차이를 비롯한 불일치가 있으면 계획 패치가 적용되지 않을 수 있습니다. 리터럴 및 상수가 포함된 쿼리의 계획 패치를 만드는 방법을 알아보려면 매개변수 민감형 쿼리 계획 패치 만들기를 참고하세요.
APPLICATION_NAME (선택사항): 계획 패치를 사용할 세션 클라이언트 애플리케이션의 이름입니다. 빈 문자열을 사용하면 쿼리를 실행하는 클라이언트 애플리케이션과 관계없이 쿼리에 계획 패치를 적용할 수 있습니다.
HINTS: 쿼리에 대한 힌트의 공백으로 구분된 목록입니다.
DISABLED (선택사항): BOOL. TRUE는 처음에는 요금제 패치를 사용 중지된 상태로 만듭니다.

예:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

이 쿼리는 my_hint1이라는 계획 패치를 만듭니다. 이 예시 쿼리의 다음 실행에서 테이블 t에 대한 색인 스캔을 강제 적용하기 위해 플래너가 힌트 IndexScan(t)를 적용합니다.

계획 패치를 만든 후 다음 예와 같이 google_query_plan_patch_view를 사용하여 계획 패치가 생성되었는지 확인할 수 있습니다.

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

기본 인스턴스에서 계획 패치가 생성되면 읽기 풀 인스턴스에서도 쿼리 계획 패치 기능을 사용 설정한 경우 읽기 풀 인스턴스의 연결된 쿼리에 자동으로 적용됩니다.

매개변수에 민감한 쿼리 계획 패치 만들기

기본적으로 쿼리에 대한 계획 패치가 생성되면 연결된 쿼리 텍스트가 쿼리 텍스트의 리터럴 및 상수 값을 ?와 같은 매개변수 마커로 대체하여 정규화됩니다. 그런 다음 계획 패치는 매개변수 마커의 값이 다른 경우에도 정규화된 쿼리에 사용됩니다.

예를 들어 다음 쿼리를 실행하면 SELECT * FROM t WHERE a = 99;와 같은 다른 쿼리에서 기본적으로 계획 패치 my_hint2을 사용할 수 있습니다.

SELECT google_create_plan_patch(
  PLAN_PATCH_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

그러면 SELECT * FROM t WHERE a = 99;와 같은 쿼리에서 기본적으로 계획 패치 my_hint2을 사용할 수 있습니다.

또한 AlloyDB를 사용하면 매개변수화되지 않은 쿼리 텍스트의 계획 패치를 만들 수 있습니다. 이 경우 쿼리를 일치시킬 때 쿼리 텍스트의 각 리터럴 및 상수 값이 중요합니다.

매개변수에 민감한 계획 패치를 적용하면 해당 리터럴 또는 상수 값만 다른 두 쿼리도 서로 다른 것으로 간주됩니다. 두 쿼리 모두에 계획을 적용하려면 각 쿼리에 대해 별도의 계획 패치를 만들어야 합니다. 하지만 두 계획 패치에 서로 다른 힌트를 사용할 수 있습니다.

매개변수에 민감한 계획 패치를 만들려면 다음 예와 같이 google_create_plan_patch() 함수의 SENSITIVE_TO_PARAM 매개변수를 TRUE로 설정합니다.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

리터럴 값 '99'가 '88'과 일치하지 않으므로 쿼리 SELECT * FROM t WHERE a = 99;는 계획 패치 my_hint3를 사용할 수 없습니다.

매개변수 민감 계획 패치를 사용하는 경우 다음 사항을 고려하세요.

매개변수에 민감한 계획 패치는 쿼리 텍스트에서 리터럴 값과 상수 값, 매개변수 마커의 혼합을 지원하지 않습니다.
동일한 쿼리에 대해 매개변수 민감 계획 패치와 기본 계획 패치를 만드는 경우 기본 패치보다 매개변수 민감 계획 패치가 우선합니다.
쿼리 ID를 사용하여 매개변수에 민감한 계획 패치를 만들려면 현재 세션에서 쿼리가 실행되었는지 확인하세요. 최근 실행 (현재 세션)의 매개변수 값이 계획 패치를 만드는 데 사용됩니다.

쿼리 계획 패치 적용 확인

계획 패치를 만든 후 다음 방법을 사용하여 쿼리 계획이 적절하게 강제 적용되었는지 확인합니다.

EXPLAIN 명령어 또는 EXPLAIN (ANALYZE) 명령어를 사용합니다.

계획자가 적용하려고 하는 힌트를 보려면 EXPLAIN 명령어를 실행하기 전에 세션 수준에서 다음 플래그를 설정하면 됩니다.
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
auto_explain 확장 프로그램을 사용합니다.

쿼리 계획 패치 관리

AlloyDB를 사용하면 쿼리 계획 패치를 보고, 사용 설정 및 중지하고, 삭제할 수 있습니다.

쿼리 계획 패치 보기

기존 계획 패치를 보려면 다음 예와 같이 google_query_plan_patch_view 함수를 사용합니다.

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

쿼리 계획 패치 사용 설정

기존 요금제 패치를 사용 설정하려면 google_enable_plan_patch(PLAN_PATCH_NAME) 함수를 사용합니다. 기본적으로 계획 패치는 생성 시 사용 설정됩니다.

예를 들어 데이터베이스에서 이전에 사용 중지된 계획 패치 my_hint1를 다시 사용 설정하려면 다음 함수를 실행합니다.

SELECT google_enable_plan_patch('my_hint1');

쿼리 계획 패치 사용 중지

기존 계획 패치를 사용 중지하려면 google_disable_plan_patch(PLAN_PATCH_NAME) 함수를 사용합니다.

예를 들어 데이터베이스에서 예시 계획 패치 my_hint1를 삭제하려면 다음 함수를 실행합니다.

SELECT google_disable_plan_patch('my_hint1');

쿼리 계획 패치 삭제

계획 패치를 삭제하려면 google_delete_plan_patch(PLAN_PATCH_NAME) 함수를 사용합니다.