Supporto della partizione di tabelle - Amazon Aurora
Configurazione della partizione delle tabelleAcquisizione dei piani per la partizione delle tabelleApplicazione di un piano di partizione delle tabelle Convenzione di denominazione

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Supporto della partizione di tabelle

Aurora Postgre SQL Query Plan Management (QPM) supporta il partizionamento dichiarativo delle tabelle nelle seguenti versioni:

  • 15.3 o versioni successive alla 15

  • 14.8 o versioni successive alla 14

  • 13.11 o versioni successive alla 13

Per ulteriori informazioni, consulta la pagina relativa al partizionamento delle tabelle.

Configurazione della partizione delle tabelle

Per configurare la partizione della tabella in Aurora SQL QPM Postgre, procedi come segue:

  1. Imposta apg_plan_mgmt.plan_hash_version su 3 o un valore maggiore nel gruppo di parametri del cluster database.

  2. Accedi a un database che utilizza la funzionalità di gestione dei piani di query e che contiene voci nella vista apg_plan_mgmt.dba_plans.

  3. Chiama apg_plan_mgmt.validate_plans('update_plan_hash') per aggiornare il valore plan_hash nella tabella dei piani.

  4. Ripeti i passaggi 2-3 per tutti i database con la funzionalità di gestione dei piani di query abilitata contenenti voci nella vista apg_plan_mgmt.dba_plans.

Per ulteriori informazioni su questi parametri, consulta Riferimento ai parametri per la gestione del piano di interrogazione di Aurora SQL Postgre.

Acquisizione dei piani per la partizione delle tabelle

NelQPM, i diversi piani si distinguono per il loro valore. plan_hash Per capire come il valore plan_hash cambia, devi prima familiarizzare con un tipo simile di piani.

La combinazione di metodi di accesso, nomi di indice con rimozione di cifre e nomi di partizione con rimozione di cifre, accumulati a livello di nodo Append deve essere costante affinché i piani siano considerati uguali. Le partizioni specifiche a cui si accede nei piani non sono significative. Nell'esempio seguente, viene creata una tabella tbl_a con 4 partizioni.

postgres=>create table tbl_a(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table tbl_a1 partition of tbl_a for values from (0) to (1000);
CREATE TABLE
postgres=>create table tbl_a2 partition of tbl_a for values from (1001) to (2000);
CREATE TABLE
postgres=>create table tbl_a3 partition of tbl_a for values from (2001) to (3000);
CREATE TABLE
postgres=>create table tbl_a4 partition of tbl_a for values from (3001) to (4000);
CREATE TABLE
postgres=>create index t_i on tbl_a using btree (i);
CREATE INDEX
postgres=>create index t_j on tbl_a using btree (j);
CREATE INDEX
postgres=>create index t_k on tbl_a using btree (k);
CREATE INDEX

I piani seguenti sono considerati uguali perché viene utilizzato un unico metodo di scansione di tbl_a indipendentemente dal numero di partizioni cercate dalla query.

postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 999 and j < 9910 and k > 50;
            
                        QUERY PLAN
-------------------------------------------------------------------
Seq Scan on tbl_a1 tbl_a
    Filter: ((i >= 990) AND (i <= 999) AND (j < 9910) AND (k > 50))
SQL Hash: 1553185667, Plan Hash: -694232056
(3 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                        QUERY PLAN
-------------------------------------------------------------------
Append
    ->  Seq Scan on tbl_a1 tbl_a_1
            Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
    ->  Seq Scan on tbl_a2 tbl_a_2
            Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
    SQL Hash: 1553185667, Plan Hash: -694232056
    (6 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a3 tbl_a_3
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -694232056
(8 rows)

Anche i seguenti 3 piani sono considerati uguali perché, a livello padre, i metodi di accesso, i nomi degli indici con rimozione di cifre e i nomi delle partizioni con rimozione di cifre sono SeqScan tbl_a, IndexScan (i_idx) tbl_a.

postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a2_i_idx on tbl_a2 tbl_a_2
         Index Cond: ((i >= 990) AND (i <= 1100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(7 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a3 tbl_a_3
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993736942
(11 rows)

Indipendentemente dalla diversità a livello di ordine e numero di occorrenze nelle partizioni secondarie, i metodi di accesso, i nomi degli indici con rimozione di cifre e i nomi delle partizioni con rimozione di cifre sono costanti a livello padre per ciascuno dei piani precedenti.

Tuttavia, i piani sarebbero considerati diversi se fosse soddisfatta una delle seguenti condizioni:

  • Nel piano viene utilizzato qualsiasi metodo di accesso aggiuntivo.

    postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
                    
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Bitmap Heap Scan on tbl_a3 tbl_a_3
         Recheck Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a3_i_idx
               Index Cond: ((i >= 990) AND (i <= 2100))
 SQL Hash: 1553185667, Plan Hash: 1134525070
(11 rows)

  • Nessuno dei metodi di accesso del piano non viene più utilizzato.

    postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
                    
                               QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -694232056
(6 rows)

  • L'indice associato a un metodo di indice viene modificato.

    postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
                    
                             QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Seq Scan on tbl_a1 tbl_a_1
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a2_j_idx on tbl_a2 tbl_a_2
         Index Cond: (j < 9910)
         Filter: ((i >= 990) AND (i <= 1100) AND (k > 50))
 SQL Hash: 1553185667, Plan Hash: -993343726
(7 rows)

Applicazione di un piano di partizione delle tabelle

I piani approvati per le tabelle partizionate vengono applicati con la corrispondenza a livello di posizione. I piani non sono specifici delle partizioni e possono essere applicati a partizioni diverse dai piani a cui si fa riferimento nella query originale. I piani possono essere applicati anche per le richieste che accedono a un numero di partizioni diverso rispetto alla struttura originale approvata.

Ad esempio, se la struttura approvata si riferisce al seguente piano:

postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))   
 SQL Hash: 1553185667, Plan Hash: -993736942
(10 rows)

Quindi, questo piano può essere applicato anche SQL alle query che fanno riferimento a 2, 4 o più partizioni. I possibili piani che potrebbero derivare da questi scenari per l'accesso a 2 e 4 partizioni sono:

postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1100 and j < 9910 and k > 50;
            
                                QUERY PLAN
----------------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 1100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1100) AND (j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan. 
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(8 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a4 tbl_a_4
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041 
(12 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 3100 and j < 9910 and k > 50;
            
                                QUERY PLAN
----------------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 3100) AND (j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a3_i_idx on tbl_a3 tbl_a_3
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Index Scan using tbl_a4_i_idx on tbl_a4 tbl_a_4
         Index Cond: ((i >= 990) AND (i <= 3100))
         Filter: ((j < 9910) AND (k > 50))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1553185667, Plan Hash: -993736942, Minimum Cost Plan Hash: -1873216041
(14 rows)

Prendi in considerazione un altro piano approvato con metodi di accesso diversi per ogni partizione:

postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 2100 and j < 9910 and k > 50;
            
                                QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 2100) AND (j < 9910) AND (k > 50))
   ->  Bitmap Heap Scan on tbl_a3 tbl_a_3
         Recheck Cond: ((i >= 990) AND (i <= 2100))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a3_i_idx
               Index Cond: ((i >= 990) AND (i <= 2100))
 SQL Hash: 1553185667, Plan Hash: 2032136998
(12 rows)

In questo caso, qualsiasi piano che esegue letture da due partizioni non viene applicato. A meno che tutte le combinazioni (metodo di accesso, nome di indice) del piano approvato non siano utilizzabili, il piano non può essere applicato. Ad esempio, i seguenti piani hanno hash diversi e il piano approvato non può essere applicato in questi casi:

postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
            
                              QUERY PLAN
-------------------------------------------------------------------------
 Append
   ->  Bitmap Heap Scan on tbl_a1 tbl_a_1
         Recheck Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a1_i_idx
               Index Cond: ((i >= 990) AND (i <= 1900))
   ->  Bitmap Heap Scan on tbl_a2 tbl_a_2
         Recheck Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
         ->  Bitmap Index Scan on tbl_a2_i_idx
               Index Cond: ((i >= 990) AND (i <= 1900))
  Note: This is not an Approved plan.  No usable Approved plan was found.
  SQL Hash: 1553185667, Plan Hash: -568647260
(13 rows)
postgres=>explain (hashes true, costs false) select j, k from tbl_a where i between 990 and 1900 and j < 9910 and k > 50;
            
                              QUERY PLAN
--------------------------------------------------------------------------
 Append
   ->  Index Scan using tbl_a1_i_idx on tbl_a1 tbl_a_1
         Index Cond: ((i >= 990) AND (i <= 1900))
         Filter: ((j < 9910) AND (k > 50))
   ->  Seq Scan on tbl_a2 tbl_a_2
         Filter: ((i >= 990) AND (i <= 1900) AND (j < 9910) AND (k > 50))
 Note: This is not an Approved plan.  No usable Approved plan was found.
 SQL Hash: 1553185667, Plan Hash: -496793743
(8 rows)

Convenzione di denominazione

QPMPer applicare un piano con tabelle partizionate dichiarative, è necessario seguire regole di denominazione specifiche per le tabelle principali, le partizioni di tabella e gli indici:

  • Nomi delle tabelle principali: questi nomi devono differire in base all'alfabeto o ai caratteri speciali e non solo in base alle cifre. Ad esempio, tA, tB e tC sono nomi accettabili per tabelle padre distinte, mentre t1, t2 e t3 non lo sono.

  • Nomi delle tabelle delle partizioni individuali: le partizioni dello stesso elemento principale devono differire l'una dall'altra solo in cifre. Ad esempio, i nomi di partizione accettabili per tA potrebbero essere tA1, tA2 o T1a, T2a o anche un numero maggiore di cifre.

    Eventuali altre differenze (lettere, caratteri speciali) non garantiranno l'applicazione del piano.

  • Nomi degli indici: nella gerarchia delle tabelle delle partizioni, assicurati che tutti gli indici abbiano nomi univoci. Ciò significa che le parti non numeriche dei nomi devono essere diverse. Ad esempio, se si dispone di una tabella partizionata denominata tA con un indice denominatotA_col1_idx1, non è possibile avere un altro indice denominato. tA_col1_idx2 Tuttavia, è possibile avere un indice chiamato tA_a_col1_idx2 perché la parte non numerica del nome è univoca. Questa regola si applica agli indici creati sia nella tabella principale che nelle singole tabelle delle partizioni.

La mancata conformità alle convenzioni di denominazione sopra citate può comportare la mancata applicazione dei piani approvati. L'esempio seguente illustra tale mancata applicazione: 

postgres=>create table t1(i int, j int, k int, l int, m int) partition by range(i);
CREATE TABLE
postgres=>create table t1a partition of t1 for values from (0) to (1000);
CREATE TABLE
postgres=>create table t1b partition of t1 for values from (1001) to (2000);
CREATE TABLE
postgres=>SET apg_plan_mgmt.capture_plan_baselines TO 'manual';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 0;

                            QUERY PLAN
--------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on t1a t1_1
               Filter: (i > 0)
         ->  Seq Scan on t1b t1_2
               Filter: (i > 0)
 SQL Hash: -1720232281, Plan Hash: -1010664377
(7 rows)
postgres=>SET apg_plan_mgmt.use_plan_baselines TO 'on';
SET
postgres=>explain (hashes true, costs false) select count(*) from t1 where i > 1000;

                            QUERY PLAN
-------------------------------------------------------------------------
 Aggregate
   ->  Seq Scan on t1b t1
         Filter: (i > 1000)
 Note: This is not an Approved plan. No usable Approved plan was found.
 SQL Hash: -1720232281, Plan Hash: 335531806
(5 rows)

Anche se i due piani possono apparire identici, Plan Hash i loro valori sono diversi a causa dei nomi delle tabelle secondarie. I nomi delle tabelle variano in base ai caratteri alfabetici anziché alle sole cifre, il che comporta un errore di applicazione.