PostgresでIndexによる実行速度の違いを調査した

Indexとは

インデックスの意味とメリット・デメリット

要するに索引で、対象のカラムを高速に検索するために利用される。
実装は対象のカラムとIDの組を、対象カラムの内容でソートしたものがある。
（他にもありそう）

イメージとしては、よく技術本の索引で用語集みたいなのがアルファベット順で並べてあるのを思い浮かべた。
「Index」を調べようとしたら、アルファベット順に並んだものからすぐに「Index」を見つけられ、Indexが記載されたページ（ID）がすぐ分かる。

一方、DBの内容を変更する操作はIndexを更新する必要があるため遅くなる。
これも本の索引を調べたらすんなり分かる。

実験

準備

実験のためDockerでPostgresサーバーを立てた。

version: "3.1"
services:
  db:
    image: postgres:12.2
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=example
      - PGPASSWORD=example
      - POSTGRES_DB=testdb
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

テーブルを作成した。

CREATE TABLE "one" (
  "id" serial NOT NULL,
  "name" character(50) NULL,
  "age" integer NULL
);

名前と年齢のデータで年齢でユーザーを検索するイメージ。
適当です。

実験用のSQL

インデックスを貼る

CREATE INDEX "one_age" ON "one" ("age");

データを入れる
1千万行入れます。
検索予定のageは1歳から200歳までランダムで挿入します。

INSERT INTO "one" (id,name, age) 
 SELECT 
     i, format('テスト%s', i), (random()*200)::int
 FROM
     generate_series(1,10000000) as i
;

データを検索する

SELECT
	*
FROM
	one
WHERE
	age = 30
;

実験結果

速度測定のためにpostgresのCUI上で\timingを使い、SQLを実行しました。

	挿入	検索
インデックス無し	24s	0.8s
インデックス有り	49s	0.3s

結構影響ありますね。