SQL-Abfragen mithilfe von KI optimieren

Wenn Sie die Abfrageleistung von AlloyDB for PostgreSQL-Datenbanken optimieren möchten, können Sie Ihre SQL-Abfragen mithilfe von KI-basierten Statistiken optimieren. Durch die Analyse Ihrer Abfrageausführungspläne identifiziert die Funktion ai.tune_sql, die auf einem Large Language Model (LLM) basiert, komplexe Engpässe und bietet Empfehlungen, die Sie zur Optimierung umsetzen können.

Wenn Sie die Funktion ai.tune_sql aufrufen, wird Ihre Abfrage analysiert und es werden Query Insights und Empfehlungen in den folgenden Bereichen bereitgestellt:

  • Abfragezusammenfassung: Analysiert Leistungsengpässe bei Abfragen.
  • Abfrageumschreibung: Schlägt Optimierungstechniken vor, um die ursprüngliche Abfrage in eine gleichwertige, effizientere Abfrage umzuschreiben.
  • Indexempfehlungen: Empfiehlt Indextypen wie Indexe für Ausdrücke, GIN-Indexe (Generalized Inverted Index) und ScaNN-Indexe (Scalable Nearest Neighbors).
  • Empfehlungen zur Statistikerfassung: Empfiehlt die Tabelle ANALYZE und die Statistikerfassung für mehrere Spalten basierend auf dem Abfrageausführungsplan.
  • Optimierung von GUC-Flags (Grand Unified Configuration): Empfiehlt neue GUC-Werte, indem der Ausführungsplan und Systeminformationen wie Arbeitsspeicher- und Puffernutzung und -größe analysiert werden.
  • Analysieren und bereinigen: Empfiehlt, ANALYZE und VACUUM für Tabellen auszuführen, basierend auf dem Analyse- und Bereinigungsverlauf sowie der Anzahl der Live- und Dead-Tupel.

Beschränkungen

Für die SQL-Abfrageoptimierung gelten die folgenden Einschränkungen:

  • Vorbereitete und parametrisierte Anweisungen: Für vorbereitete und parametrisierte Anweisungen bietet ai.tune_sql keine Empfehlungen zum Umschreiben von Abfragen, aber andere Empfehlungen zur Optimierung. Wenn Sie Empfehlungen zum Umschreiben von Abfragen für eine Abfrage erhalten möchten, die Parameterplatzhalter enthält, z. B. $1 oder $2, die aus Query Insights oder pg_stat_statements kopiert wurden, ersetzen Sie die Platzhalter durch repräsentative Literalwerte, bevor Sie ai.tune_sql ausführen.
  • Nur schreibgeschützte SELECT-Anweisungen, wenn explain_analyze auf TRUE gesetzt ist: Es werden nur einzelne, schreibgeschützte SELECT-Anweisungen unterstützt, wenn explain_analyze auf TRUE gesetzt ist. Wenn Sie ai.tune_sql mit explain_analyze auf TRUE für andere Anweisungen wie INSERT, UPDATE, DELETE, SELECT INTO oder SELECT FOR UPDATE ausführen, wird eine Fehlermeldung zurückgegeben.

Hinweis

Prüfen Sie, ob Folgendes vorhanden ist:

SQL-Abstimmung aktivieren

Registrieren Sie ein LLM und aktivieren Sie die erforderlichen Datenbank-Flags.

  1. Erstellen Sie einen Gemini-Modellendpunkt für die Funktion ai.tune_sql, indem Sie entweder einen globalen Modellendpunkt oder einen regionalen Modellendpunkt in der Region Ihrer Instanz registrieren:

    • Wenn Sie einen globalen Modellendpunkt registrieren möchten, wenn das Modell in der Region Ihrer Instanz nicht verfügbar ist (z. B. gemini-3.5-flash auf locations/global), führen Sie die folgende Anweisung aus:

      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');

      Ersetzen Sie PROJECT_ID durch die ID des Projekts, in dem das Modell verfügbar ist.

    • Führen Sie die folgende Anweisung aus, um einen regionalen Modellendpunkt in Ihrer Instanzregion zu registrieren, z. B. gemini-2.5-pro in locations/REGION_ID:

      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');

      Ersetzen Sie Folgendes:

      • REGION_ID: Die Region Ihrer AlloyDB-Instanz, z. B. us-central1.
      • PROJECT_ID: die ID des Projekts, in dem das Modell verfügbar ist.

  2. Legen Sie die Modell-ID für Empfehlungen zur SQL-Abfrageoptimierung in Ihrer aktuellen Datenbanksitzung fest. Geben Sie die model_id an, die Sie im vorherigen Schritt registriert haben, z. B. 'gemini-3.5-flash-global' oder 'gemini-2.5-pro':

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

  3. Aktivieren Sie die SQL-Abfrageoptimierung für Ihre aktuelle Datenbanksitzung, indem Sie das Flag google_db_advisor.enable_ai_tune_sql festlegen:

    SET google_db_advisor.enable_ai_tune_sql = on;

    Da SET nur für die aktuelle Datenbanksitzung gilt, können Sie diese Einstellungen sitzungsübergreifend persistent speichern, indem Sie ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE; für beide Flags ausführen oder die Datenbank-Flags der Instanz konfigurieren.

SQL-Abfragen manuell optimieren

Wenn Sie SQL-Abfragen manuell optimieren möchten, führen Sie die Funktion ai.tune_sql mit einer SELECT-Anweisung aus.

  1. Verbinden Sie einen psql-Client mit einer AlloyDB-Instanz oder öffnen Sie AlloyDB Studio.

    Wenn Sie SET zum Konfigurieren von google_db_advisor.llm_model_id und google_db_advisor.enable_ai_tune_sql verwendet haben, führen Sie diese SET-Anweisungen in derselben Datenbanksitzung aus, bevor Sie ai.tune_sql aufrufen.

  2. Führen Sie die Funktion ai.tune_sql aus, um SQL-Abfragen zu optimieren. Diese Funktion ruft das abfragebezogene Schema ab und sendet es zusammen mit der Abfrage zur Leistungsanalyse an das LLM:

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

    Ersetzen Sie SQL_QUERY durch die SQL-Abfrage, die Sie analysieren möchten.

    Standardmäßig ist der Ausführungsparameter (explain_analyze) auf TRUE festgelegt. Wenn der Wert auf TRUE gesetzt ist, führt die Funktion EXPLAIN ANALYZE aus, um die Abfrage auszuführen und den Ausführungsplan zu erfassen. Dieser Ansatz liefert genauere und nützlichere Hinweise zur Abfrageoptimierung. Wenn Sie explain_analyze auf FALSE setzen, führt die Funktion nur EXPLAIN aus, um einen Plan zu generieren, ohne die Abfrage auszuführen.

Abfragepläne mit Leistungsempfehlungen ansehen

In der Ausgabe wird zuerst der Standardabfrageplan angezeigt, gefolgt von den detaillierten KI-generierten Empfehlungen. Die Empfehlungen umfassen eine Abfragezusammenfassung, vorgeschlagene Umschreibungen und Empfehlungen für die Erfassung von Statistiken, die Optimierung von GUC-Flags und das Vacuuming. Die Funktion ai.tune_sql kann zwei bis vier Minuten dauern, um Empfehlungen zu generieren.

Das folgende Beispiel zeigt die ai.tune_sql-Ausgabe für eine Anti-Join-Abfrage:

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)

Nächste Schritte