PostgreSQL で計算列を効率的に生成してクエリを高速化するにはどうすればよいですか?

計算列を使用した PostgreSQL クエリの最適化

複数のテーブルからの派生値に頻繁にアクセスするクエリの場合、ターゲット テーブル内に計算列を作成するとパフォーマンスが大幅に向上します。 PostgreSQL は、これを実現するためのいくつかの方法を提供します。

ビューはクエリの論理表現を提供しますが、データを物理的に保存しないため、パフォーマンスが重要なシナリオには理想的ではありません。

優れたソリューションは、PostgreSQL の「生成された列」 (バージョン 11 以降で利用可能) です。 STORED 生成された列は、計算された値をテーブル内に直接保存し、従来の列と同じパフォーマンスを提供します。

古い PostgreSQL バージョンや、仮想生成列が適さない状況の場合は、関数を使用して計算列をシミュレートできます。 これらの関数はテーブル タイプを入力として受け入れ、計算された値を返し、実質的に疑似列として機能します。

例で説明してみましょう:

<code class="language-sql">CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);

CREATE TABLE tbl_b (b_id int, a_id int, colx int);
INSERT INTO tbl_b VALUES
  (1,1,5),  (2,1,5),  (3,1,1)
, (4,2,8),  (5,2,8),  (6,2,6)
, (7,3,11), (8,3,11), (9,3,11);</code>

関数を使用して疑似列 col3 を作成するには:

<code class="language-sql">CREATE FUNCTION col3(tbl_a)
  RETURNS int8
  LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = .a_id
$func$;</code>

属性表記 (tbl_a.col3) または関数表記 (col3(tbl_a)) を使用して計算値にアクセスし、クエリの柔軟性を提供します。 名前の競合を防ぐために、テーブルの別名を必ず使用してください。

計算列または生成関数を使用すると、サブクエリが最小限に抑えられ、データ取得が合理化され、クエリ速度が大幅に向上します。 最適なアプローチは、特定のニーズと PostgreSQL のバージョンによって異なります。 慎重に検討することで、効率的なデータベース設計が保証されます。

以上がPostgreSQL で計算列を効率的に生成してクエリを高速化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。