はじめに

最近は業務でDBMSをよく扱うことからデータベース関連の書籍を読んでいます。
「SQL実践入門」は性能に目を向けた内容で面白かったです。

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

作者:ミック
発売日: 2015/04/11
メディア: 単行本（ソフトカバー）

本書籍ではSQLの実行計画を読み解くことでSQLをチューニングしています。紹介されている内容を実際に試してみるため、MacBookに本書籍で説明に用いているPostgreSQLをインストールすることにしました (業務で扱っているのはHiRDBですが、基本的な考え方はどのDBMSでも通用するはずなのであまり気にしないことにします）。

PostgreSQLのインストール

Homebrewでインストールします。インストールしてない場合は次のコマンドでインストールしてください。

$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

続けてPostgreSQLをインストールして、'initdb'でデータベースクラスタ *1を作成します。

$ brew install postgresql
$ initdb /usr/local/var/postgres -E utf8                                                                                                                    
$ postgres --version
 postgres (PostgreSQL) 10.1

postgresサーバの起動

PGDATA環境変数でデータの物理的な配置を指定します。

~/.bash_profile

export PGDATA=/usr/local/var/postgres

'pg_ctl'でサーバを起動します。 -Dオプションを使用するとデータベースファイルの場所を指定することができます。以下の例では省略しているため.bash_profileのPGDATA環境変数で指定した内容で動作します。

$ pg_ctl -l /usr/local/var/postgres/server.log start
waiting for server to start.... done
server started

データベースの作成

新規作成するデータベースの名称と所有者とするユーザを指定して'createdb'を実行します。

$ createdb example-db -O hiroki_sawano
$ psql -l
                                          List of databases
    Name    |     Owner     | Encoding |   Collate   |    Ctype    |        Access privileges        
------------+---------------+----------+-------------+-------------+---------------------------------
 example-db | hiroki_sawano | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | hiroki_sawano | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | hiroki_sawano | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/hiroki_sawano               +
            |               |          |             |             | hiroki_sawano=CTc/hiroki_sawano
 template1  | hiroki_sawano | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/hiroki_sawano               +
            |               |          |             |             | hiroki_sawano=CTc/hiroki_sawano
(4 rows)

データベースへの接続とSQLの実行

'psql'(PostgreSQL対話的ターミナル)でデータベースに接続します。
試しに'numbers'表を作成し、複数の行を適当に挿入しました。

$ psql -U hiroki_sawano example-db
psql (10.1)
Type "help" for help.

example-db=# create table numbers(num integer primary key);
CREATE TABLE
example-db=# insert into numbers values(1);
INSERT 0 1
example-db=# insert into numbers values(3);
INSERT 0 1
example-db=# insert into numbers values(4);
INSERT 0 1
example-db=# insert into numbers values(7);
INSERT 0 1
example-db=# insert into numbers values(8);
INSERT 0 1
example-db=# insert into numbers values(9);
INSERT 0 1
example-db=# insert into numbers values(12);
INSERT 0 1
example-db=# select * from numbers;
 num
-----
   1
   3
   4
   7
   8
   9
  12
(7 rows)

SQL実行計画の確認

EXPLAINコマンドに続けてSQLを実行することで実行計画を確認できます。
WHERE句を省略したSELECT文ではSeq Scanで全行が順にスキャンされ、 WHERE句に主キー'num'への条件を付与したSELECT文ではインデクスが効いてIndex Only Scanされている様子が確認できます。

example-db=# explain
example-db-# select * from numbers;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on numbers  (cost=0.00..35.50 rows=2550 width=4)
(1 row)

example-db=# select * from numbers where num = 3;
 num 
-----
   3
(1 row)

example-db=# explain
example-db-# select * from numbers where num = 3;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Only Scan using numbers_pkey on numbers  (cost=0.15..8.17 rows=1 width=4)
   Index Cond: (num = 3)
(2 rows)

参考

MacにPostgreSQLをインストール - Qiita
PostgreSQL: Documentation: 10: PostgreSQL 10.13 Documentation

*1:1つのサーバインスタンスで管理されるデータベースの集合

JoTech

MacにPostgreSQLインストールとSQL実行計画の確認