RAKUS Developers Blog | ラクス エンジニアブログ

株式会社ラクスのITエンジニアによる技術ブログです。

トップ > PostgreSQL > 【初心者向け】PostgreSQLでインデックスを使ってくれない理由

【初心者向け】PostgreSQLでインデックスを使ってくれない理由

PostgreSQL

はじめに

こんにちは、2年目になったyk_itgです。
今回はpostgreSQLでindexを利用した時、どれだけ検索速度が上がるのか調べてみよう…としたのですが、うまくindexを使ってもらえませんでした。今回はその時のことを記事にしたいと思います。
なお、この記事ではpostgreSQLでindexを作成した時のデフォルトであるB-tree indexを使用していることを前提としています。

検証バージョン

postgreSQL 11.4

さっそくindexを作ってみた

とりあえず、indexを貼るテーブルが必要です。
大量データがないと効果がわからないと思ったので、こちらの記事を参考に100万件のレコードを持つテーブルを作ってみました。

tech-blog.rakus.co.jp

CREATE TABLE indexTest (
 id integer PRIMARY KEY,
 name text 
)
INSERT INTO indexTest (id,name)
 SELECT
     i, format('text%s', i)
 FROM
     generate_series(1,10000000) as i
;

このテーブルにCREATE INDEXしてindexを作ってみます。

CREATE INDEX ON indexTest (id);
CREATE INDEX ON indexTest (name);
explain_test=# \di
                     List of relations
 Schema |        Name        | Type  |  Owner   |   Table   
--------+--------------------+-------+----------+-----------
 test1  | indextest_id_idx   | index | postgres | indextest
 test1  | indextest_name_idx | index | postgres | indextest
(2 rows)

いざ実行

では実際にindexを貼ったテーブルに対して検索をかけてみます。
ただ、検索が速くなるといっても目視ではさっぱり違いがわからないのでEXPLAINを使います。

EXPLAINを使うとコストとどんな方法を使って検索しようとしているのか調べることができます。
Indexが利用されている場合には、Index ScanやIndex Only Scanなどのindexを使っていることを示すEXPLAIN演算子が実行計画に表示されるはずです。

早速先ほどindexを張ったnameのカラムを検索条件に入れて検索してみます。

explain_test=# EXPLAIN SELECT COUNT(name) FROM indexTest WHERE name LIKE '%text10001%';
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=107139.59..107139.60 rows=1 width=8)
   ->  Gather  (cost=107139.38..107139.59 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=106139.38..106139.39 rows=1 width=8)
               ->  Parallel Seq Scan on indextest  (cost=0.00..106138.33 rows=417 width=11)
                     Filter: (name ~~ '%text10001%'::text)
(6 rows)

あれ？indexが使われていないようですね。
Index Scanではなく、テーブルを順番に見ていくSeq Scanが表示されています。

試しに検索条件をidに変更してみます。

explain_test=# EXPLAIN SELECT COUNT(*) FROM indexTest WHERE id <= 5000000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=112375.23..112375.24 rows=1 width=8)
   ->  Gather  (cost=112375.01..112375.22 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=111375.01..111375.02 rows=1 width=8)
               ->  Parallel Seq Scan on indextest  (cost=0.00..106138.33 rows=2094672 width=0)
                     Filter: (id <= 5000000)
(6 rows)

が、やはりindexは使われないようです。

使われない理由

一つ目の理由

一つ目の理由は前方一致以外のLIKEを使って検索しているためです。
B-treeのindexを使用した場合は、文字列の前半についてしか検索を行えません。 なので、前半が検索条件である前方一致の検索ではindexを使用してくれます。

explain_test=# EXPLAIN SELECT COUNT(name) FROM indexTest WHERE name LIKE 'text10001%';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Aggregate  (cost=12.27..12.28 rows=1 width=8)
   ->  Index Only Scan using indextest_name_idx on indextest  (cost=0.43..9.77 rows=1000 width=11)
         Index Cond: ((name >= 'text10001'::text) AND (name < 'text10002'::text))
         Filter: (name ~~ 'text10001%'::text)
(4 rows)

実行計画を見てもしっかり Index Only Scanが表示されています。

二つ目の理由

二つ目の理由は検索条件に当てはまる行数が多すぎるためです。
当てはまる行数が多すぎる場合には、indexを使って検索するよりもテーブルを順番にみていくほうが速くなります。

explain_test=# EXPLAIN SELECT COUNT(*) FROM indexTest WHERE id <= 3700000;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=110081.60..110081.61 rows=1 width=8)
   ->  Gather  (cost=110081.38..110081.59 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=109081.38..109081.39 rows=1 width=8)
               ->  Parallel Index Only Scan using indextest_pkey on indextest  (cost=0.43..105174.50 rows=1562752 width=0)
                     Index Cond: (id <= 3700000)
(6 rows)

このように検索条件に応じて適切なプランをデータベースは絞り込んでくれます。

感想

indexを使えばどれくらい速くなるのかは検証することができませんでしたが、indexの仕組みや実行計画について触れることが出来ました。
どんどん触れる機会が増えていくと思うので、今後もデータベースの仕組みを学んでいきたいと思います。

参考

[MySQL]WHERE句でLIKEを使っている場合のINDEX | DayByDay

PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～

【SQL】インデックスの基本知識まとめ - Qiita

B木 - Wikipedia

◆TECH PLAY
techplay.jp

◆connpass
rakus.connpass.com

ラクスの技術 / デザイン情報サイト
Copyright © RAKUS Co., Ltd. All rights reserved.