Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Requêtes à partition unique dans Aurora PostgreSQL Limitless Database
Une *requête à partition unique* est une requête qui peut être exécutée directement sur une partition, tout en conservant les propriétés [ACID](https://en.wikipedia.org/wiki/ACID) du SQL. Lorsque le planificateur de requêtes du routeur identifie une requête de ce type, il transmet la requête SQL complète à la partition concernée.
Cette optimisation réduit le nombre d’allers et retours réseau entre le routeur et la partition, améliorant ainsi les performances. Actuellement, cette optimisation est effectuée pour les requêtes `INSERT`, `SELECT`, `UPDATE` et `DELETE`.
**Topics**
+ [Exemples de requêtes à partition unique](#limitless-query.single-shard.examples)
+ [Restrictions appliquées aux requêtes à partition unique](#limitless-query.single-shard.restrictions)
+ [Jointures entièrement qualifiées (explicites)](#limitless-query.single-shard.fq)
+ [Configuration d’une clé de partition active](#limitless-query.single-shard.active)
## Exemples de requêtes à partition unique
Dans les exemples suivants, nous utiliserons la table partitionnée `customers`, avec la clé de partition `customer_id` et la table de référence `zipcodes`.
**SELECT**
```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM customers WHERE customer_id = 100;
QUERY PLAN
---------------------------------------------------------
Foreign Scan
Output: customer_id, other_id, customer_name, balance
Remote SQL: SELECT customer_id,
other_id,
customer_name,
balance
FROM public.customers
WHERE (customer_id = 100)
Single Shard Optimized
(9 rows)
```
```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders
LEFT JOIN zipcodes ON orders.zipcode_id = zipcodes.zipcode_id
WHERE customer_id = 11;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Foreign Scan
Output: customer_id, order_id, zipcode_id, customer_name, balance, zipcodes.zipcode_id, zipcodes.city
Remote SQL: SELECT orders.customer_id,
orders.order_id,
orders.zipcode_id,
orders.customer_name,
orders.balance,
zipcodes.zipcode_id,
zipcodes.city
FROM (public.orders
LEFT JOIN public.zipcodes ON ((orders.zipcode_id = zipcodes.zipcode_id)))
WHERE (orders.customer_id = 11)
Single Shard Optimized
(13 rows)
```
**INSERT**
```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO customers
(customer_id, other_id, customer_name, balance)
VALUES (1, 10, 'saikiran', 1000);
QUERY PLAN
-------------------------------------------------------
Insert on public.customers
-> Result
Output: 1, 10, 'saikiran'::text, '1000'::real
Single Shard Optimized
(4 rows)
```
**UPDATE**
```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) UPDATE orders SET balance = balance + 100
WHERE customer_id = 100;
QUERY PLAN
---------------------------------------------------------------------------------------------
Update on public.orders
Foreign Update on public.orders_fs00002 orders_1
-> Foreign Update
Remote SQL: UPDATE public.orders SET balance = (balance + (100)::double precision)
WHERE (customer_id = 100)
Single Shard Optimized
(6 rows)
```
**DELETE**
```
postgres_limitless=> EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM orders
WHERE customer_id = 100 and balance = 0;
QUERY PLAN
---------------------------------------------------------------------
Delete on public.orders
Foreign Delete on public.orders_fs00002 orders_1
-> Foreign Delete
Remote SQL: DELETE FROM public.orders
WHERE ((customer_id = 100) AND (balance = (0)::double precision))
Single Shard Optimized
(6 rows)
```
## Restrictions appliquées aux requêtes à partition unique
Les requêtes à partition unique sont soumises aux restrictions suivantes :
**Fonctions**
Si une requête à partition unique contient une fonction, elle n’est éligible à l’optimisation à partition unique que si l’une des conditions suivantes est remplie :
+ La fonction est immuable. Pour plus d’informations, consultez [Volatilité des fonctions](limitless-reference.DDL-limitations.md#limitless-function-volatility).
+ La fonction est mutable, mais elle est enregistrée dans la vue `rds_aurora.limitless_distributed_functions`. Pour plus d’informations, consultez [Répartition des fonctions](limitless-reference.DDL-limitations.md#limitless-function-distribution).
**Vues**
Si une requête contient une ou plusieurs vues, l’optimisation à partition unique est désactivée si l’une des conditions suivantes est remplie :
+ N’importe quelle vue possède l’attribut `security_barrier`.
+ Les objets utilisés dans la requête nécessitent plusieurs privilèges utilisateur. Par exemple, une requête contient deux vues, lesquelles sont exécutées par deux utilisateurs différents.
```
CREATE VIEW v1 AS SELECT customer_name FROM customers c WHERE c.customer_id = 1;
CREATE VIEW v2 WITH (security_barrier) AS SELECT customer_name FROM customers c WHERE c.customer_id = 1;
postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v1;
QUERY PLAN
------------------------------------------------------------------------------------
Foreign Scan (cost=100.00..101.00 rows=100 width=0)
Output: customer_name
Remote Plans from Shard postgres_s3:
Seq Scan on public.customers_ts00001 c (cost=0.00..24.12 rows=6 width=32)
Output: c.customer_name
Filter: (c.customer_id = 1)
Query Identifier: -6005737533846718506
Remote SQL: SELECT customer_name
FROM ( SELECT c.customer_name
FROM public.customers c
WHERE (c.customer_id = 1)) v1
Query Identifier: -5754424854414896228
(12 rows)
postgres_limitless=> EXPLAIN VERBOSE SELECT * FROM v2;
QUERY PLAN
--------------------------------------------------------------------------------------------
Foreign Scan on public.customers_fs00001 c (cost=100.00..128.41 rows=7 width=32)
Output: c.customer_name
Remote Plans from Shard postgres_s3:
Seq Scan on public.customers_ts00001 customers (cost=0.00..24.12 rows=6 width=32)
Output: customers.customer_name
Filter: (customers.customer_id = 1)
Query Identifier: 4136563775490008117
Remote SQL: SELECT customer_name FROM public.customers WHERE ((customer_id = 1))
Query Identifier: 5056054318010163757
(9 rows)
```
**Instructions PREPARE et EXECUTE**
Aurora PostgreSQL Limitless Database prend en charge l’optimisation à partition unique pour les instructions préparées `SELECT`, `UPDATE` et `DELETE`.
Cependant, lorsque des instructions préparées sont utilisées pour `PREPARE` et `EXECUTE` alors que `plan_cache_mode` est défini sur `'force_generic_plan'`, le planificateur de requêtes rejette l’optimisation à partition unique pour cette requête.
**PL/pgSQL**
Les requêtes contenant des variables PL/pgSQL sont exécutées sous forme d’instructions préparées implicites. Si une requête contient des variables PL/pgSQL, le planificateur de requêtes rejette l’optimisation à partition unique.
L’optimisation est prise en charge dans le bloc PL/pgSQL si l’instruction ne contient aucune variable PL/pgSQL.
## Jointures entièrement qualifiées (explicites)
L’optimisation à partition unique est basée sur l’élimination des partitions. L’optimiseur PostgreSQL élimine les partitions en fonction de conditions constantes. Si Aurora PostgreSQL Limitless Database constate que toutes les partitions et tables restantes résident sur la même partition, elle indique que la requête est éligible à l’optimisation à partition unique. Toutes les conditions du filtre doivent être explicites pour que l’élimination des partitions fonctionne. Aurora PostgreSQL Limitless Database ne peut pas éliminer de partitions en l’absence d’un ou de plusieurs prédicats de jointure ou de filtrage portant sur les clés de partition de chaque table partitionnée de l’instruction.
Supposons que nous ayons partitionné les tables `customers`, `orders` et `order_details` en fonction de la colonne `customer_id`. Dans ce schéma, l’application essaie de conserver toutes les données d’un client sur une seule partition.
Considérons la requête suivante :
```
SELECT * FROM
customers c, orders o, order_details od
WHERE c.customer_id = o.customer_id
AND od.order_id = o.order_id
AND c.customer_id = 1;
```
Cette requête récupère toutes les données d’un client (`c.customer_id = 1`). Les données de ce client résident sur une seule partition, mais Aurora PostgreSQL Limitless Database ne considère pas cette requête comme une requête à partition unique. Le processus d’optimisation de la requête est le suivant :
1. L’optimiseur peut éliminer les partitions pour `customers` et `orders` selon les conditions suivantes :
```
c.customer_id = 1
c.customer_id = o.customer_id
o.customer_id = 1 (transitive implicit condition)
```
1. L’optimiseur ne peut éliminer aucune partition pour `order_details`, car aucune condition constante n’est appliquée à la table.
1. L’optimiseur détermine qu’il a lu toutes les partitions depuis `order_details`. Par conséquent, la requête ne peut pas être qualifiée pour l’optimisation à partition unique.
Pour en faire une requête à partition unique, nous ajoutons la condition de jointure explicite suivante :
```
o.customer_id = od.customer_id
```
La requête modifiée se présente comme suit :
```
SELECT * FROM
customers c, orders o, order_details od
WHERE c.customer_id = o.customer_id
AND o.customer_id = od.customer_id
AND od. order_id = o. order_id
AND c.customer_id = 1;
```
L’optimiseur peut désormais éliminer les partitions pour `order_details`. La nouvelle requête devient une requête à partition unique et est éligible à l’optimisation.
## Configuration d’une clé de partition active
Cette fonctionnalité vous permet de définir une clé de partition unique lors de l’interrogation de la base de données, ce qui permet d’ajouter la clé de partition à toutes les requêtes `SELECT` et DML en tant que prédicat constant. Cette fonctionnalité est utile si vous avez migré vers Aurora PostgreSQL Limitless Database et que vous avez dénormalisé le schéma en ajoutant des clés de partition aux tables.
Vous pouvez ajouter automatiquement un prédicat de clé de partition à la logique SQL existante, sans modifier la sémantique des requêtes. L’ajout d’un prédicat de clé de partition actif n’est possible que sur les [tables compatibles](#active-shard-key-compatible-tables).
La fonctionnalité de clé de partition active utilise la variable `rds_aurora.limitless_active_shard_key`, dont la syntaxe est la suivante :
```
SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';
```
Quelques considérations concernant les clés de partition actives et les clés étrangères :
+ Une table partitionnée peut être soumise à une contrainte de clé étrangère lorsque les tables parente et enfant sont colocalisées et que la clé étrangère constitue un sur-ensemble de la clé de partition.
+ Une table partitionnée peut être soumise à une contrainte de clé étrangère vers une table de référence.
+ Une table de référence peut être soumise à une contrainte de clé étrangère vers une autre table de référence.
Prenons l’exemple d’une table partitionnée `customers` sur la colonne `customer_id`.
```
BEGIN;
SET local rds_aurora.limitless_create_table_mode='sharded';
SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}';
CREATE TABLE customers(customer_id int PRIMARY KEY, name text , email text);
COMMIT;
```
Lorsqu’une clé de partition active est définie, les requêtes subissent les transformations suivantes.
**SELECT**
```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
SELECT * FROM customers;
-- This statement is changed to:
SELECT * FROM customers WHERE customer_id = '123'::int;
```
**INSERT**
```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
INSERT INTO customers(name, email) VALUES('Alex', 'alex@example.com');
-- This statement is changed to:
INSERT INTO customers(customer_id, name, email) VALUES('123'::int, 'Alex', 'alex@example.com');
```
**UPDATE**
```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
UPDATE customers SET email = 'alex_new_email@example.com';
-- This statement is changed to:
UPDATE customers SET email = 'alex_new_email@example.com' WHERE customer_id = '123'::int;
```
**DELETE**
```
SET rds_aurora.limitless_active_shard_key = '{"123"}';
DELETE FROM customers;
-- This statement is changed to:
DELETE FROM customers WHERE customer_id = '123'::int;
```
**Jointures**
Lorsque vous effectuez des opérations de jointure sur des tables ayant une clé de partition active, le prédicat de clé de partition est automatiquement ajouté à toutes les tables participant à la jointure. Cet ajout automatique du prédicat de clé de partition se produit uniquement lorsque toutes les tables de la requête appartiennent au même groupe de collocation. Lorsque des tables issues de plusieurs groupes de colocalisation sont impliquées dans la requête, le système renvoie une erreur.
Supposons en outre l’existence des tables `orders` et `order_details`, qui sont colocalisées avec la table `customers`.
```
SET local rds_aurora.limitless_create_table_mode='sharded';
SET local rds_aurora.limitless_create_table_collocate_with='customers';
SET local rds_aurora.limitless_create_table_shard_key='{"customer_id"}';
CREATE TABLE orders (id int , customer_id int, total_amount int, date date);
CREATE TABLE order_details (id int , order_id int, customer_id int, product_name VARCHAR(100), price int);
COMMIT;
```
Récupérez les 10 dernières factures de commande du client portant l’ID 10.
```
SET rds_aurora.limitless_active_shard_key = '{"10"}';
SELECT * FROM customers, orders, order_details WHERE
orders.customer_id = customers.customer_id AND
order_details.order_id = orders.order_id AND
customers.customer_id = 10
order by order_date limit 10;
```
Cette requête est transformée comme suit :
```
SELECT * FROM customers, orders, order_details WHERE
orders.customer_id = customers.customer_id AND
orders.order_id = order_details.order_id AND
customers.customer_id = 10 AND
order_details.customer_id = 10 AND
orders.customer_id = 10 AND
ORDER BY "order_date" LIMIT 10;
```
**Tables compatibles avec les clés de partition actives**
Le prédicat de clé de partition est ajouté uniquement aux tables compatibles avec la clé de partition active. Une table est considérée comme compatible si le nombre de colonnes de sa clé de partition correspond à celui indiqué dans la variable `rds_aurora.limitless_active_shard_key`. Si la requête concerne des tables incompatibles avec la clé de partition active, le système génère une erreur au lieu de poursuivre la requête.
Exemples :
```
-- Compatible table
SET rds_aurora.limitless_active_shard_key = '{"10"}';
-- The following query works because the customers table is sharded on one column.
SELECT * FROM customers;
-- Incompatible table
SET rds_aurora.limitless_active_shard_key = '{"10","20"}';
-- The following query raises a error because the customers table isn't sharded on two columns.
SELECT * FROM customers;
```