クイズ：Postgres深掘り 第2部

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 句でフィルタリングされない限り)

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

ANSI SQLに対するPostgreSQL独自の拡張機能の例：

• RETURNING 句
• 大文字小文字を区別しないマッチング用の ILIKE
• 配列型とその操作
• JSON/JSONB型

オリジナルのANSI SQL標準に含まれるもの：

• WITH (共通テーブル式)
• パターンマッチング用の LIKE
• LATERAL ジョイン
• ウィンドウ関数

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

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

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

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

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

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

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

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

注意: 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 に感謝します！

PostgreSQLにおいて:

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

ベストプラクティス:

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

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

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

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

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

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

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

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

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

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

Postgresは、クエリ内に status = 'active' という条件が存在する場合にのみ、このインデックスを使用する。

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

さて、これは少し意地悪な問題でした。落とし穴は、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)

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

ベストプラクティス:

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

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;