KDOC 497: 複合インデックスの使用条件を試す

CREATE TABLE users (
    id serial PRIMARY KEY,
    name varchar(255),
    tel integer,
    nonidx varchar(255)
);
CREATE INDEX idx_users_name ON users(name, tel);

-- レコードが空だと、すべてインデックス使用になったため
INSERT INTO users (name, tel)
SELECT
    'Name' || (i % 1000),
    (i % 10000)
FROM generate_series(1, 100000) i;

-- 複合インデックスの両方の要素を使うと、インデックスが使われる
explain
select id from users where name = 'a' and tel = 1;

-- 複合インデックスの両方の要素を使ってインデックス要素だけだと、Index Only Scanになる
explain
select name from users where name = 'a' and tel = 1;

-- 複合インデックスの最初の要素を使うと、インデックスが使われる
explain
select id from users where name = 'a';

-- 複合インデックスの順番も入れ替えても、要素があっていればインデックスが使われる
explain
select id from users where tel = 1 and name = 'a';

-- 複合インデックスの最初の要素 + インデックス外だとインデックスが使われる
explain
select id from users where name = 'a' and nonidx = 'a';

-- 複合インデックスの2番めの要素を使うと、インデックスが使われない
explain
select id from users where tel = 1;

|                                                                                   |   |             |
|-----------------------------------------------------------------------------------+---+-------------|
| > CREATE TABLE users (                                                            |   |             |
| >     id serial PRIMARY KEY,                                                      |   |             |
| >     name varchar(255),                                                          |   |             |
| >     tel integer,                                                                |   |             |
| >     nonidx varchar(255)                                                         |   |             |
| > );                                                                              |   |             |
| CREATE TABLE                                                                      |   |             |
|                                                                                   |   |             |
| > CREATE INDEX idx_users_name ON users(name, tel);                                |   |             |
| CREATE INDEX                                                                      |   |             |
|                                                                                   |   |             |
| > INSERT INTO users (name, tel)                                                   |   |             |
| > SELECT                                                                          |   |             |
| >     'Name'                                                                      |   | (i % 1000), |
| >     (i % 10000)                                                                 |   |             |
| > FROM generate_series(1, 100000) i;                                              |   |             |
| INSERT 0 100000                                                                   |   |             |
|                                                                                   |   |             |
| > explain                                                                         |   |             |
| > select id from users where name = 'a' and tel = 1;                              |   |             |
| QUERY PLAN                                                                        |   |             |
| Index Scan using idx_users_name on users  (cost=0.28..8.30 rows=1 width=4)        |   |             |
| Index Cond: (((name)::text = 'a'::text) AND (tel = 1))                            |   |             |
|                                                                                   |   |             |
| > explain                                                                         |   |             |
| > select name from users where name = 'a' and tel = 1;                            |   |             |
| QUERY PLAN                                                                        |   |             |
| Index Only Scan using idx_users_name on users  (cost=0.28..8.30 rows=1 width=516) |   |             |
| Index Cond: ((name = 'a'::text) AND (tel = 1))                                    |   |             |
|                                                                                   |   |             |
| > explain                                                                         |   |             |
| > select id from users where name = 'a';                                          |   |             |
| QUERY PLAN                                                                        |   |             |
| Bitmap Heap Scan on users  (cost=4.43..69.98 rows=19 width=4)                     |   |             |
| Recheck Cond: ((name)::text = 'a'::text)                                          |   |             |
| ->  Bitmap Index Scan on idx_users_name  (cost=0.00..4.42 rows=19 width=0)        |   |             |
| Index Cond: ((name)::text = 'a'::text)                                            |   |             |
|                                                                                   |   |             |
| > explain                                                                         |   |             |
| > select id from users where tel = 1 and name = 'a';                              |   |             |
| QUERY PLAN                                                                        |   |             |
| Index Scan using idx_users_name on users  (cost=0.28..8.30 rows=1 width=4)        |   |             |
| Index Cond: (((name)::text = 'a'::text) AND (tel = 1))                            |   |             |
|                                                                                   |   |             |
| > explain                                                                         |   |             |
| > select id from users where name = 'a' and nonidx = 'a';                         |   |             |
| QUERY PLAN                                                                        |   |             |
| Bitmap Heap Scan on users  (cost=4.42..70.03 rows=1 width=4)                      |   |             |
| Recheck Cond: ((name)::text = 'a'::text)                                          |   |             |
| Filter: ((nonidx)::text = 'a'::text)                                              |   |             |
| ->  Bitmap Index Scan on idx_users_name  (cost=0.00..4.42 rows=19 width=0)        |   |             |
| Index Cond: ((name)::text = 'a'::text)                                            |   |             |
|                                                                                   |   |             |
| > explain                                                                         |   |             |
| > select id from users where tel = 1;                                             |   |             |
| QUERY PLAN                                                                        |   |             |
| Seq Scan on users  (cost=0.00..588.34 rows=19 width=4)                            |   |             |
| Filter: (tel = 1)                                                                 |   |             |