PostgreSQL で頻繁に使用されるサブ選択を効率的に保存してアクセスするにはどうすればよいですか?

PostgreSQL での頻繁なサブクエリの最適化

PostgreSQL ユーザーは、繰り返しのサブクエリに依存するクエリに頻繁に遭遇し、関連するテーブルからデータを集約することがよくあります。 この記事では、これらの一般的なサブクエリ パターンを管理し、パフォーマンスとコードの可読性を向上させる効率的な方法について説明します。 一般的なシナリオには、外部キー関係に基づいてセカンダリ テーブルから集計データを取得することが含まれます。

ビューのアプローチ: 堅牢なソリューション

サブクエリを疑似列として直接埋め込むことは不可能ですが、PostgreSQL の VIEW 機能は優れた代替手段を提供します。 VIEW は、既存のテーブルとクエリに基づいて仮想テーブルを定義し、データのカスタマイズされたビューを効果的に作成します。これにより、望ましい結果を達成しながら、ベース テーブル構造の変更を回避できます。

生成された列 (PostgreSQL 11 ): パフォーマンスの向上

PostgreSQL 11 以降のバージョンでは、生成された列 (仮想列とも呼ばれます) が提供されます。これらにより、式を使用して値が計算される列を定義でき、サブクエリを埋め込む目的を反映できます。 このアプローチでは、多くの場合、ビューよりも優れたパフォーマンスが得られます。

古いバージョンで生成された列をエミュレートする

PostgreSQL 11 より前のバージョンの場合、または仮想列アプローチが推奨される場合は、ユーザー定義関数で生成された列をシミュレートできます。 この関数はテーブルの行タイプを入力として受け取り、必要な値を計算します。 次に、この関数は疑似列にリンクされ、生成された列と同様の機能を実現します。

実際の実装

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

<code class="language-sql">-- Create tables
CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
CREATE TABLE tbl_b (b_id int, a_id int, colx int);

-- Sample data
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
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);

-- Function to emulate the generated column
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>

次に、col3 疑似列にアクセスします:

<code class="language-sql">SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

SELECT *, a.col3 FROM tbl_a a;</code>

生成列の利点

ビューと比較して、生成された列には次の利点があります:

• シームレスな統合: 明示的に言及することなく SELECT * クエリに含まれます。
• 動的更新: ベーステーブルの変更を自動的に反映します。
• 最適化されたパフォーマンス: PostgreSQL のクエリ オプティマイザーを活用して効率を高めます。

結論

サブクエリの直接埋め込みはサポートされていませんが、PostgreSQL は頻繁に使用されるサブクエリを管理するための効果的な代替手段 (ビューと生成された列) を提供します。 これらの方法により、クエリのパフォーマンスとコードの明瞭さが向上し、より効率的なデータベース管理が可能になります。 PostgreSQL のバージョンとパフォーマンス要件に最も適したアプローチを選択してください。

以上がPostgreSQL で頻繁に使用されるサブ選択を効率的に保存してアクセスするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。