# Aurora PostgreSQL Limitless Database の単一シャードクエリ
<a name="limitless-query.single-shard"></a>

*単一シャードクエリ*は、SQL [ACID](https://en.wikipedia.org/wiki/ACID) セマンティクスを維持しながらシャードで直接実行できるクエリです。このようなクエリがルーターのクエリプランナーによって検出されると、プランナーはそれを検出し、SQL クエリ全体を対応するシャードにプッシュダウンします。

この最適化により、ルーターとシャード間のネットワークラウンドトリップの回数が減り、パフォーマンスが向上します。現在、この最適化は `INSERT`、`SELECT`、`UPDATE`、および `DELETE` クエリに対して実行されます。

**Topics**
+ [単一シャードクエリの例](#limitless-query.single-shard.examples)
+ [単一シャードクエリの制限](#limitless-query.single-shard.restrictions)
+ [完全修飾 (明示的) 結合](#limitless-query.single-shard.fq)
+ [アクティブなシャードキーの設定](#limitless-query.single-shard.active)

## 単一シャードクエリの例
<a name="limitless-query.single-shard.examples"></a>

次の例では、シャードキー `customer_id` を持つシャードテーブル `customers` と、リファレンステーブル `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)
```

## 単一シャードクエリの制限
<a name="limitless-query.single-shard.restrictions"></a>

単一シャードクエリには以下の制限があります。

**関数**  
単一シャードクエリに関数が含まれている場合、クエリは次のいずれかの条件に該当する場合にのみ単一シャード最適化の対象となります。  
+ 関数がイミュータブルである。詳細については、「[関数の可変性](limitless-reference.DDL-limitations.md#limitless-function-volatility)」を参照してください。
+ 関数は変更可能だが、`rds_aurora.limitless_distributed_functions` ビューに登録されている。詳細については、「[関数の分散](limitless-reference.DDL-limitations.md#limitless-function-distribution)」を参照してください。

**ビュー**  
クエリに 1 つ以上のビューが含まれている場合で、次のいずれかの条件に該当する場合、クエリの単一シャード最適化は無効になります。  
+ どのビューにも `security_barrier` 属性がある。
+ クエリで使用されるオブジェクトには、複数のユーザー権限が必要である。例えば、クエリに 2 つのビューが含まれ、ビューが 2 つの異なるユーザーで実行される場合です。

```
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)
```

**PREPARE ステートメントと EXECUTE ステートメント**  
Aurora PostgreSQL Limitless Database は、準備済みの `SELECT`、`UPDATE`、`DELETE` ステートメントに対する単一シャード最適化をサポートしています。  
ただし、準備済みステートメントを `PREPARE` と `EXECUTE` で使用する場合に、`plan_cache_mode` が `'force_generic_plan'` に設定されていると、クエリプランナーはそのクエリに対する単一シャード最適化を拒否します。

**PL/pgSQL**  
PL/pgSQL 変数を含むクエリは、暗黙的に準備済みステートメントとして実行されます。クエリに PL/pgSQL 変数が含まれている場合、クエリプランナーは単一シャード最適化を拒否します。  
ステートメントに PL/pgSQL 変数が含まれていない場合、PL/pgSQL ブロックで最適化がサポートされます。

## 完全修飾 (明示的) 結合
<a name="limitless-query.single-shard.fq"></a>

単一シャード最適化は、パーティション排除に基づいています。PostgreSQL オプティマイザーは、一定の条件に基づいてパーティションを排除します。Aurora PostgreSQL Limitless Database が、残りのすべてのパーティションとテーブルが同じシャードにあると判断した場合、単一シャード最適化の対象としてクエリをマークします。パーティション排除が機能するには、すべてのフィルター条件が明示的である必要があります。Aurora PostgreSQL Limitless Database は、ステートメント内のすべてのシャードテーブルのシャードキーに対して 1 つ以上の結合述語またはフィルター述語がないパーティションを排除することはできません。

`customer_id` 列に基づいて、`customers`、`orders`、`order_details` テーブルをパーティション化したとします。このスキーマでは、アプリケーションは顧客のすべてのデータを 1 つのシャードに保持しようとします。

次のクエリについて考えます。

```
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;
```

このクエリは、顧客 (`c.customer_id = 1`) のすべてのデータを取得します。この顧客のデータは単一のシャードにありますが、Aurora PostgreSQL Limitless Database はこのクエリを単一シャードクエリとして認識しません。クエリのオプティマイザープロセスは次のとおりです。

1. オプティマイザーは、次の条件に基づいて `customers` および `orders` のパーティションを排除できます。

   ```
   c.customer_id = 1
   c.customer_id = o.customer_id
   o.customer_id =  1 (transitive implicit condition)
   ```

1. テーブルに一定の条件がないため、オプティマイザーは `order_details` のパーティションを排除できません。

1. オプティマイザーは、`order_details` からすべてのパーティションを読み取ったと結論付けます。そのため、このクエリは単一シャード最適化の対象とはなりません。

これを単一シャードクエリにするには、次の明示的な結合条件を追加します。

```
o.customer_id = od.customer_id
```

変更後のクエリは次のようになります。

```
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;
```

これで、オプティマイザーは `order_details` のパーティションを排除できます。新しいクエリは単一シャードクエリになり、最適化の対象となります。

## アクティブなシャードキーの設定
<a name="limitless-query.single-shard.active"></a>

この機能を使用すると、データベースのクエリ中に単一のシャードキーを設定でき、これにより、すべての `SELECT` および DML クエリにシャードキーが定数述語として追加されます。この機能は、Aurora PostgreSQL Limitless Database に移行し、テーブルにシャードキーを追加してスキーマを非正規化した場合に便利です。

クエリのセマンティクスを変更せずに、シャードキー述語を既存の SQL ロジックに自動的に追加できます。アクティブなシャードキー述語の追加は、[互換性のあるテーブル](#active-shard-key-compatible-tables)に対してのみ行われます。

アクティブなシャードキー機能は、次の構文を持つ `rds_aurora.limitless_active_shard_key` 変数を使用します。

```
SET [session | local] rds_aurora.limitless_active_shard_key = '{"col1_value", "col2_value", ...}';
```

アクティブなシャードキーと外部キーに関する考慮事項:
+ 親テーブルと子テーブルがコロケーションされ、外部キーがシャードキーのスーパーセットである場合、シャードテーブルには外部キー制約を含めることができます。
+ シャードテーブルには、リファレンステーブルへの外部キー制約を含めることができます。
+ リファレンステーブルには、別のリファレンステーブルへの外部キー制約を含めることができます。

`customer_id` 列にシャードされた `customers` テーブルがあるとします。

```
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;
```

 アクティブなシャードキーが設定されている場合、クエリには次の変換が適用されます。

**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;
```

**Joins**  
アクティブなシャードキーを持つテーブルで結合オペレーションを実行すると、結合に関係するすべてのテーブルにシャードキー述語が自動的に追加されます。このシャードキー述語の自動追加は、クエリ内のすべてのテーブルが同じコロケーショングループに属している場合にのみ発生します。クエリに異なるコロケーショングループのテーブルが含まれる場合、代わりにエラーが発生します。  
`customers` テーブルとコロケーションされている `orders` テーブルと `order_details` テーブルがあるとします。  

```
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;
```
顧客 ID が 10 である顧客の過去 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;
```
このクエリは、次に変換されます。  

```
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;
```

**アクティブなシャードキーと互換性のあるテーブル**  
シャードキー述語は、アクティブなシャードキーと互換性のあるテーブルにのみ追加されます。テーブルのシャードキー内の列数が `rds_aurora.limitless_active_shard_key` 変数で指定されている列数と同じである場合、テーブルは互換性があると見なされます。クエリにアクティブなシャードキーと互換性のないテーブルが含まれる場合、システムはクエリを続行する代わりにエラーを発生させます。  
例：  

```
-- 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;
```