AI를 사용하여 SQL 쿼리 조정

PostgreSQL용 AlloyDB 데이터베이스 쿼리 성능을 최적화하려면 AI 기반 인사이트를 사용하여 SQL 쿼리를 조정하면 됩니다. 대규모 언어 모델(LLM)을 기반으로 하는 ai.tune_sql 함수는 쿼리 실행 계획을 분석하여 복잡한 병목 현상을 식별하고 최적화를 위해 실행할 수 있는 권장사항을 제공합니다.

ai.tune_sql 함수를 호출하면 쿼리를 분석하고 다음 영역에서 쿼리 통계 및 권장사항을 제공합니다.

  • 쿼리 요약: 쿼리 성능 병목 현상을 분석합니다.
  • 쿼리 재작성: 원래 쿼리를 동일하고 더 효율적인 쿼리로 재작성하는 최적화 기법을 제안합니다.
  • 색인 추천: 표현식의 색인, 일반화된 역색인 (GIN) 색인, 확장 가능한 최근접 이웃 (ScaNN) 색인과 같은 색인 유형을 추천합니다.
  • 통계 수집 권장사항: 쿼리 실행 계획을 기반으로 여러 열에 대한 테이블 ANALYZE 및 통계 수집을 권장합니다.
  • GUC (Grand Unified Configuration) 플래그 조정: 실행 계획과 메모리, 버퍼 사용량, 크기와 같은 시스템 정보를 분석하여 새로운 GUC 값을 추천합니다.
  • 분석 및 진공: 분석 및 진공 기록과 라이브 및 데드 튜플 수를 기반으로 테이블에서 ANALYZE 및 VACUUM를 실행하는 것이 좋습니다.

제한사항

SQL 쿼리 조정에는 다음과 같은 제한사항이 있습니다.

  • 준비된 문 및 파라미터화된 문: 준비된 문 및 파라미터화된 문의 경우 ai.tune_sql는 쿼리 재작성 권장사항을 제공하지 않지만 다른 조정 권장사항은 계속 제공합니다. 쿼리 통계 또는 pg_stat_statements에서 복사한 $1 또는 $2와 같은 파라미터 자리표시자가 포함된 쿼리에 대한 쿼리 재작성 추천을 받으려면 ai.tune_sql를 실행하기 전에 자리표시자를 대표 리터럴 값으로 바꿉니다.
  • explain_analyze가 TRUE인 경우에만 읽기 전용 SELECT 문: explain_analyze가 TRUE로 설정된 경우 단일 읽기 전용 SELECT 문만 지원됩니다. INSERT, UPDATE, DELETE, SELECT INTO 또는 SELECT FOR UPDATE와 같은 다른 문에서 explain_analyze를 TRUE로 설정하여 ai.tune_sql를 실행하면 오류 메시지가 반환됩니다.

시작하기 전에

다음 항목이 있는지 확인합니다.

SQL 조정 사용 설정

LLM을 등록하고 필수 데이터베이스 플래그를 사용 설정합니다.

  1. 인스턴스 리전에 전역 모델 엔드포인트 또는 리전 모델 엔드포인트를 등록하여 ai.tune_sql 함수에 대한 Gemini 모델 엔드포인트를 만듭니다.

    • 인스턴스 리전에서 모델을 사용할 수 없는 경우(예: locations/global의 gemini-3.5-flash) 전역 모델 엔드포인트를 등록하려면 다음 문을 실행합니다.

      CALL google_ml.create_model(
  model_id => 'gemini-3.5-flash-global',
  model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
  model_qualified_name => 'gemini-3.5-flash',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      PROJECT_ID를 모델을 사용할 수 있는 프로젝트의 ID로 바꿉니다.

    • 인스턴스 리전(예: locations/REGION_ID의 gemini-2.5-pro)에 리전 모델 엔드포인트를 등록하려면 다음 문을 실행합니다.

      CALL google_ml.create_model(
  model_id => 'gemini-2.5-pro',
  model_request_url => 'https://REGION_ID-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/REGION_ID/publishers/google/models/gemini-2.5-pro:generateContent',
  model_qualified_name => 'gemini-2.5-pro',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      다음을 바꿉니다.

      • REGION_ID: AlloyDB 인스턴스의 리전(예: us-central1)
      • PROJECT_ID: 모델을 사용할 수 있는 프로젝트의 ID입니다.

  2. 현재 데이터베이스 세션에서 SQL 쿼리 조정 추천의 모델 ID를 설정합니다. 이전 단계에서 등록한 model_id를 지정합니다(예: 'gemini-3.5-flash-global' 또는 'gemini-2.5-pro').

    SET google_db_advisor.llm_model_id = 'gemini-3.5-flash-global';

  3. google_db_advisor.enable_ai_tune_sql 플래그를 설정하여 현재 데이터베이스 세션에 SQL 쿼리 조정을 사용 설정합니다.

    SET google_db_advisor.enable_ai_tune_sql = on;

    SET는 현재 데이터베이스 세션에만 적용되므로 두 플래그 모두에 대해 ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE;를 실행하거나 인스턴스의 데이터베이스 플래그를 구성하여 세션 간에 이러한 설정을 유지할 수 있습니다.

SQL 쿼리 수동 조정

SQL 쿼리를 수동으로 조정하려면 SELECT 문을 사용하여 ai.tune_sql 함수를 실행합니다.

  1. psql 클라이언트를 AlloyDB 인스턴스에 연결하거나 AlloyDB Studio를 엽니다.

    SET를 사용하여 google_db_advisor.llm_model_id 및 google_db_advisor.enable_ai_tune_sql를 구성한 경우 ai.tune_sql를 호출하기 전에 동일한 데이터베이스 세션에서 해당 SET 문을 실행합니다.

  2. SQL 쿼리를 조정하려면 ai.tune_sql 함수를 실행합니다. 이 함수는 쿼리 관련 스키마를 검색하고 쿼리와 함께 성능 분석을 위해 LLM에 전송합니다.

    SELECT ai.tune_sql(query => 'SQL_QUERY', explain_analyze => TRUE);

    SQL_QUERY를 분석하려는 SQL 쿼리로 바꿉니다.

    기본적으로 실행 매개변수 (explain_analyze)는 TRUE로 설정됩니다. TRUE로 설정하면 함수가 EXPLAIN ANALYZE를 실행하여 쿼리를 실행하고 실행 계획을 캡처합니다. 이 접근 방식은 보다 정확하고 유용한 쿼리 조정 조언을 제공합니다. explain_analyze를 FALSE로 설정하면 함수는 쿼리를 실행하지 않고 계획을 생성하기 위해 EXPLAIN만 실행합니다.

성능 권장사항이 포함된 쿼리 계획 보기

출력에는 먼저 표준 쿼리 계획이 표시되고 그 뒤에 AI 생성 추천이 자세히 표시됩니다. 추천에는 쿼리 요약, 추천 재작성, 통계 수집, GUC 플래그 조정, 비우기에 대한 추천이 포함됩니다. ai.tune_sql 함수가 추천을 생성하는 데 2~4분이 걸릴 수 있습니다.

다음 예시에서는 안티 조인 쿼리의 ai.tune_sql 출력을 보여줍니다.

SELECT ai.tune_sql(
  'SELECT *
   FROM store_sales
   WHERE ss_customer_sk NOT IN (
     SELECT sr_customer_sk
     FROM store_returns
     WHERE sr_item_sk = ss_item_sk)');

                     tune_sql
--------------------------------------------------
 NOTE
 --------------------------------------------------
 Advices generated with the assistance of an LLM. Independent verification of these advices is required due to the potential for inaccuracies.
 --------------------------------------------------
 ORIGINAL QUERY PLAN
 --------------------------------------------------
 Seq Scan on store_sales  (cost=0.00..111292742.52 rows=1440384 width=115) (actual time=0.092..103093.207 rows=1306312.00 loops=1)
   Filter: (NOT (ANY (ss_customer_sk = (SubPlan 1).col1)))
   Rows Removed by Filter: 1574198
   Buffers: shared hit=72987539
   SubPlan 1
     ->  Index Scan using idx_sr_item_sk on store_returns  (cost=0.42..76.74 rows=18 width=4) (actual time=0.005..0.034 rows=19.38 loops=2880510)
           Index Cond: (sr_item_sk = store_sales.ss_item_sk)
           Index Searches: 2880510
           Buffers: shared hit=72933670
 Planning Time: 0.257 ms
 --------------------------------------------------
 QUERY SUMMARY
 --------------------------------------------------
 The original query plan shows a Sequential Scan on store_sales with a correlated SubPlan for the NOT IN condition. For each of the 1.44 million rows in store_sales, it executes an Index Scan on store_returns, resulting in over 2.88 million index searches. This nested loop execution is the primary bottleneck, taking over 103 seconds. Converting the correlated NOT IN subquery to an anti-join (NOT EXISTS) will allow the optimizer to use a Hash Anti Join, processing both tables in bulk and significantly reducing execution time.
 --------------------------------------------------
 AI SUGGESTED QUERY 1
 --------------------------------------------------
 SELECT * FROM store_sales WHERE NOT EXISTS (SELECT 1 FROM store_returns WHERE store_returns.sr_item_sk = store_sales.ss_item_sk AND (store_returns.sr_customer_sk = store_sales.ss_customer_sk OR store_returns.sr_customer_sk IS NULL OR store_sales.ss_customer_sk IS NULL));
 Reason: The original query uses a NOT IN subquery which is executed as a correlated subquery (SubPlan), leading to millions of index scans. Rewriting to NOT EXISTS allows the optimizer to use a Hash Anti Join, which is much more efficient for large datasets. The OR IS NULL conditions ensure strict semantic equivalence with NOT IN when dealing with NULL values.
 --------------------------------------------------
 REWRITTEN QUERY PLAN
 --------------------------------------------------
 Gather  (cost=8541.96..139088.40 rows=154194 width=115)
   Workers Planned: 2
   ->  Parallel Hash Anti Join  (cost=7541.96..122669.00 rows=64248 width=115)
         Hash Cond: (store_sales.ss_item_sk = store_returns.sr_item_sk)
         Join Filter: ((store_returns.sr_customer_sk = store_sales.ss_customer_sk) OR (store_returns.sr_customer_sk IS NULL) OR (store_sales.ss_customer_sk IS NULL))
         ->  Parallel Seq Scan on store_sales  (cost=0.00..65872.20 rows=1200320 width=115)
         ->  Parallel Hash  (cost=6044.76..6044.76 rows=119776 width=8)
               ->  Parallel Seq Scan on store_returns  (cost=0.00..6044.76 rows=119776 width=8)
 --------------------------------------------------
 AI SUGGESTED INDEX 1
 --------------------------------------------------
 CREATE INDEX idx_sr_item_customer ON store_returns (sr_item_sk, sr_customer_sk);
 Reason: A composite index on (sr_item_sk, sr_customer_sk) allows index-only scans for the subquery or early aggregation, reducing table access and speeding up the anti-join condition evaluation.
 Note: Indexes are suggested solely based on the given query, but they don't consider the overall system workload.
           The suggested indexes improve the performance of the original query.
 --------------------------------------------------
 GUC FLAGS TUNING
 --------------------------------------------------
 SET work_mem = '64MB'; The current work_mem of 4MB is too small for a Hash Anti Join on tables with hundreds of thousands of rows. Increasing it to 64MB allows the hash table for store_returns to fit entirely in memory, preventing costly disk spills during the join.

 Execution Time: 103167.475 ms
(50 rows)

다음 단계