クイズ：Postgres深掘り第2部

あなたはSQLの鉄人か？それとも、ただのSQL初心者か？

Part 2 of 2. パート1に戻る

PostgreSQL深掘りクイズのパート2へようこそ！

このクイズでは、JOIN、NULL、ANSI SQL、トランザクション、内部構造、インデックス、データサンプリングなど、PostgreSQLの定番機能から意外と知られていない落とし穴まで幅広くカバーしています。

健闘を祈ります！ 🍀

ウォームアップ：結合（Join）

PostgreSQLの有効なJOIN式ではない ❌ ものはどれ？

CROSS JOIN は知っていたかな？

JOIN ALL を見て、別の操作である UNION ALL を連想しなかっただろうか？

ここでの正解は JOIN ALL ❌ だ。

正しいJOINの種類は以下の通り：

INNER JOIN (デフォルトの JOIN)
LEFT JOIN (または LEFT OUTER JOIN)
RIGHT JOIN (または RIGHT OUTER JOIN)
FULL JOIN (または FULL OUTER JOIN)
CROSS JOIN (デカルト積。ただし、後続の WHERE 句でフィルタリングされない限り)

JOIN ALL

INNER JOIN

CROSS JOIN

LEFT OUTER JOIN

FULL OUTER JOIN

アイデンティティ列

PostgreSQL (v10以降) において、自動インクリメントを行う IDENTITY 列の（SQL標準）構文は何でしょうか？

SERIAL は今でも一般的に使われていますが、SQL標準に準拠した書き方は以下の通りです：

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

id INT IDENTITY(1,1) は SQL Server の構文です。
id INT AUTO_INCREMENT は MySQL の構文です。
id INTEGER AUTO_INCREMENT PRIMARY KEY は MySQL の構文です。

この標準構文には SERIAL と比較して、いくつかの利点があります：

SQL標準に準拠している
シーケンスの所有権の扱いが優れている
手動挿入に関するセマンティクスが明確である
レプリケーションとの相性が良い

実は SERIAL は、内部的にシーケンスを作成してデフォルト値を設定するための単なるショートハンドに過ぎません。そのため、ダンプやリストアの際に問題が発生する可能性があります。

id SERIAL PRIMARY KEY

id INT IDENTITY(1,1)

id INT AUTO_INCREMENT

id NUMBER GENERATED ALWAYS AS IDENTITY

id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

ANSI SQL

PostgreSQLの文書で、標準SQLへの拡張として説明されている句はどれですか？

PostgreSQLでは RETURNING 句を標準SQLへの拡張として説明しています。ILIKE もPostgreSQLの拡張です。

配列やJSONの機能をすべて非標準と分類するのは不正確です。SQL標準にもこれらの機能があり、PostgreSQLには jsonb など独自の型や演算子もあります。

SQL標準の各版では、WITH（共通テーブル式）、LIKE、LATERAL 結合、ウィンドウ関数などが導入されてきました。

INTO

WITH

LATERAL

RETURNING

トランザクション

「サイドエフェクトに依存する」以外の場合、エラーが発生した後に何が起こるか？

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

ここでエラーが発生した場合：

トランザクション全体がロールバックされる
変更は一切コミットされない
トランザクションは失敗としてマークされる

部分的にコミットするには：

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

サイドエフェクトに依存する

すべてのステートメントをロールバックする

最初のステートメントのみコミットする

成功したすべてのステートメントをコミットする

SQLの内部構造

PostgreSQLにおいて、HASH JOINを使用できるのはどのON条件ですか？

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

ハッシュ結合（hash join）は、ON句に等価条件（=）がある場合に実行可能です。

= はハッシュ結合における主要な述語の形式です
> や >= はハッシュ結合の述語にはなりません
HASH JOIN という構文はPostgreSQLでは無効です

なお、テーブルのサイズ、統計情報、インデックス、およびメモリ設定によっては、プランナがネステッドループ結合やマージ結合を選択する場合もあります。

ON a.id = b.id

ON a.id > b.id

ON a.id >= b.id

HASH JOIN ON a.id = b.id ...

インデックス

このクエリに最適なインデックスはどれですか？

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

驚くべきことに、クエリ内でのカラムの記述順序がインデックスの定義順序と一致していなくても、マルチカラムインデックスは有効に機能します。

注意: B-treeインデックスでは、先頭のカラムが重要です。このクエリは grade_level と last_name でフィルタリングしているため、grade_level で始まり、次に last_name が続くインデックスが最適です。

SQLがいかに早く行を絞り込めるかが、パフォーマンス向上の鍵となります。

例えば、task テーブルにおいて、status の方が created_at よりも選択性が高い（行をより絞り込める）ことがわかっている場合、status = 'done' AND created_at > '2024-01-01' というクエリに対しては status から始まるインデックスを選択するのが賢明です。重要なのは WHERE 句の記述順序ではなく、インデックスのカラム順序と述語の形式です。

B+Treeインデックスは以下のケースで使用可能です：

先頭のカラム、またはカラムの左側プレフィックス（左端からの連続した列）。
クエリの形状に応じた =、>、<、BETWEEN、IN などの演算子。
前方一致検索、LIKE 'prefix%'。

この解説の以前の誤りを指摘してくれた u/mwdb2 に感謝します！

CREATE INDEX ON students(grade_level, last_name);

CREATE INDEX ON students(grade_level);

CREATE INDEX ON students(last_name, age);

CREATE INDEX ON students(first_name, last_name, grade_level);

クォーティング 1

大文字小文字を区別するカラム名には、どちらが正しいですか？

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

PostgreSQLにおいて:

ダブルクォート (") は識別子（テーブル名、カラム名）に使用します
シングルクォート (') は文字列リテラルに使用します

ベストプラクティス:

名前の中で大文字小文字を混ぜたり、スペースを使用したりするのは避けましょう
識別子には snake_case を使用しましょう
引用符付きの識別子は、どうしても必要な場合にのみ使用しましょう

どちらも同じように動作する

ダブルクォートが必要

シングルクォートが必要

構文エラー: 無効なユーザーリテラル

すべての名前は小文字でなければならない

クォーティング 2

ピリオドを含むカラム名を指定するにはどうすればよいでしょうか？

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQLでは、識別子のクォーティングに二重引用符（ダブルクォート）を使用します：

識別子（カラム名、テーブル名）には二重引用符 "..." を使用
文字列リテラルには単一引用符（シングルクォート） '...' を使用
first.name という形式は、通常 table_name.column_name と解釈されます

クォートがない場合、カラム名に含まれるピリオドはスキーマやテーブルの区切り文字として解釈されてしまいます！さらに、クォートされていない識別子はデフォルトで小文字に変換されます。

予期せぬトラブルを避けるため、識別子には snake_case を使用し、特殊文字の使用は控えるのが最善です。

first.name

"first.name"

'first.name'

[first.name]

[first].[name]

データサンプリング

学生の 10% をランダムにサンプリング するための有効な構文はどれですか？

これはかなり意地悪な問題です！約6つの罠が仕掛けられています。例えば：

高レベルな説明と構文の断片。不完全な情報をいかに適応させ、組み立てるかをテストしています。
TABLESAMPLE の構文に関する正確な知識が必要です。提示された TABLESAMPLE の選択肢の中に正しい構文は一つもありません！（邪悪でしょう？わかっています！でも諦めないで、あなたならできるはず！！！ ❤️）
ROW_NUMBER() OVER (ORDER BY RAND()) と ORDER BY RANDOM() > 10 はさらなる「赤にしん（おとり）」です。正解っぽく見えますが、違います。そもそも RAND() という関数は存在しません。また、RANDOM() は常に 0.0 から 1.0 の範囲の値を返すため、RANDOM() > 10 というブール式は常に偽になります。もう一つの問題として、ORDER BY RANDOM() > 10 はランダムな数値ではなくブール値でソートしてしまいます。しかし、この > 10 という数字は、いかにもパーセンテージっぽく見えましたよね？
BERNOULLI は有効ですが、SAMPLETABLE は無効です。
WHERE RANDOM() >= 0.1 はひっかけです！RANDOM() は 0.0 <= x < 1.0 の範囲の値を返すため、>= 0.1 とすると行の約 90% が返されてしまい、10% にはなりません。

TABLESAMPLE メソッドに関して、PostgreSQL は以下の2つのサンプリング方式をサポートしています：

BERNOULLI: 各行が等しい確率を持つ
SYSTEM: ブロックレベルのサンプリング（高速だがランダム性は低い）

サンプリングについて詳しく知りたい場合は、Render のこの記事をチェックしてください。

例：

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);

-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

また、TABLESAMPLE はあまりランダムではないという報告もあります。もし、より精度の高いランダムな固定サイズのサンプルが必要な場合は、ORDER BY RANDOM() LIMIT 10 の使用を検討してください。ただし、これは 10% ではなく「10行」を返すこと、そして巨大なテーブルでは低速になる可能性があることに注意してください。

より優れたランダムサンプリング手法を提供するサードパーティ製の拡張機能もありますが、それは今回の範囲外です！ 😅

...students SAMPLETABLE BERNOULLI (0.10)

...students TABLESAMPLE USER (10)

...students TABLESAMPLE RANDOM (10)

...students SAMPLE 10 PERCENT

ROW_NUMBER() OVER (ORDER BY RAND())

ORDER BY RANDOM() >= 10

WHERE RANDOM() >= 0.1

WHERE RANDOM() >= 0.9

10.

インデックス

この呪文は何を実行するもの？

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

このテクニックは Partial Index（部分インデックス）と呼ばれる。

通常、行の特定のサブセットのみがクエリの対象となる場合に、ディスク使用量を削減し、クエリを高速化するのに役立つ。

プランナが、クエリの条件から status = 'active' が成り立つと証明できる場合に、このインデックスを候補にできます。完全な文字列一致は必須ではありませんが、推論には限界があり、パラメータ化された条件では計画時に証明できない場合があります。候補になっても、実際に選択されるとは限りません。

注意: Postgresは他のRDBMSのような「クエリヒント」をサポートしていない。クエリプランナが、適切なインデックスを使用するかどうかを自律的に判断する。

構文エラー

重複したインデックスを作成する

アクティブな生徒のみをインデックスする

アクティブな生徒を優先的にインデックスする

クエリプランナにヒントを提供する

11.

NULL の振る舞い

このクエリは何行を返しますか？

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

さて、これは少し意地悪な問題でした。落とし穴は、x = NULL はどの行にも決して一致せず、常にカウントが 0 である 1 行を返すという点です。

NULL は「未知の値」であるため、他の何とも比較できず、自分自身とさえ比較できません。

NULL 値を正しくチェックする方法は IS NULL を使うことです。

例:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)

postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

100

12.

パフォーマンス

UPDATE 文に対して EXPLAIN ANALYZE を実行するとどうなりますか？

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE は実際にクエリを実行します！書き込み操作、特に UPDATE、DELETE、INSERT 文を分析する際は注意が必要です。再現性のある結果を得るために、EXPLAIN ANALYZE を実行するたびにクリーンアップ操作が必要になる場合があります。

ベストプラクティス:

UPDATE/DELETE/INSERT には（ANALYZE なしの）EXPLAIN を使用する
変更操作をトランザクションで囲む:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

簡易プランの見積もり

SELECT 文でのみ動作する

データを更新し、実行計画を出力する

実行をシミュレートして実行計画を取得する

お見事！PostgreSQLの様々な領域を深く掘り下げましたね。🐘

何か新しい学びがあったか、少なくとも自慢できるようなスコアが得られたなら幸いです！🏆

さらなる刺激を求めていますか？クイズコレクションで、終わりのない*楽しみを！

クイズ：Postgres深掘り 第2部

Share Stats

クイズ：Postgres深掘り第2部