列グループ統計 - AnalyticDB - Alibaba Cloud ドキュメントセンター

AnalyticDB for PostgreSQL V7.0は、複数の列の統計を収集できる列グループ統計機能を提供します。この特徴は、コスト推定のための独立した分配の仮定を破る。したがって、コスト推定の精度を高め、クエリのパフォーマンスを向上させることができます。

注意事項

列グループ統計機能は、プランナーオプティマイザ (旧称レガシオプティマイザ) のみをサポートします。オプティマイザの設定方法の詳細については、「パラメーターの設定」をご参照ください。

構文

CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
    [ ( statistics_kind [, ... ] ) ]
    ON column_name, column_name [, ...]
    FROM table_name

パラメーター

パラメーター	説明
statistics_name	列グループの統計アイテムの名前。
statistics_kind	収集する統計のタイプ。有効な値： ndistinct: 複数の列間の異なる値の数。 dependencies: 複数の列間の関数依存関係。 mcv: 複数の列の中で最も頻繁に発生する値と値の発生頻度。このパラメーターが指定されていない場合、前述のタイプの統計がすべて収集されます。
column_name	列グループ統計に含まれる列の名前。少なくとも2つの列を指定する必要があります。
table_name	前の列を含むテーブルの名前。

例

ndistinctという名前のテーブルを作成し、テーブルにデータを挿入します。
```
CREATE TABLE ndistinct(a int, b int, c int, d int) DISTRIBUTED BY (d);
INSERT INTO ndistinct(a, b) SELECT i/100, i/200, i/100, i FROM generate_series(1, 10000000) i;
```
列aおよびbの値は強く依存しており、これは独立分布の仮定に反する。

推定行数と実際の行数を計算する関数を作成します。

CREATE FUNCTION check_estimated_rows(text) returns table (estimated int, actual int)
LANGUAGE plpgsql AS
$$
DECLARE
    ln text;
    tmp text[];
    first_row bool := true;
BEGIN
    FOR ln in
        execute format('explain analyze %s', $1)
    LOOP
        IF first_row then
            first_row := false;
            tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)');
            return query select tmp[1]::int, tmp[2]::int;
        END IF;
    END LOOP;
END;
$$;

推定行数と実際の行数を照会します。
```
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
```
次の情報が返されます。
推定
```
 estimated | actual
-----------+--------
   1000000 | 100001
(1 row)
```
この例では、推定行数は1000000であり、実際の行数は100001である。大きな違いがあります。

実行プランを照会します。

EXPLAIN ANALYZE SELECT count(*) FROM ndistinct GROUP BY a,b,c;

次の実行プランが返されます。列グループ統計アイテムが作成された後の実行計画との比較に使用できます。

                                                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=137814.33..154481.00 rows=1000000 width=20) (actual time=3986.143..4014.601 rows=100001 loops=1)
   ->  HashAggregate  (cost=137814.33..141147.67 rows=333333 width=20) (actual time=3977.037..4002.222 rows=33661 loops=1)
         Group Key: a, b, c
         Peak Memory Usage: 0 kB
         ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..104481.00 rows=3333333 width=12) (actual time=0.079..1492.983 rows=3366100 loops=1)
               Hash Key: a, b, c
               ->  Seq Scan on ndistinct  (cost=0.00..37814.33 rows=3333333 width=12) (actual time=0.050..1101.632 rows=3334839 loops=1)
 Planning Time: 0.161 ms
   (slice0)    Executor memory: 12336K bytes.
   (slice1)    Executor memory: 13884K bytes avg x 3 workers, 13899K bytes max (seg0).  Work_mem: 16401K bytes max.
   (slice2)    Executor memory: 37K bytes avg x 3 workers, 37K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 4041.613 ms
(14 rows)

列グループ統計項目を作成し、ANALYZEステートメントを実行して統計を収集します。
```
CREATE STATISTICS s_a_b(ndistinct) ON a,b FROM ndistinct;
ANALYZE ndistinct;
```
推定行数と実際の行数を再度照会します。
```
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
```
次の情報が返されます。
推定
```
 estimated | actual
-----------+--------
     99431 | 100001
(1 row)
```
この例では、推定行数は99431であり、実際の行数は100001である。列グループ統計項目が作成された後、推定行数はより正確になります。

実行プランを照会します。

EXPLAIN ANALYZE SELECT count(*) FROM ndistinct GROUP BY a,b,c;

次の実行プランが返されます。実行計画は、推定がより正確であるため、クエリの実行を高速化するためにPartial HashAggregateが生成されることを示しています。

                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=75124.91..76782.09 rows=99431 width=20) (actual time=2854.765..2879.734 rows=100001 loops=1)
   ->  Finalize HashAggregate  (cost=75124.91..75456.34 rows=33144 width=20) (actual time=2853.610..2868.194 rows=33661 loops=1)
         Group Key: a, b, c
         Peak Memory Usage: 0 kB
         ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=71147.67..74130.60 rows=99431 width=20) (actual time=2269.435..2759.413 rows=100983 loops=1)
               Hash Key: a, b, c
               ->  Partial HashAggregate  (cost=71147.67..72141.98 rows=99431 width=20) (actual time=2744.039..2794.808 rows=100001 loops=1)
                     Group Key: a, b, c
                     Peak Memory Usage: 0 kB
                     ->  Seq Scan on ndistinct  (cost=0.00..37814.33 rows=3333333 width=12) (actual time=0.028..454.030 rows=3334839 loops=1)
 Planning Time: 0.173 ms
   (slice0)    Executor memory: 4670K bytes.
   (slice1)    Executor memory: 3134K bytes avg x 3 workers, 3149K bytes max (seg0).  Work_mem: 5649K bytes max.
   (slice2)    Executor memory: 13848K bytes avg x 3 workers, 13848K bytes max (seg0).  Work_mem: 14353K bytes max.
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 2893.470 ms
(17 rows)