# Consultas de una sola partición en Base de datos ilimitada de Aurora PostgreSQL
Una *consulta de una sola partición* es una consulta que se puede ejecutar directamente en una partición y, al mismo tiempo, mantener la semántica [ACID](https://en.wikipedia.org/wiki/ACID) de SQL. Cuando el planificador de consultas del enrutador encuentra una consulta de este tipo, la detecta y envía toda la consulta SQL a la partición correspondiente.
Esta optimización reduce el número de recorridos de ida y vuelta a través de la red entre el enrutador y la partición, lo que mejora el rendimiento. Actualmente, esta optimización se realiza para las consultas `INSERT`, `SELECT`, `UPDATE` y `DELETE`.
**Topics**
+ [Ejemplos de consultas de una sola partición](#limitless-query.single-shard.examples)
+ [Restricciones para las consultas de una sola partición](#limitless-query.single-shard.restrictions)
+ [Uniones totalmente cualificadas (explícitas)](#limitless-query.single-shard.fq)
+ [Definición de una clave de partición activa](#limitless-query.single-shard.active)
## Ejemplos de consultas de una sola partición
En los siguientes ejemplos, tenemos la tabla particionada `customers`, con la clave de partición `customer_id` y la tabla de referencia `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)
```
## Restricciones para las consultas de una sola partición
Las consultas de una sola partición tienen las siguientes restricciones:
**Funciones**
Si una consulta de una sola partición contiene una función, la consulta solo es apta para la optimización de una sola partición si se cumple una de las siguientes condiciones:
+ La función es inmutable. Para obtener más información, consulte [Volatilidad de las funciones](limitless-reference.DDL-limitations.md#limitless-function-volatility).
+ La función es mutable, pero está registrada en la vista `rds_aurora.limitless_distributed_functions`. Para obtener más información, consulte [Distribución de funciones](limitless-reference.DDL-limitations.md#limitless-function-distribution).
**Vistas**
Si una consulta contiene una o más vistas, la optimización de una sola partición estará deshabilitada para la consulta si cumple una de las siguientes condiciones:
+ Cualquier vista tiene el atributo `security_barrier`.
+ Los objetos utilizados en la consulta requieren varios privilegios de usuario. Por ejemplo, una consulta contiene dos vistas y las vistas se ejecutan con dos usuarios diferentes.
```
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)
```
**Instrucciones PREPARE y EXECUTE**
La base de datos ilimitada de Aurora PostgreSQL admite la optimización de una sola partición para las instrucciones `SELECT`, `UPDATE` y `DELETE` preparadas.
Sin embargo, si usa instrucciones preparadas para `PREPARE` y `EXECUTE` con `plan_cache_mode` establecido en `'force_generic_plan'`, el planificador de consultas rechaza la optimización de una sola partición para esa consulta.
**PL/pgSQL**
Las consultas con variables PL/pgSQL se ejecutan como instrucciones preparadas de forma implícita. Si una consulta contiene variables PL/pgSQL, el planificador de consultas rechaza la optimización de una sola partición.
El bloque PL/pgSQL admite la optimización si la instrucción no contiene ninguna variable PL/pgSQL.
## Uniones totalmente cualificadas (explícitas)
La optimización de una sola partición se basa en la eliminación de particiones. El optimizador de PostgreSQL elimina las particiones en función de condiciones constantes. Si Base de datos ilimitada de Aurora PostgreSQL descubre que todas las particiones y tablas restantes se encuentran en la misma partición, marca la consulta apta para la optimización de una sola partición. Todas las condiciones del filtro deben ser explícitas para que la eliminación de particiones funcione. Base de datos ilimitada de Aurora PostgreSQL no puede eliminar las particiones sin uno o más predicados de unión o predicados de filtrado en las claves de partición de cada tabla particionada de la instrucción.
Supongamos que hemos dividido las tablas `customers`, `orders` y `order_details` en función de la columna `customer_id`. En este esquema, la aplicación intenta mantener todos los datos de un cliente en una única partición.
Analice la siguiente consulta:
```
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;
```
Esta consulta recupera todos los datos de un cliente (`c.customer_id = 1`). Los datos de este cliente se encuentran en una sola partición, pero Base de datos ilimitada de Aurora PostgreSQL no cualifica esta consulta como consulta de una sola partición. El proceso del optimizador de la consulta es el siguiente:
1. El optimizador puede eliminar las particiones de `customers` y `orders` de la siguiente condición:
```
c.customer_id = 1
c.customer_id = o.customer_id
o.customer_id = 1 (transitive implicit condition)
```
1. El optimizador no puede eliminar ninguna partición de `order_details` porque no hay ninguna condición constante en la tabla.
1. El optimizador concluye que ha leído todas las particiones de `order_details`. Por lo tanto, la consulta no puede cualificarse para la optimización de una sola partición.
Para convertirla en una consulta de una sola partición, agregamos la siguiente condición de unión explícita:
```
o.customer_id = od.customer_id
```
El cambio de consulta tiene un aspecto similar al siguiente:
```
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;
```
Ahora el optimizador puede eliminar las particiones de `order_details`. La nueva consulta se convierte en una consulta de una sola partición y cumple los requisitos para la optimización.
## Definición de una clave de partición activa
Esta característica le permite establecer una única clave de partición al consultar la base de datos, lo que hace que todas las consultas `SELECT` y DML se asocien a la clave de partición como predicado constante. Esta característica resulta útil si ha migrado a Base de datos ilimitada de Aurora PostgreSQL y ha desnormalizado el esquema al añadir claves de partición a las tablas.
Puede añadir un predicado de clave de partición de forma automática a la lógica SQL existente, sin cambiar la semántica de las consultas. La adición de un predicado de clave de partición activa solo se realiza en el caso de las [tablas compatibles](#active-shard-key-compatible-tables).
La característica de clave de partición activa utiliza la variable `rds_aurora.limitless_active_shard_key`, que tiene la siguiente sintaxis:
```
SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';
```
Algunas consideraciones sobre las claves de partición activas y las claves externas:
+ Una tabla particionada puede tener una restricción de clave externa si las tablas principal y secundaria están colocadas y la clave externa es un superconjunto de la clave de partición.
+ Una tabla particionada puede tener una restricción de clave externa a una tabla de referencia.
+ Una tabla particionada puede tener una restricción de clave externa a otra tabla de referencia.
Supongamos que tenemos una tabla `customers` particionada en la columna `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;
```
Con un conjunto de claves de partición activo, las consultas sufren las siguientes transformaciones.
**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;
```
**Uniones**
Al realizar operaciones de unión en tablas con una clave de partición activa, el predicado de la clave de partición se añade automáticamente a todas las tablas implicadas en la unión. Esta adición automática del predicado de la clave de partición solo se produce cuando todas las tablas de la consulta pertenecen al mismo grupo de colocación. Si la consulta incluye tablas de diferentes grupos de colocación, se genera un error.
Supongamos que también tenemos las tablas `orders` y `order_details` y que están colocadas junto a la tabla `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;
```
Recupere las últimas diez facturas de pedidos de un cliente cuyo ID de cliente sea 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;
```
Esta consulta se convertirá en lo siguiente:
```
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;
```
**Tablas que admiten claves de partición activas**
El predicado de la clave de partición se agrega solo a las tablas que son compatibles con la clave de partición activa. Una tabla se considera compatible si tiene el mismo número de columnas en su clave de partición que el especificado en la variable `rds_aurora.limitless_active_shard_key`. Si la consulta incluye tablas que son incompatibles con la clave de partición activa, el sistema genera un error en lugar de continuar con la consulta.
Por ejemplo:
```
-- 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;
```