PostgresSQLでテーブル、項目を別名で参照したい

アイキャッチ画像（公園） システム開発全般
アイキャッチ画像（公園）

はじめに

アプリケーションから物理テーブル名、列名と異なる名前でDBを参照したい要件があり、View機能を使って実現可能かを確認。機能的には実現できるが、パフォーマンス観点で物理テーブルを参照した時と劣化することがないか？が確認ポイント。
・不要な列が読み込まれないか？
・適切なスキャンが行われるか？

前提事項

・アプリケーションからは参照のみ。更新などは行わない
・View定義は別名参照目的のため、View内にサブクエリなどは記載しない。

View参照時の動きを確認①（JOINの場合）

物理テーブル定義

User, Companyテーブルをサンプルとして定義。

-- Userテーブル
CREATE TABLE "User" (
  user_id     INTEGER PRIMARY KEY,
  user_name   TEXT,
  company_id  INTEGER
);

-- Companyテーブル
CREATE TABLE "Company" (
  company_id   INTEGER PRIMARY KEY,
  company_name TEXT
);

View定義

別名定義のみのシンプルなViewを作成。項目名には「view_」をプリフィックス付与。

CREATE OR REPLACE VIEW ViewUser AS
SELECT
  user_id      AS view_user_id,
  user_name    AS view_user_name,
  company_id   AS view_company_id
FROM "User";

CREATE OR REPLACE VIEW ViewCompany AS
SELECT
  company_id   AS view_company_id,
  company_name AS view_company_name
FROM "Company";

アプリケーションのSQL

View名でJOIN利用。

SELECT
  u.view_user_id,
  u.view_user_name,
  c.view_company_name
FROM ViewUser u
INNER JOIN ViewCompany c
  ON u.view_company_id = c.view_company_id
WHERE u.view_user_id = 101;

オプティマイザの展開イメージ

ステップ１：Viewをサブクエリとして展開

SELECT
  u.view_user_id,
  u.view_user_name,
  c.view_company_name
FROM (
  SELECT user_id AS view_user_id, user_name AS view_user_name, company_id AS view_company_id
  FROM "User"
) u
INNER JOIN (
  SELECT company_id AS view_company_id, company_name AS view_company_name
  FROM "Company"
) c
  ON u.view_company_id = c.view_company_id
WHERE u.view_user_id = 101;

ステップ２：最適化
・WHERE句、JOIN条件を最適化し、物理テーブルのフィルタ、JOIN条件として計画
・必要カラムのみ取得するよう計画

ステップ３：最適化後のイメージ
実行計画でサブクエリ部分は消え、直で物理テーブルアクセス・JOIN・WHEREになります

SELECT
  u.user_id AS view_user_id,
  u.user_name AS view_user_name,
  c.company_name AS view_company_name
FROM "User" u
INNER JOIN "Company" c
  ON u.company_id = c.company_id
WHERE u.user_id = 101;

実行統計(EXPLAIN ANALYZE）の確認

1.1. view参照の確認

    EXPLAIN ANALYZE
SELECT
  u.view_user_id,
  u.view_user_name,
  c.view_company_name
FROM ViewUser u
INNER JOIN ViewCompany c
  ON u.view_company_id = c.view_company_id
WHERE u.view_user_id = 101;

    1.2 View参照の実行統計

    "QUERY PLAN"
"Nested Loop  (cost=0.30..16.35 rows=1 width=68) (actual time=0.011..0.012 rows=1 loops=1)"
"  ->  Index Scan using ""User_pkey"" on ""User""  (cost=0.15..8.17 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=1)"
"        Index Cond: (user_id = 101)"
"  ->  Index Scan using ""Company_pkey"" on ""Company""  (cost=0.15..8.17 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=1)"
"        Index Cond: (company_id = ""User"".company_id)"
"Planning Time: 0.325 ms"
"Execution Time: 0.040 ms"

    2.1 物理名参照の確認

    EXPLAIN ANALYZE
SELECT
  u.user_id      AS view_user_id,
  u.user_name    AS view_user_name,
  c.company_name AS view_company_name
FROM "User" u
INNER JOIN "Company" c
  ON u.company_id = c.company_id
WHERE u.user_id = 101;

    2.2 物理名参照の実行統計

    "QUERY PLAN"
"Nested Loop  (cost=0.30..16.35 rows=1 width=68) (actual time=0.046..0.048 rows=1 loops=1)"
"  ->  Index Scan using ""User_pkey"" on ""User"" u  (cost=0.15..8.17 rows=1 width=40) (actual time=0.036..0.037 rows=1 loops=1)"
"        Index Cond: (user_id = 101)"
"  ->  Index Scan using ""Company_pkey"" on ""Company"" c  (cost=0.15..8.17 rows=1 width=36) (actual time=0.007..0.007 rows=1 loops=1)"
"        Index Cond: (company_id = u.company_id)"
"Planning Time: 0.205 ms"
"Execution Time: 0.085 ms"

    3.1. 実行統計の比較
    View参照の場合と物理名参照時の比較

    参照方式JOIN手法主キーインデックス利用実行時間実行される内容
    View参照Nested Loop
    ※1    		両テーブルで利用~0.040 msUser/Companyインデックス
    スキャン＋意図通りのJOIN
    ※２
    物理テーブル直参照Nested Loop両テーブルで利用~0.085 msUser/Companyインデックス
    スキャン＋意図通りのJOIN

    ※1 Nested Loop Join（ネストループ結合）
    1つ目のテーブルのレコードを1件ずつ取り出し、それぞれに対して2つ目のテーブルの該当レコードを検索する
    ※2 意図通りのJOIN
    　 実行統計では「Index Cond: (company_id = u.company_id) 」の部分。

    View参照時の動きを確認②（複雑なサブクエリ）

    アプリ側で複雑なサブクエリを記述した場合の動きを確認します。

    実行統計(EXPLAIN ANALYZE）の確認

    1.1. View参照時の確認

    EXPLAIN ANALYZE
SELECT
  v.view_user_id,
  v.view_user_name,
  (
    SELECT COUNT(*)
    FROM ViewCompany vc
    WHERE vc.view_company_id = v.view_company_id
      AND LENGTH(vc.view_company_name) > 5
  ) as long_company_count
FROM ViewUser v
WHERE v.view_user_id IN (
  SELECT view_user_id FROM ViewUser WHERE view_user_name LIKE 'A%'
)
ORDER BY long_company_count DESC;

    1.2 View参照の実行統計

    "QUERY PLAN"
"Sort  (cost=99.44..99.45 rows=6 width=44) (actual time=0.257..0.258 rows=1 loops=1)"
"  Sort Key: ((SubPlan 1)) DESC"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Hash Join  (cost=25.07..99.36 rows=6 width=44) (actual time=0.243..0.245 rows=1 loops=1)"
"        Hash Cond: (""User"".user_id = ""User_1"".user_id)"
"        ->  Seq Scan on ""User""  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.015..0.015 rows=3 loops=1)"
"        ->  Hash  (cost=25.00..25.00 rows=6 width=4) (actual time=0.151..0.151 rows=1 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"              ->  Seq Scan on ""User"" ""User_1""  (cost=0.00..25.00 rows=6 width=4) (actual time=0.142..0.144 rows=1 loops=1)"
"                    Filter: (user_name ~~ 'A%'::text)"
"                    Rows Removed by Filter: 2"
"        SubPlan 1"
"          ->  Aggregate  (cost=8.18..8.19 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)"
"                ->  Index Scan using ""Company_pkey"" on ""Company""  (cost=0.15..8.18 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=1)"
"                      Index Cond: (company_id = ""User"".company_id)"
"                      Filter: (length(company_name) > 5)"
"Planning Time: 0.322 ms"
"Execution Time: 0.318 ms"

    2.1. 物理名参照時の確認

    EXPLAIN ANALYZE
SELECT
  u.user_id,
  u.user_name,
  (
    SELECT COUNT(*)
    FROM "Company" c
    WHERE c.company_id = u.company_id
      AND LENGTH(c.company_name) > 5
  ) as long_company_count
FROM "User" u
WHERE u.user_id IN (
  SELECT user_id FROM "User" WHERE user_name LIKE 'A%'
)
ORDER BY long_company_count DESC;

    2.2. 物理名参照の実行統計

    "QUERY PLAN"
"Sort  (cost=99.44..99.45 rows=6 width=44) (actual time=0.089..0.090 rows=1 loops=1)"
"  Sort Key: ((SubPlan 1)) DESC"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Hash Join  (cost=25.07..99.36 rows=6 width=44) (actual time=0.081..0.083 rows=1 loops=1)"
"        Hash Cond: (u.user_id = ""User"".user_id)"
"        ->  Seq Scan on ""User"" u  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.023..0.023 rows=3 loops=1)"
"        ->  Hash  (cost=25.00..25.00 rows=6 width=4) (actual time=0.019..0.019 rows=1 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"              ->  Seq Scan on ""User""  (cost=0.00..25.00 rows=6 width=4) (actual time=0.013..0.015 rows=1 loops=1)"
"                    Filter: (user_name ~~ 'A%'::text)"
"                    Rows Removed by Filter: 2"
"        SubPlan 1"
"          ->  Aggregate  (cost=8.18..8.19 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)"
"                ->  Index Scan using ""Company_pkey"" on ""Company"" c  (cost=0.15..8.18 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1)"
"                      Index Cond: (company_id = u.company_id)"
"                      Filter: (length(company_name) > 5)"
"Planning Time: 0.358 ms"
"Execution Time: 0.146 ms"
    比較項目View参照物理テーブル直参照
    ソート方法Sort (quicksort)・Sort Key: ((SubPlan 1)) DESCSort (quicksort)・Sort Key: ((SubPlan 1)) DESC
    JOIN手法Hash Join, Hash Cond: (“User”.user_id = “User_1”.user_id)Hash Join, Hash Cond: (u.user_id = “User”.user_id)
    ユーザデータ取得Seq Scan on “User”、そして Filter: (user_name ~~ ‘A%’::text)Seq Scan on “User” u、そして Filter: (user_name ~~ ‘A%’::text)
    サブクエリ集計SubPlan 1 → Aggregate → Index Scan using “Company_pkey” on “Company”SubPlan 1 → Aggregate → Index Scan using “Company_pkey” on “Company” c
    集計・フィルタ内容Index Cond: (company_id = “User”.company_id)、Filter: (length(company_name) > 5)Index Cond: (company_id = u.company_id)、Filter: (length(company_name) > 5)
    行数・バケットrows=1 loops=1、Buckets: 1024 Batches: 1 Memory Usage: 9kBrows=1 loops=1、Buckets: 1024 Batches: 1 Memory Usage: 9kB

    その他補足

    ・アプリ側でGROUP BY/ORDER BY/DISTINCT/ウィンドウ関数を使う場合でも、「外側（View/サブクエリから出た後のSQL）」でまとめて評価されるのでView参照、物理名参照の違いはない。

    以上

    タイトルとURLをコピーしました