rds_duckdb拡張機能を使用したAPクエリの高速化 - ApsaraDB RDS

複雑なクエリを実行し、クエリ結果をすばやく取得したい場合は、ApsaraDB RDS for PostgreSQLのrds_duckdb拡張機能を使用して、分析処理 (AP) クエリアクセラレーションを実装できます。拡張機能は、ローカルテーブルを列指向テーブルとしてエクスポートし、ベクトル化機能を使用して、元のクエリステートメントを変更することなく、複雑なクエリを大幅に高速化します。これにより、クエリ結果を便利かつ効率的に取得できます。

概要

ApsaraDB RDS for PostgreSQLのrds_duckdb拡張機能は、効率的でリソースに優しいDuckDBに基づいて開発されています。この拡張により、分析クエリ機能が強化されます。拡張機能を使用して、ApsaraDB RDS for PostgreSQLインスタンスのローカルテーブルを列指向テーブルとしてエクスポートし、APクエリアクセラレーション機能を有効にすることができます。これにより、分析サービスの要件を満たす複雑なクエリが大幅に加速されます。

前提条件

RDSインスタンスはPostgreSQL 12以降のバージョンを実行します。
RDSインスタンスは、20241030以降のマイナーエンジンバージョンを実行します。
rds_duckdbは、shared_preload_librariesパラメーターの実行中の値に追加されます。
たとえば、shared_preload_librariesパラメーターの実行中の値を 'pg_stat_statements,auto_explain, rds_duckdb 'に設定でき。インスタンスのパラメーターを設定する方法の詳細については、「ApsaraDB RDS For PostgreSQLインスタンスのパラメーターの変更」をご参照ください。
shared_preload_librariesパラメーターは 'rds_duckdb' に設定されています。パラメーターの設定方法の詳細については、「ApsaraDB RDS For PostgreSQLインスタンスのパラメーターの変更」をご参照ください。

使用上の注意

プライマリRDSインスタンスとセカンダリRDSインスタンス間で列指向テーブルのデータを同期することはできません。
列指向テーブルのデータをエクスポートして、自動増分同期を実装することはできません。

拡張機能の作成と削除

拡張機能を作成または削除するには、特権アカウントを使用する必要があります。

拡張機能の作成

CREATE EXTENSION rds_duckdb;

拡張機能のDuckDBバージョンを照会する

SELECT rds_duckdb.duckdb_version();

拡張機能の削除

DROP EXTENSION rds_duckdb;

列指向テーブルの管理

列指向テーブルの作成

次のステートメントを実行して、RDSインスタンスのローカルテーブルを列指向テーブルとしてエクスポートします。ローカルテーブルは、ユーザーテーブル、マテリアライズドビュー、または外部テーブルです。列指向テーブルは、分析クエリを高速化するために使用されます。

SELECT rds_duckdb.create_duckdb_table('Local table name');

列指向テーブルの更新

次のステートメントを実行して、RDSインスタンスのローカルテーブルの最新データに基づいてエクスポートされた列ストアテーブルを更新し、テーブルスキーマとデータを更新します。

SELECT rds_duckdb.refresh_duckdb_table('Local table name');

列指向テーブルのサイズの照会

SELECT rds_duckdb.duckdb_table_size('Local table name');

現在のデータベース内のすべてのエクスポートされたテーブルのサイズの照会

SELECT rds_duckdb.duckdb_database_size();

列指向テーブルの削除

SELECT rds_duckdb.drop_duckdb_table('Local table name');

APクエリの高速化機能の管理

rds_duckdb拡張機能を使用して、読み取り専用クエリを高速化できます。 APクエリアクセラレーション機能を有効にした後、テーブルからデータをクエリするためにSQL文を実行する必要があり、テーブルにDuckDB列指向テーブルが存在する場合、クエリを高速化するためにDuckDBでSQL文が実行されます。 SQL文がサポートされていないDMLまたはDDL文である場合、または列指向テーブルが存在しない場合、SQL文はApsaraDB RDS for PostgreSQLで実行されます。

システムは、ApsaraDB RDS for PostgreSQLで実行されるSQL文について、warning: duckdb以外のテーブル (テスト) で操作を実行しようとしている場合、PGにフォールバックと同様の警告を表示します。括弧 () 内の内容は、DuckDB列指向テーブルが存在しないRDSインスタンスのテーブルを示します。

また、読み取り専用クエリに使用されていないSQL文について、[警告: DuckDBテーブルの変更操作は現在サポートされていません。フォールバックto PG] 警告が表示されます。

APクエリアクセラレーション機能の有効化

SET rds_duckdb.execution = on;

APクエリアクセラレーションのパラメーターの設定

セッションでパラメーターを設定して、APクエリアクセラレーションのパフォーマンスを管理できます。サンプルコマンド：

SET rds_duckdb.worker_threads = 32;
SET rds_duckdb.memory_limit = 16384;

パラメーター

説明

提案

rds_duckdb.worker_スレッド

APクエリの高速化に使用されるワーカースレッドの数。

有効な値: 1 ~ 255

デフォルト値: 1。これは、1つのワーカースレッドのみが使用されることを示します。

パフォーマンスを大幅に向上させたい場合は、このパラメーターをCPUコアの数に設定することを推奨します。
このパラメーターは、クエリが実行されるハードウェアデバイスに密接に関連しています。値が大きいほど、APクエリの高速化を実行したときのCPU負荷が高くなります。このパラメーターは、ビジネス要件に基づいて設定する必要があります。
このパラメーターをより高い値に設定すると、パフォーマンスが向上しますが、CPU負荷も増加します。このパラメーターを低い値に設定すると、アクセラレーションのパフォーマンスは影響を受けますが、CPU負荷も減少します。

rds_duckdb.memory_limit

APクエリの高速化に使用できる最大メモリ。

単位：MB。このパラメーターを設定するときにユニットを追加する必要はありません。

有効値: 1〜INT32_MAX。

デフォルト値: 100。上限が100 MBであることを示します。

パフォーマンスを大幅に向上させたい場合は、このパラメーターをワークロードでサポートされている大きな値に設定することをお勧めします。
このパラメーターは、クエリが実行されるハードウェアデバイスに密接に関連しています。値が大きいほど、APクエリの高速化を実行したときのメモリ使用量が多いことを示します。このパラメーターは、ビジネス要件に基づいて設定する必要があります。
このパラメーターのデフォルト値は保守的な値です。ビジネス要件に基づいてこのパラメーターを設定することを推奨します。
このパラメーターを小さな値に設定すると、大きなテーブルの列指向テーブルのエクスポートプロセスとAPクエリの高速化のパフォーマンスが影響を受けます。

説明

DuckDBパラメーターの詳細については、「設定」をご参照ください。

APクエリアクセラレーション機能の無効化

SET rds_duckdb.execution = off;

例

rds_duckdb拡張機能のパフォーマンスのテスト

この例では、LinuxサーバーでTPC-Hテストを実行して、複雑なクエリに対するrds_duckdb拡張機能のパフォーマンス向上をテストします。

Elastic Compute Service (ECS) インスタンスを作成し、テストデータを生成します。詳細については、「クイック起動タブのサブスクリプションECSインスタンスの作成」をご参照ください。
1. PostgreSQL公式WebサイトからPostgreSQLをダウンロードしてインストールします。
2. ダウンロードdbgen.
```
wget https://github.com/electrum/tpch-dbgen/archive/refs/heads/master.zip
yum install -y unzip zip
unzip master.zip
cd tpch-dbgen-master/
echo "#define EOL_HANDLING 1" >> config.h # Delete the vertical bars (|) at the end of each row of data.
make
./dbgen --help
```
3. テストデータを生成します。
  この例では、テストデータの格納パスは /data/testであり、テストデータのサイズは100 GBである。ビジネス要件に基づいて、データストレージパスとデータサイズを選択できます。
```
./dbgen -s 100
mkdir /data/test/tpch_data
mv *.tbl /data/test/tpch_data
```
  説明
  データボリュームのサイズは、クエリ速度に直接影響します。スケールファクタ (SF) は、データ量を記述するTPC-Hに使用される。 TPC-Hでは、1 SFは1 GBのデータを示す。同様に、100 SFは100 GBのデータを示す。 1 SFのデータ量は、8つのテーブルの合計データ量のみを示す。インデックスなどの要素に関するデータは含まれません。データを準備するときは、十分なストレージを確保する必要があります。

前提条件の説明に基づいてRDSインスタンスを作成し、テストデータをインポートします。

RDSインスタンスを作成します。詳細については、「ApsaraDB RDS for PostgreSQL インスタンスの作成」をご参照ください。
特権アカウントを作成します。詳細については、「アカウントの作成」をご参照ください。
RDSインスタンスに接続します。詳細については、「ApsaraDB RDS For PostgreSQLインスタンスへの接続」をご参照ください。

次のSQL文を実行して、TPC-Hテスト用の8つのテーブルを作成します。

CREATE TABLE customer(c_custkey BIGINT NOT NULL, c_name VARCHAR NOT NULL, c_address VARCHAR NOT NULL, c_nationkey INTEGER NOT NULL, c_phone VARCHAR NOT NULL, c_acctbal DECIMAL(15,2) NOT NULL, c_mktsegment VARCHAR NOT NULL, c_comment VARCHAR NOT NULL);
CREATE TABLE lineitem(l_orderkey BIGINT NOT NULL, l_partkey BIGINT NOT NULL, l_suppkey BIGINT NOT NULL, l_linenumber BIGINT NOT NULL, l_quantity DECIMAL(15,2) NOT NULL, l_extendedprice DECIMAL(15,2) NOT NULL, l_discount DECIMAL(15,2) NOT NULL, l_tax DECIMAL(15,2) NOT NULL, l_returnflag VARCHAR NOT NULL, l_linestatus VARCHAR NOT NULL, l_shipdate DATE NOT NULL, l_commitdate DATE NOT NULL, l_receiptdate DATE NOT NULL, l_shipinstruct VARCHAR NOT NULL, l_shipmode VARCHAR NOT NULL, l_comment VARCHAR NOT NULL);
CREATE TABLE nation(n_nationkey INTEGER NOT NULL, n_name VARCHAR NOT NULL, n_regionkey INTEGER NOT NULL, n_comment VARCHAR NOT NULL);
CREATE TABLE orders(o_orderkey BIGINT NOT NULL, o_custkey BIGINT NOT NULL, o_orderstatus VARCHAR NOT NULL, o_totalprice DECIMAL(15,2) NOT NULL, o_orderdate DATE NOT NULL, o_orderpriority VARCHAR NOT NULL, o_clerk VARCHAR NOT NULL, o_shippriority INTEGER NOT NULL, o_comment VARCHAR NOT NULL);
CREATE TABLE part(p_partkey BIGINT NOT NULL, p_name VARCHAR NOT NULL, p_mfgr VARCHAR NOT NULL, p_brand VARCHAR NOT NULL, p_type VARCHAR NOT NULL, p_size INTEGER NOT NULL, p_container VARCHAR NOT NULL, p_retailprice DECIMAL(15,2) NOT NULL, p_comment VARCHAR NOT NULL);
CREATE TABLE partsupp(ps_partkey BIGINT NOT NULL, ps_suppkey BIGINT NOT NULL, ps_availqty BIGINT NOT NULL, ps_supplycost DECIMAL(15,2) NOT NULL, ps_comment VARCHAR NOT NULL);
CREATE TABLE region(r_regionkey INTEGER NOT NULL, r_name VARCHAR NOT NULL, r_comment VARCHAR NOT NULL);
CREATE TABLE supplier(s_suppkey BIGINT NOT NULL, s_name VARCHAR NOT NULL, s_address VARCHAR NOT NULL, s_nationkey INTEGER NOT NULL, s_phone VARCHAR NOT NULL, s_acctbal DECIMAL(15,2) NOT NULL, s_comment VARCHAR NOT NULL);

\dt # View the imported data tables.

生成されたテストデータをインポートします。

COPY customer FROM '/data/test/tpch_data/customer.tbl' DELIMITER '|';
COPY lineitem FROM '/data/test/tpch_data/lineitem.tbl' DELIMITER '|';
COPY nation FROM '/data/test/tpch_data/nation.tbl' DELIMITER '|';
COPY orders FROM '/data/test/tpch_data/orders.tbl' DELIMITER '|';
COPY part FROM '/data/test/tpch_data/part.tbl' DELIMITER '|';
COPY partsupp FROM '/data/test/tpch_data/partsupp.tbl' DELIMITER '|';
COPY region FROM '/data/test/tpch_data/region.tbl' DELIMITER '|';
COPY supplier FROM '/data/test/tpch_data/supplier.tbl' DELIMITER '|';

\dt + # View the details of the imported data tables.

rds_duckdb拡張をインストールし、列指向テーブルを生成します。

rds_duckdb拡張子をインストールします。
```
CREATE EXTENSION rds_duckdb;
```

列指向テーブルを生成します。

# Create column-oriented tables for the local tables of the RDS instance.
SELECT rds_duckdb.create_duckdb_table('customer');
SELECT rds_duckdb.create_duckdb_table('lineitem');
SELECT rds_duckdb.create_duckdb_table('nation');
SELECT rds_duckdb.create_duckdb_table('orders');
SELECT rds_duckdb.create_duckdb_table('part');
SELECT rds_duckdb.create_duckdb_table('partsupp');
SELECT rds_duckdb.create_duckdb_table('region');
SELECT rds_duckdb.create_duckdb_table('supplier');

APクエリアクセラレーションのパラメーターを設定します。

# Specify the number of threads and memory limit based on your test machine. Unit of the memory limit: MB.
SET rds_duckdb.worker_threads = 32;
SET rds_duckdb.memory_limit = 8192;

APクエリの高速化機能を有効にします。

SET rds_duckdb.execution = on;

# Start timing.
\timing on

# Redirect the result to a file.
\o /data/test/tpch_data/tpch_out

次の22のTPC-H SQL文を実行して、クエリのパフォーマンスをテストします。

標準テストのSQL文

-- Q1
SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) AS sum_qty,
    sum(l_extendedprice) AS sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    avg(l_quantity) AS avg_qty,
    avg(l_extendedprice) AS avg_price,
    avg(l_discount) AS avg_disc,
    count(*) AS count_order
FROM
    lineitem
WHERE
    l_shipdate <= CAST('1998-09-02' AS date)
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus;


-- Q2
SELECT
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
FROM
    part,
    supplier,
    partsupp,
    nation,
    region
WHERE
    p_partkey = ps_partkey
    AND s_suppkey = ps_suppkey
    AND p_size = 15
    AND p_type LIKE '%BRASS'
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
    AND r_name = 'EUROPE'
    AND ps_supplycost = (
        SELECT
            min(ps_supplycost)
        FROM
            partsupp,
            supplier,
            nation,
            region
        WHERE
            p_partkey = ps_partkey
            AND s_suppkey = ps_suppkey
            AND s_nationkey = n_nationkey
            AND n_regionkey = r_regionkey
            AND r_name = 'EUROPE')
ORDER BY
    s_acctbal DESC,
    n_name,
    s_name,
    p_partkey
LIMIT 100;


-- Q3
SELECT
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
    AND c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate < CAST('1995-03-15' AS date)
    AND l_shipdate > CAST('1995-03-15' AS date)
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue DESC,
    o_orderdate
LIMIT 10;


-- Q4
SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= CAST('1993-07-01' AS date)
    AND o_orderdate < CAST('1993-10-01' AS date)
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate)
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;


-- Q5
SELECT
    n_name,
    sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM
    customer,
    orders,
    lineitem,
    supplier,
    nation,
    region
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND l_suppkey = s_suppkey
    AND c_nationkey = s_nationkey
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
    AND r_name = 'ASIA'
    AND o_orderdate >= CAST('1994-01-01' AS date)
    AND o_orderdate < CAST('1995-01-01' AS date)
GROUP BY
    n_name
ORDER BY
    revenue DESC;


-- Q6
SELECT
    sum(l_extendedprice * l_discount) AS revenue
FROM
    lineitem
WHERE
    l_shipdate >= CAST('1994-01-01' AS date)
    AND l_shipdate < CAST('1995-01-01' AS date)
    AND l_discount BETWEEN 0.05
    AND 0.07
    AND l_quantity < 24;


-- Q7
SELECT
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) AS revenue
FROM (
    SELECT
        n1.n_name AS supp_nation,
        n2.n_name AS cust_nation,
        extract(year FROM l_shipdate) AS l_year,
        l_extendedprice * (1 - l_discount) AS volume
    FROM
        supplier,
        lineitem,
        orders,
        customer,
        nation n1,
        nation n2
    WHERE
        s_suppkey = l_suppkey
        AND o_orderkey = l_orderkey
        AND c_custkey = o_custkey
        AND s_nationkey = n1.n_nationkey
        AND c_nationkey = n2.n_nationkey
        AND ((n1.n_name = 'FRANCE'
                AND n2.n_name = 'GERMANY')
            OR (n1.n_name = 'GERMANY'
                AND n2.n_name = 'FRANCE'))
        AND l_shipdate BETWEEN CAST('1995-01-01' AS date)
        AND CAST('1996-12-31' AS date)) AS shipping
GROUP BY
    supp_nation,
    cust_nation,
    l_year
ORDER BY
    supp_nation,
    cust_nation,
    l_year;


-- Q8
SELECT
    o_year,
    sum(
        CASE WHEN nation = 'BRAZIL' THEN
            volume
        ELSE
            0
        END) / sum(volume) AS mkt_share
FROM (
    SELECT
        extract(year FROM o_orderdate) AS o_year,
        l_extendedprice * (1 - l_discount) AS volume,
        n2.n_name AS nation
    FROM
        part,
        supplier,
        lineitem,
        orders,
        customer,
        nation n1,
        nation n2,
        region
    WHERE
        p_partkey = l_partkey
        AND s_suppkey = l_suppkey
        AND l_orderkey = o_orderkey
        AND o_custkey = c_custkey
        AND c_nationkey = n1.n_nationkey
        AND n1.n_regionkey = r_regionkey
        AND r_name = 'AMERICA'
        AND s_nationkey = n2.n_nationkey
        AND o_orderdate BETWEEN CAST('1995-01-01' AS date)
        AND CAST('1996-12-31' AS date)
        AND p_type = 'ECONOMY ANODIZED STEEL') AS all_nations
GROUP BY
    o_year
ORDER BY
    o_year;


-- Q9
SELECT
    nation,
    o_year,
    sum(amount) AS sum_profit
FROM (
    SELECT
        n_name AS nation,
        extract(year FROM o_orderdate) AS o_year,
        l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
    FROM
        part,
        supplier,
        lineitem,
        partsupp,
        orders,
        nation
    WHERE
        s_suppkey = l_suppkey
        AND ps_suppkey = l_suppkey
        AND ps_partkey = l_partkey
        AND p_partkey = l_partkey
        AND o_orderkey = l_orderkey
        AND s_nationkey = n_nationkey
        AND p_name LIKE '%green%') AS profit
GROUP BY
    nation,
    o_year
ORDER BY
    nation,
    o_year DESC;


-- Q10
SELECT
    c_custkey,
    c_name,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
FROM
    customer,
    orders,
    lineitem,
    nation
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate >= CAST('1993-10-01' AS date)
    AND o_orderdate < CAST('1994-01-01' AS date)
    AND l_returnflag = 'R'
    AND c_nationkey = n_nationkey
GROUP BY
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
ORDER BY
    revenue DESC
LIMIT 20;


-- Q11
SELECT
    ps_partkey,
    sum(ps_supplycost * ps_availqty) AS value
FROM
    partsupp,
    supplier,
    nation
WHERE
    ps_suppkey = s_suppkey
    AND s_nationkey = n_nationkey
    AND n_name = 'GERMANY'
GROUP BY
    ps_partkey
HAVING
    sum(ps_supplycost * ps_availqty) > (
        SELECT
            sum(ps_supplycost * ps_availqty) * 0.0001000000
        FROM
            partsupp,
            supplier,
            nation
        WHERE
            ps_suppkey = s_suppkey
            AND s_nationkey = n_nationkey
            AND n_name = 'GERMANY')
ORDER BY
    value DESC;


-- Q12
SELECT
    l_shipmode,
    sum(
        CASE WHEN o_orderpriority = '1-URGENT'
            OR o_orderpriority = '2-HIGH' THEN
            1
        ELSE
            0
        END) AS high_line_count,
    sum(
        CASE WHEN o_orderpriority <> '1-URGENT'
            AND o_orderpriority <> '2-HIGH' THEN
            1
        ELSE
            0
        END) AS low_line_count
FROM
    orders,
    lineitem
WHERE
    o_orderkey = l_orderkey
    AND l_shipmode IN ('MAIL', 'SHIP')
    AND l_commitdate < l_receiptdate
    AND l_shipdate < l_commitdate
    AND l_receiptdate >= CAST('1994-01-01' AS date)
    AND l_receiptdate < CAST('1995-01-01' AS date)
GROUP BY
    l_shipmode
ORDER BY
    l_shipmode;


-- Q13
SELECT
    c_count,
    count(*) AS custdist
FROM (
    SELECT
        c_custkey,
        count(o_orderkey)
    FROM
        customer
    LEFT OUTER JOIN orders ON c_custkey = o_custkey
    AND o_comment NOT LIKE '%special%requests%'
GROUP BY
    c_custkey) AS c_orders (c_custkey,
        c_count)
GROUP BY
    c_count
ORDER BY
    custdist DESC,
    c_count DESC;


-- Q14
SELECT
    100.00 * sum(
        CASE WHEN p_type LIKE 'PROMO%' THEN
            l_extendedprice * (1 - l_discount)
        ELSE
            0
        END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
    lineitem,
    part
WHERE
    l_partkey = p_partkey
    AND l_shipdate >= date '1995-09-01'
    AND l_shipdate < CAST('1995-10-01' AS date);


-- Q15
SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
FROM
    supplier,
    (
        SELECT
            l_suppkey AS supplier_no,
            sum(l_extendedprice * (1 - l_discount)) AS total_revenue
        FROM
            lineitem
        WHERE
            l_shipdate >= CAST('1996-01-01' AS date)
            AND l_shipdate < CAST('1996-04-01' AS date)
        GROUP BY
            supplier_no) revenue0
WHERE
    s_suppkey = supplier_no
    AND total_revenue = (
        SELECT
            max(total_revenue)
        FROM (
            SELECT
                l_suppkey AS supplier_no,
                sum(l_extendedprice * (1 - l_discount)) AS total_revenue
            FROM
                lineitem
            WHERE
                l_shipdate >= CAST('1996-01-01' AS date)
                AND l_shipdate < CAST('1996-04-01' AS date)
            GROUP BY
                supplier_no) revenue1)
ORDER BY
    s_suppkey;


-- Q16
SELECT
    p_brand,
    p_type,
    p_size,
    count(DISTINCT ps_suppkey) AS supplier_cnt
FROM
    partsupp,
    part
WHERE
    p_partkey = ps_partkey
    AND p_brand <> 'Brand#45'
    AND p_type NOT LIKE 'MEDIUM POLISHED%'
    AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
    AND ps_suppkey NOT IN (
        SELECT
            s_suppkey
        FROM
            supplier
        WHERE
            s_comment LIKE '%Customer%Complaints%')
GROUP BY
    p_brand,
    p_type,
    p_size
ORDER BY
    supplier_cnt DESC,
    p_brand,
    p_type,
    p_size;


-- Q17
SELECT
    sum(l_extendedprice) / 7.0 AS avg_yearly
FROM
    lineitem,
    part
WHERE
    p_partkey = l_partkey
    AND p_brand = 'Brand#23'
    AND p_container = 'MED BOX'
    AND l_quantity < (
        SELECT
            0.2 * avg(l_quantity)
        FROM
            lineitem
        WHERE
            l_partkey = p_partkey);


-- Q18
SELECT
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
FROM
    customer,
    orders,
    lineitem
WHERE
    o_orderkey IN (
        SELECT
            l_orderkey
        FROM
            lineitem
        GROUP BY
            l_orderkey
        HAVING
            sum(l_quantity) > 300)
    AND c_custkey = o_custkey
    AND o_orderkey = l_orderkey
GROUP BY
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
ORDER BY
    o_totalprice DESC,
    o_orderdate
LIMIT 100;


-- Q19
SELECT
    sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM
    lineitem,
    part
WHERE (p_partkey = l_partkey
    AND p_brand = 'Brand#12'
    AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    AND l_quantity >= 1
    AND l_quantity <= 1 + 10
    AND p_size BETWEEN 1 AND 5
    AND l_shipmode IN ('AIR', 'AIR REG')
    AND l_shipinstruct = 'DELIVER IN PERSON')
    OR (p_partkey = l_partkey
        AND p_brand = 'Brand#23'
        AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        AND l_quantity >= 10
        AND l_quantity <= 10 + 10
        AND p_size BETWEEN 1 AND 10
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON')
    OR (p_partkey = l_partkey
        AND p_brand = 'Brand#34'
        AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        AND l_quantity >= 20
        AND l_quantity <= 20 + 10
        AND p_size BETWEEN 1 AND 15
        AND l_shipmode IN ('AIR', 'AIR REG')
        AND l_shipinstruct = 'DELIVER IN PERSON');


-- Q20
SELECT
    s_name,
    s_address
FROM
    supplier,
    nation
WHERE
    s_suppkey IN (
        SELECT
            ps_suppkey
        FROM
            partsupp
        WHERE
            ps_partkey IN (
                SELECT
                    p_partkey
                FROM
                    part
                WHERE
                    p_name LIKE 'forest%')
                AND ps_availqty > (
                    SELECT
                        0.5 * sum(l_quantity)
                    FROM
                        lineitem
                    WHERE
                        l_partkey = ps_partkey
                        AND l_suppkey = ps_suppkey
                        AND l_shipdate >= CAST('1994-01-01' AS date)
                        AND l_shipdate < CAST('1995-01-01' AS date)))
            AND s_nationkey = n_nationkey
            AND n_name = 'CANADA'
        ORDER BY
            s_name;


-- Q21
SELECT
    s_name,
    count(*) AS numwait
FROM
    supplier,
    lineitem l1,
    orders,
    nation
WHERE
    s_suppkey = l1.l_suppkey
    AND o_orderkey = l1.l_orderkey
    AND o_orderstatus = 'F'
    AND l1.l_receiptdate > l1.l_commitdate
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem l2
        WHERE
            l2.l_orderkey = l1.l_orderkey
            AND l2.l_suppkey <> l1.l_suppkey)
    AND NOT EXISTS (
        SELECT
            *
        FROM
            lineitem l3
        WHERE
            l3.l_orderkey = l1.l_orderkey
            AND l3.l_suppkey <> l1.l_suppkey
            AND l3.l_receiptdate > l3.l_commitdate)
    AND s_nationkey = n_nationkey
    AND n_name = 'SAUDI ARABIA'
GROUP BY
    s_name
ORDER BY
    numwait DESC,
    s_name
LIMIT 100;


-- Q22
SELECT
    cntrycode,
    count(*) AS numcust,
    sum(c_acctbal) AS totacctbal
FROM (
    SELECT
        substring(c_phone FROM 1 FOR 2) AS cntrycode,
        c_acctbal
    FROM
        customer
    WHERE
        substring(c_phone FROM 1 FOR 2) IN ('13', '31', '23', '29', '30', '18', '17')
        AND c_acctbal > (
            SELECT
                avg(c_acctbal)
            FROM
                customer
            WHERE
                c_acctbal > 0.00
                AND substring(c_phone FROM 1 FOR 2) IN ('13', '31', '23', '29', '30', '18', '17'))
            AND NOT EXISTS (
                SELECT
                    *
                FROM
                    orders
                WHERE
                    o_custkey = c_custkey)) AS custsale
GROUP BY
    cntrycode
ORDER BY
    cntrycode;

SQL実行プランの表示

APクエリアクセラレーション機能が有効または無効になった後、EXPLAINステートメントを実行してSQLステートメントの実行計画を表示できます。

次のサンプルコードは、APクエリ高速化機能を有効にした後のSQL文の実行計画の例を示しています。

APクエリアクセラレーション機能の有効化

tpch_10x=# SET rds_duckdb.execution = on;
SET
tpch_10x=# EXPLAIN SELECT
tpch_10x-#     100.00 * sum(
tpch_10x(#         CASE WHEN p_type LIKE 'PROMO%' THEN
tpch_10x(#             l_extendedprice * (1 - l_discount)
tpch_10x(#         ELSE
tpch_10x(#             0
tpch_10x(#         END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
tpch_10x-# FROM
tpch_10x-#     lineitem,
tpch_10x-#     part
tpch_10x-# WHERE
tpch_10x-#     l_partkey = p_partkey
tpch_10x-#     AND l_shipdate >= date '1995-09-01'
tpch_10x-#     AND l_shipdate < CAST('1995-10-01' AS date);
                         QUERY PLAN
------------------------------------------------------------
 Custom Scan (DuckDBNode)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Plan:

 ┌───────────────────────────┐
 │         PROJECTION        │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Projections:       │
 │       promo_revenue       │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │   Estimated Cardinality:  │
 │             1             │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Aggregates:        │
 │          sum(#0)          │
 │          sum(#1)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Projections:       │
 │ CASE  WHEN (prefix(p_type,│
 │    'PROMO')) THEN (CAST(  │
 │ (l_extendedprice * (1.000 │
 │    - CAST(l_discount AS   │
 │     DECIMAL(18,3)))) AS   │
 │   DECIMAL(20,5))) ELSE 0  │
 │         .00000 END        │
 │ (l_extendedprice * (1.000 │
 │    - CAST(l_discount AS   │
 │      DECIMAL(18,3))))     │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │   Estimated Cardinality:  │
 │          6600339          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         HASH_JOIN         │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │         Join Type:        │
 │           INNER           │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Conditions:        ├──────────────┐
 │   l_partkey = p_partkey   │              │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │              │
 │   Estimated Cardinality:  │              │
 │          6600339          │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         SEQ_SCAN          ││         SEQ_SCAN          │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Stringified:       ││        Stringified:       │
 │          lineitem         ││            part           │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Projections:       ││        Projections:       │
 │         l_partkey         ││         p_partkey         │
 │      l_extendedprice      ││           p_type          │
 │         l_discount        ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   Estimated Cardinality:  │
 │          Filters:         ││          2000000          │
 │ l_shipdate>='1995-09-01': ││                           │
 │ :DATE AND l_shipdate<'1995││                           │
 │     -10-01'::DATE AND     ││                           │
 │   l_shipdate IS NOT NULL  ││                           │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
 │   Estimated Cardinality:  ││                           │
 │          11997210         ││                           │
 └───────────────────────────┘└───────────────────────────┘


(71 rows)

次のサンプルコードは、APクエリアクセラレーション機能を無効にした後のSQL文の実行計画の例を示しています。

APクエリアクセラレーション機能の無効化

tpch_10x=# SET rds_duckdb.execution = off;
SET
tpch_10x=# EXPLAIN SELECT
    100.00 * sum(
        CASE WHEN p_type LIKE 'PROMO%' THEN
            l_extendedprice * (1 - l_discount)
        ELSE
            0
        END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
    lineitem,
    part
WHERE
    l_partkey = p_partkey
    AND l_shipdate >= date '1995-09-01'
    AND l_shipdate < CAST('1995-10-01' AS date);
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=1286740.42..1286740.43 rows=1 width=32)
   ->  Gather  (cost=1286739.96..1286740.37 rows=4 width=64)
         Workers Planned: 4
         ->  Partial Aggregate  (cost=1285739.96..1285739.97 rows=1 width=64)
               ->  Parallel Hash Join  (cost=1235166.04..1282419.39 rows=189747 width=33)
                     Hash Cond: (part.p_partkey = lineitem.l_partkey)
                     ->  Parallel Seq Scan on part  (cost=0.00..43232.15 rows=500016 width=29)
                     ->  Parallel Hash  (cost=1233776.40..1233776.40 rows=111171 width=20)
                           ->  Parallel Seq Scan on lineitem  (cost=0.00..1233776.40 rows=111171 width=20)
                                 Filter: ((l_shipdate >= '1995-09-01'::date) AND (l_shipdate < '1995-10-01'::date))
 JIT:
   Functions: 17
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(13 rows)