← 2026-01-21 の一覧へ戻るホーム
**従来にとらわれない PostgreSQL の最適化**

2026/01/20 23:23

**従来にとらわれない PostgreSQL の最適化**

RSS: https://news.ycombinator.com/rss

元のHacker News記事へ ↗
要約▶

Japanese Translation:

PostgreSQLは、インデックスサイズを削減しクエリ速度を向上させるためにいくつかの方法を提供します：

  • constraint_exclusion
    – クエリの述語がテーブルのチェック制約と一致できない場合、プランナーはそのパーティションまたはテーブルのスキャンをスキップできます。 デフォルトでは 
    "partition"
    に設定されていますが、BI／レポートワークロードでアドホッククエリに不可能な値（例：
    CHECK (plan IN ('free','pro'))
    のあるテーブルで 
    plan = 'Pro'
    ）が含まれる場合は 
    "on"
    に変更すると便利です。

  • 関数ベースのインデックス – 

    date_trunc('day', sold_at AT TIME ZONE 'UTC')::date
    などの式をインデックス化することで、214 MB の B‑Tree インデックスを 66 MB に縮小し、クエリ時間を約 627 ms から約 145 ms に短縮しました。ただし、このインデックスは脆弱であり、その式に変更があればロジックがビューまたは仮想生成列で強制されない限り使用できなくなります。

  • 仮想生成列（PostgreSQL 18） – 

    ALTER TABLE sale ADD sold_at_date DATE GENERATED ALWAYS AS (…)
    はデータを保存せず、同じ式を一貫してインデックス化することができます。 仮想列に対するインデックスはまだサポートされていないため、メリットは重複ストレージの回避と整合性の確保に限定されます。

  • ハッシュベースの除外制約 – 大きなテキスト列（

    urls.url
    ）に対する標準的な一意制約は約 154 MB の B‑Tree インデックスを作成します。これを 
    ALTER TABLE urls ADD CONSTRAINT urls_url_unique_hash EXCLUDE USING HASH (url WITH =)`` に置き換えると、約 32 MB のハッシュインデックスが生成され、まだ一意性が保証されます。 制限点としては、外部キーで参照できないこと、
    ON CONFLICT (url) DO NOTHING
    が
    ON CONFLICT ON CONSTRAINT
     を使用する必要があること、
    DO UPDATE
    はサポートされていないことがあります。 回避策としては
    ON CONFLICT ON CONSTRAINT
    の利用や、upsert 用に予定されている
    MERGE` 文の使用があります。

影響：

  • ユーザーはレポーティングシナリオで少ないバイト数を保存し、大きな文字列列で一意性を強制する際に高速クエリを実行できます。
  • 開発者は関数ベースの式を安定させる（または仮想生成列を使用する）よう自律的に管理し、ハッシュ除外制約の制限を認識して適切な競合解決手法を採用する必要があります。

本文

データベース最適化 – PostgreSQL における非定番テクニック

1. チェック制約を活用してフルテーブルスキャンを排除

シナリオ

以下のように、

plan
列が 
free
または 
pro
のみを許容するチェック制約を持つテーブルがあります。

CREATE TABLE users (
    id INT PRIMARY KEY,
    username TEXT NOT NULL,
    plan TEXT NOT NULL,
    CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))
);

あるユーザーが誤って大文字の 

'Pro'
を検索すると、PostgreSQL は自動的に制約を利用して行を除外できません。

EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';
-- Seq Scan on users (cost=0..2185)

解決策 – 
constraint_exclusion

constraint_exclusion
を有効にすると、PostgreSQL は条件を満たせないテーブル（あるいはパーティション）をスキップできます。

SET constraint_exclusion TO 'on';

EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';
-- Result (cost=0..0) – no rows, no scan

いつ使うべきか

  • BI ツールやレポート作成時にアドホックでクエリが実行される場合
  • 入力ミス（大文字・小文字の混在など）が想定される環境
  • 不要なフルテーブルスキャンを防ぎたいとき

2. 関数ベースインデックスで低カード性に最適化

シナリオ

数百万行からなる販売テーブルがあります。

CREATE TABLE sale (
    id INT PRIMARY KEY,
    sold_at TIMESTAMPTZ NOT NULL,
    charged INT NOT NULL
);

アナリストは次のような日次レポートを実行します。

SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged)
FROM sale
WHERE '2025-01-01 UTC' <= sold_at
  AND sold_at < '2025-02-01 UTC'
GROUP BY 1;

悪いアプローチ – 
sold_at
に対する B‑Tree

CREATE INDEX sale_sold_at_ix ON sale(sold_at);
-- サイズ: 約214 MB（テーブルのほぼ半分）

性能は向上しますが、ストレージコストが大きくなります。

良いアプローチ – 日付に対する関数ベースインデックス

CREATE INDEX sale_sold_at_date_ix 
    ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);
-- サイズ: 約66 MB

クエリを次のように書き換え、関数ベースインデックスを利用します。

SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged)
FROM sale
WHERE date_trunc('day', sold_at AT TIME ZONE 'UTC')::date BETWEEN
      '2025-01-01' AND '2025-01-31'
GROUP BY 1;

結果:
フルスキャン: 約627 ms → 関数ベースインデックス: 約145 ms（全インデックス使用時は約187 ms）

規律の問題

クエリは インデックスと同じ式を正確に使わなければ PostgreSQL はシーケンシャルスキャンに戻ります。

SELECT (sold_at AT TIME ZONE 'UTC')::date ...

解決策

アプローチメリットデメリット
ビュー (
v_sale
)		正しい式が保証される直接テーブルにアクセスできてしまう
仮想生成列 (PostgreSQL 18+)式をカラムとして保存、追加ストレージ不要仮想列へのインデックスはまだサポートされていない
ALTER TABLE sale ADD sold_at_date DATE 
    GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC'));

これにより、クエリは単に 

sold_at_date
を参照するだけでインデックスが利用されます。

3. ハッシュインデックスで一意性を保証

問題

URL が長大なテキスト (

text
) の場合、B‑Tree インデックスは膨大になります。

CREATE TABLE urls (
    id INT PRIMARY KEY,
    url TEXT NOT NULL,
    data JSON
);

CREATE UNIQUE INDEX urls_url_unique_ix ON urls(url);
-- サイズ: 約154 MB（テーブル 160 MB）

ハッシュインデックスで解決

  1. 排除制約を使ってユニークハッシュインデックスを作成

    ALTER TABLE urls ADD CONSTRAINT urls_url_unique_hash 
    EXCLUDE USING HASH (url WITH =);

  2. 一意性の検証 – 重複挿入は失敗します。

  3. クエリ性能 – B‑Tree と同等か、しばしば高速です。

  4. サイズ比較

インデックスサイズ
urls_url_unique_hash
32 MB
urls_url_unique_ix
154 MB

制限事項

  • 外部キー参照に使用できない。
  • ON CONFLICT … DO UPDATE
    はサポートされていないので、
    MERGE
    を使うか、制約名を指定して 
    ON CONFLICT ON CONSTRAINT
    とする必要があります。
  • 大きな文字列で一意性が必須で、FK 参照が不要なケースに有効です。

まとめ

テクニック適用シナリオ主なメリット
constraint_exclusionアドホック BI クエリや入力ミスが想定される環境条件に合わないテーブルを完全にスキップ
関数ベースインデックス／仮想生成列カード性が低い列（例: 日付）インデックスサイズ削減、クエリ速度向上、規律の問題解消
ハッシュインデックス (排除制約)長大な文字列で一意性を確保したいとき大幅に小さいインデックス、検索高速化

これらの非定番手法を取り入れることで、ストレージコストの削減、クエリ性能の向上、人為的ミスへの耐性強化が実現できます。

同じ日のほかのニュース

一覧に戻る →

2026/01/21 3:16

**2019年 「見えないままに隠された26000年前の天文モニュメント」**

## Japanese Translation: **概要：** フーバーダムの西側にあるモニュメント・プラザは、地球の25,772年周期の軸進動をタレーズ床にマッピングした芸術的な敬意表現です。設計の中心には旗竿があり、これは進動円の中心として機能します。その周囲には巨大な翼付きブロンズ像が配置されています。床はダム開業時（1936年）のポラリス、ピラミッド建設中のトゥバン、そして将来の北極星としてプロジェクトされたベガを示しており、技術図面には惑星位置が正確に配置されているため、1日単位での日付計算が可能です。1931年に米国再利用局（U.S. Bureau of Reclamation）から委託され、1936年に完成したモニュメントは後にモニュメント・プラザと名付けられました。アーティストのオスカー J. W. ハンセン氏の意図表明は抽象的であり、歴史家エメー・ウッドワードが提供したアーカイブ写真には「セーフティアイランド」という早期建設名が示されています。デザインはロング・ナウ（Long Now）の10,000年時計コンセプトを反映しており、天体周期の公衆展示に類似するものとしてインスピレーションを与える可能性があります。米国再利用局が計画図をInternet Archiveへ公開したことは、ダム文書へのオープンアクセス化の動向を示しています。訪問者には短い音声解説のみが提供されるため、詳細な科学内容はほぼ隠蔽されており、教育的広報は限定的です。それでもプラザは20世紀初頭の大規模インフラプロジェクトにおける芸術と科学の統合を示す具体例として、歴史家・天文学者・エンジニアに貴重な実証を提供します。

2026/01/21 6:34

**日本のスナックバーの秘密世界へようこそ** 「おかかん」と呼ばれることもある日本のスナックバーは、地元ならではの味と創造性を堪能できる隠れた宝石です。小さな飲食店が揃うこれらのお店では、フライドライスボール（おにぎり）や塩気のあるペストリー、甘いスイーツなど、手軽で美味しい一口料理を提供しつつ、親切なサービスも楽しめます。 - **雰囲気**：街角の居心地の良い隅っこや静かな路地裏が主流です。 - **メニューの見どころ**： - 海苔と醤油をトッピングしたフライドライスボール - 蜂蜜でコーティングされた甘いかぼちゃロール - 豚肉と竹笹の餡が入った塩味の餃子 - **特徴**： - 多くのお店では、新鮮な地元産食材を使用しています。 - 季節ごとにメニューが変わるローテーションもあります。 おかかんへ足を踏み入れることは、単なるスナックの取得以上の体験です。便利さと伝統が交差する日本の食文化を垣間見る瞬間なのです。

## Japanese Translation: スナックバー―第二次世界大戦後に厳しい酒類法を回避するために登場した、女性が経営する小規模カフェ―は、日本独自の社会的ハブとして確立しました。全国には約10万店舗が存在し、コンビニエンスストアの数を2倍以上上回ります。これらは「ママさん」と呼ばれる年配女性によって運営され、家庭的な雰囲気でシンプルなお菓子や飲み物を提供します。主目的は食べ物や飲み物だけでなく、会話と個人的つながりを得られる居心地の良い空間を提供することです。 このコンセプトは日本の戦後経済成長期に急速に広がりました。1960年代後半には全国的にスナックバーが普及し、コミュニティのハブとなり、その後1980年代中頃から1990年代初頭まで存続しました。特徴として「ボトルキープ」システムがあります：常連客は自分のウイスキーや焼酎をラベル付けして保管し、次回訪問時に利用できます。伊良谷真由子（Mayuko Igarashi）氏などの研究者は2021年以降、1200件以上の店舗を巡り、旅行者とこれまで地元住民限定だった場所を結びつけています。 スナックバーはCOVID-19以前から人気低下やホスト人口の高齢化、カラオケバーやチェーン居酒屋との競争など課題に直面していますが、新しいトレンドが存続を支えています。アニメテーマのスナック、SNSプロモーション、LGBTQ+来客へのより包括的な対応などです。著名例として新橋の「Aeru」のウララ氏は14年間でタロットリーディングと現代のマッチメイキング手法を用いて90組以上のカップルを紹介しています。 デジタルライフの圧力にもかかわらず、スナックバーはママさんとの真の人間関係や親密な会話が評価され続けています。旅行者は、豪華ホテルや寿司教室など高価な観光活動よりも、これらの場所での思い出深い体験を重視しており、地元経済と伝統的ホスピタリティ慣行の保存に対する継続的な関連性を示しています。

2026/01/21 1:48

**UNIXパイプ・カードゲーム**

## Japanese Translation: 記事は、子どもに Unix パイプコマンドチェーンの構築方法を教えるカードベースのボードゲームについて説明しています。デッキには、`cat`、`grep`、`tail`、**`head`、`wc`、`sort`、`uniq`** などの主要な Unix コマンドを表すカードと、特定行の表示、出現回数のカウント、非表示コマンドチェーンの作成といったタスクが含まれています。ローカルの Unix システムが利用できない場合は、ブラウザベースの jslinux を使用できます。 ゲームプレイは時計回りに進行します：プレイヤーはカードを引き、最短または最長の有効パイプラインを目指すかどうかを選択し、タスクを完了した最初のプレイヤーがポイントを獲得します。例として、最も頻出する行を見つけるチェーンを示すラウンドがあります： ``` cat 03.txt | sort | uniq -c | sort -n | tail -1 ``` ゲームは €5,00 EUR（現在完売）で販売されており、印刷可能な PDF（`unix-pipe-cards.pdf`、`unix-pipe-box.pdf`）も入手できます。 GitHub 上のソースコードリポジトリ（`github.com/jackdoe`）、共著者 Jackie、連絡メールアドレス (`b0000@fastmail.com`) と CC BY 4.0 ライセンスが製品に付属しています。 「UNIX Pipe Game – Process Substitution」という拡張パックでは、プロセス置換用のコマンド（`paste`、`tr`、`cut`、`bc`）を追加します。 著者は Python の基礎、C ポインタ、機械語、ランレングス符号化、関数合成、RISCV アセンブラなどをカバーする追加のカードゲームも公開しており、子ども向けにプログラミング概念をゲーミフィケーションする広範な取り組みを示しています。