이름이 지정된 힌트 만들기 및 관리

이 페이지에서는 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_named_hints 플래그를 on으로 설정합니다. 이 플래그는 서버 전체 수준 또는 세션 수준에서 사용 설정할 수 있습니다. 이 기능을 사용하여 발생할 수 있는 오버헤드를 최소화하려면 세션 수준에서만 이 플래그를 사용 설정하세요.

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

**참고:** 읽기 풀 인스턴스에서 동일한 쿼리 계획을 강제 적용하려면 읽기 풀 인스턴스에서 이름이 지정된 힌트 기능을 사용 설정하세요.

플래그가 사용 설정되어 있는지 확인하려면 show alloydb.enable_named_hints; 명령어를 실행합니다. 플래그가 사용 설정되면 출력에서 "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_named_hints() 함수를 사용합니다.

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

다음을 바꿉니다.

HINTS_NAME: 이름이 지정된 힌트의 이름입니다. 데이터베이스 내에서 고유해야 합니다.
SQL_ID (선택사항): 이름이 지정된 힌트를 만들 쿼리의 쿼리 ID입니다.

쿼리 ID 또는 쿼리 텍스트(SQL_TEXT 파라미터)를 사용하여 이름이 지정된 힌트를 만들 수 있습니다. 하지만 AlloyDB는 쿼리 ID를 기반으로 정규화된 쿼리 텍스트를 자동으로 찾으므로 쿼리 ID를 사용하여 이름이 지정된 힌트를 만드는 것이 좋습니다.
SQL_TEXT (선택사항): 이름이 지정된 힌트를 만들 쿼리의 쿼리 텍스트입니다.

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

예:

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

이 쿼리는 my_hint1이라는 이름이 지정된 힌트를 만듭니다. 이 힌트 IndexScan(t)는 플래너에 의해 적용되어 이 예시 쿼리의 다음 실행 에서 테이블 t에 색인 스캔을 강제 적용합니다.

이름이 지정된 힌트를 만든 후 google_named_hints_view을(를) 사용하여 이름이 지정된 힌트가 생성되었는지 확인할 수 있습니다. 다음 예시를 참조하세요.

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_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_named_hints(
  HINTS_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에서 파라미터화되지 않은 쿼리 텍스트의 이름이 지정된 힌트도 만들 수 있습니다. 이 경우 쿼리를 일치시킬 때 쿼리 텍스트의 각 리터럴 및 상수 값이 중요합니다.

파라미터에 민감한 이름이 지정된 힌트를 적용하면 해당 리터럴 또는 상수 값만 다른 두 쿼리도 서로 다른 것으로 간주됩니다. 두 쿼리 모두에 계획을 강제 적용하려면 쿼리마다 별도의 이름이 지정된 힌트를 만들어야 합니다. 하지만 두 이름이 지정된 힌트에 다른 힌트를 사용할 수 있습니다.

파라미터에 민감한 이름이 지정된 힌트를 만들려면 다음 예와 같이 SENSITIVE_TO_PARAM 함수의 google_create_named_hints() 파라미터를 TRUE로 설정합니다.

SELECT google_create_named_hints(
HINTS_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_named_hints_view 함수를 사용합니다.

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

이름이 지정된 힌트 사용 설정

기존 이름이 지정된 힌트를 사용 설정하려면 google_enable_named_hints(HINTS_NAME) 함수를 사용합니다. 기본적으로 이름이 지정된 힌트 는 만들 때 사용 설정됩니다.

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

SELECT google_enable_named_hints('my_hint1');

이름이 지정된 힌트 중지

기존 이름이 지정된 힌트를 중지하려면 google_disable_named_hints(HINTS_NAME) 함수를 사용합니다.

예를 들어 데이터베이스에서 예시 이름이 지정된 힌트 my_hint1을 삭제하려면 다음 함수를 실행합니다.

SELECT google_disable_named_hints('my_hint1');

이름이 지정된 힌트 삭제

이름이 지정된 힌트를 삭제하려면 google_delete_named_hints(HINTS_NAME) 함수를 사용합니다.