PostgreSQLチューニング

PostgresSQL Conference Japan 2017のセッションで発表しました。

Transcript

1. PostgreSQL チューニング 【T1】 SQLの基本 【T2】 PostgreSQLチューニング 【T3】 データベース監視の基本 【T4】 PostgreSQLレプリケーション解説 PostgreSQL Conference Japan 2017 2017.11.3 株式会社アシスト データベース技術本部 駒形 美鈴
2. 【T2】 PostgreSQLチューニング Japan PostgreSQL User's Group 2  セッションテーマ SQLでデータベースに対して検索/操作を指示することができますが、その所要 時間は環境によって様々です。データベースは、正しくメンテナンスやチューニ ングされ、性能面でも実用レベルであることが期待されます。  内容、対象者 アプリ開発者やDBA初級者を対象に、性能の悪いSQLを改善していく「SQL チューニング」の実例を通してチューニングの必要性や考え方を解説します。 株式会社アシスト 駒形 美鈴氏 仕事では主にOracle Databaseの構築やお客様問合せを担当して おり、その傍らデータベースに関する話題を日々勉強しています。 Oracle DB、PostgreSQLなど多くの製品に触れられる機会を活か して、両データベースでチューニングの勉強をする中で今回の発 表のお話をいただきました。 みなさんと一緒にステップアップしていきたいと思います！
3. アジェンダ データベースの性能について考える SQLチューニングとDBチューニング 少量のデータを高速に取得するしくみ、インデックス インデックスを使ったSQLチューニング SQLチューニング実践 Japan PostgreSQL User's Group 3
4. データベースの性能とは？ それぞれどのような問題だと思いますか？ Japan PostgreSQL User's Group 4 性能問題 2パターン ◦◦の処理が遅いです どのような状況ですか？ ？ では、以下の情報を ご提供いただけますか？ ・そのときのSQLや 実行計画 ・アプリの修正やデータ 追加など、問題発生前 に実施した作業 ◦◦の処理が遅いです どのような状況ですか？ ？ では、以下の情報を ご提供いただけますか？ ・平常時と負荷高騰時の CPU使用率、I/O量 などの監視データ ・データベースの パラメータ設定一覧 Case A Case B
5. データベースの性能とは？ それぞれどのような問題だと思いますか？ Japan PostgreSQL User's Group 5 性能問題 2パターン ◦◦の処理が遅いです どのような状況ですか？ これまですぐ表示され ていた画面が、数十秒 かかってしまいます。 では、以下の情報を ご提供いただけますか？ ・そのときのSQLや 実行計画 ・アプリの修正やデータ 追加など、問題発生前 に実施した作業 ◦◦の処理が遅いです どのような状況ですか？ ？ では、以下の情報を ご提供いただけますか？ ・平常時と負荷高騰時の CPU使用率、I/O量 などの監視データ ・データベースの パラメータ設定一覧 Case A Case B
6. データベースの性能とは？ それぞれどのような問題だと思いますか？ Japan PostgreSQL User's Group 6 性能問題 2パターン ◦◦の処理が遅いです どのような状況ですか？ これまですぐ表示され ていた画面が、数十秒 かかってしまいます。 では、以下の情報を ご提供いただけますか？ ・そのときのSQLや 実行計画 ・アプリの修正やデータ 追加など、問題発生前 に実施した作業 ◦◦の処理が遅いです どのような状況ですか？ 月末で負荷が集中する と、全体的に重くてク レームがきています。 では、以下の情報を ご提供いただけますか？ ・平常時と負荷高騰時の CPU使用率、I/O量 などの監視データ ・データベースの パラメータ設定一覧 Case A Case B
7. データベースの性能とは？ それぞれどのような問題だと思いますか？ Japan PostgreSQL User's Group 7 性能問題 2パターン ◦◦の処理が遅いです どのような状況ですか？ これまですぐ表示され ていた画面が、数十秒 かかってしまいます。 では、以下の情報を ご提供いただけますか？ ・そのときのSQLや 実行計画 ・アプリの修正やデータ 追加など、問題発生前 に実施した作業 ◦◦の処理が遅いです どのような状況ですか？ 月末で負荷が集中する と、全体的に重くてク レームがきています。 では、以下の情報を ご提供いただけますか？ ・平常時と負荷高騰時の CPU使用率、I/O量 などの監視データ ・データベースの パラメータ設定一覧 同じ性能に関する問題でも、このように、影響の仕方や 影響範囲が異なり、調査の進め方もそれぞれです。 Case A Case B
8. データベースの性能とは？ 今日は主にSQLチューニングの話をします Japan PostgreSQL User's Group 8 SQLチューニングとDBチューニング 実行時間が長いSQLを対象に、 インデックスの作成、SQLの修正 などで所要時間の短縮を目指す。 サーバーのボトルネックを特定し パラメータ修正、マシン増強など 全体での処理量の改善を目指す。
9. 少しだけDBチューニングの話 データベース構築時のパラメータ設定 DBへの割り当てメモリサイズ 同時接続プロセス数 監視も重要 平常時と比較 ある時間帯だけ◦◦ Japan PostgreSQL User's Group 9 SQLチューニングとDBチューニング サーバーのボトルネックを特定し パラメータ修正、マシン増強など 全体での処理量の改善を目指す。 よく利用されるデータは メモリに載っていること ピーク時の接続数に備える それを扱うだけのCPU性能が必要 質疑応答をうけて追記：メモリチューニングの反対で、性能検証等ではバッファミスの状態を再現するこ とがあります。その場合、PostgreSQLでは、DBの再起動＋OSファイルキャッシュのクリアが必要です。
10. SQLチューニングの基本 1画面＝ある条件に従って抽出した1つのデータを表示 Japan PostgreSQL User's Group 10 インデックスを使って1行をとりだす Click！ DBへの問合せ 「駒形美鈴」の 「記事ID ｘｘｘｘ」 を取り出して表示
11. SQLチューニングの基本 1画面＝ある条件に従って抽出した1つのデータを表示 Japan PostgreSQL User's Group 11 インデックスを使って1行をとりだす 記事ID xxxx article表 ： DBへの問合せ •article表から「記事ID＝ｘｘｘｘ」条件に マッチする行を1つ特定する •このような検索ではインデックス（索引）を 使って該当行を探しだすことが極めて重要 SELECT * FROM article WHERE article_id=‘xxxxx’ AND user_id = ‘koma’
12. SQLチューニングの基本 1画面＝ある条件に従って抽出した1つのデータを表示 Japan PostgreSQL User's Group 12 インデックスを使って1行をとりだす article表 ： DBへの問合せ •article表から「記事ID＝ｘｘｘｘ」条件に マッチする行を1つ特定する •このような検索ではインデックス（索引）を 使って該当行を探しだすことが極めて重要 SELECT * FROM article WHERE article_id=‘xxxxx’ AND user_id = ‘koma’ xxxx koma 11/3 PostgreSQLカンファレンスで チュートリアルの発表しました 記事ID ユーザー 登録日 記事本文
13. どのファイルのどのブロックを取得する？ ファイル 98342 （article表） 20番ブロック SQLチューニングの基本 Japan PostgreSQL User's Group 13 インデックスを使って1行をとりだす ： ファイル98342の 20個目のブロック、 3行目です ID=ｘｘｘｘの位置は？ A B X ・・・ 21番ブロック 19番ブロック 記事ID BBBB 記事ID AAAA 記事ID xxxx インデックス
14. どのファイルのどのブロックを取得する？ ファイル 98342 （article表） 20番ブロック SQLチューニングの基本 Japan PostgreSQL User's Group 14 インデックスを使って1行をとりだす ： ファイル98342の 20個目のブロック、 3行目です ID=ｘｘｘｘの位置は？ A B X ・・・ 21番ブロック 19番ブロック 記事ID BBBB 記事ID AAAA 記事ID xxxx インデックス インデックスは、値通りに 並べたデータの集まりなので、 指定の値を探せるんです！ 表内の並び順はバラバラで、 条件にあうかどうかは、 検索してみないとわかりません。
15. どのファイルのどのブロックを取得する？ ファイル 98342 （article表） 20番ブロック SQLチューニングの基本 Japan PostgreSQL User's Group 15 インデックスを使って1行をとりだす ： ファイル98342の 20個目のブロック、 3行目です ID=ｘｘｘｘの位置は？ A B ・・・ 21番ブロック 19番ブロック 記事ID BBBB 記事ID AAAA 記事ID xxxx X インデックスは、値通りに 並べたデータの集まりなので、 指定の値を探せるんです！ 表内の並び順はバラバラで、 条件にあうかどうかは、 検索してみないとわかりません。
16. アジェンダ データベースの性能について考える インデックスを使ったSQLチューニング インデックスを作成する インデックスを使う チューニングの効果を確認する SQLチューニング実践 Japan PostgreSQL User's Group 16
17. デモ環境について article表の作成 Japan PostgreSQL User's Group 17 CREATE TABLE article (article_id text ,user_id text ,regdate date ,maintext text); -- 100000行のダミーデータを生成 INSERT INTO article SELECT article_id,user_id,regdate,maintext FROM (SELECT i ,lpad(string_agg(id, ''),8) article_id ,(random()* 100000)::int%80 user_id ,(now()::date-1-(random()* 100000)::int%365*interval'1day')::date regdate ,string_agg(str, '') maintext FROM ( SELECT chr(97+(random() * 100000)::int % 26 ) as id , chr(12354+(random() * 100000)::int % 85 ) as str, i FROM generate_series(1,20) length ,generate_series(1,100000) num(i) )t GROUP BY i) datagen ; INSERT INTO article VALUES ('aaaa','0' ,now()::date,'チューニングのセッションを担当しました') ,('bbbb','1' ,now()::date,'SQLの解説をしました') ,('cccc','10' ,now()::date,'データベース監視の話をしました') ,('dddd','11' ,now()::date,'チュートリアルのプロデュースしました'); SELECT * FROM article; article_id | user_id | regdate | maintext ------------+---------+------------+---------------------------------------- aaaa | 0 | 2017-10-29 | チューニングのセッションを担当しました bbbb | 1 | 2017-10-29 | SQLの解説をしました cccc | 10 | 2017-10-29 | データベース監視の話をしました dddd | 11 | 2017-10-29 | チュートリアルのプロデュースしました gbdtmnen | 7 | 2017-03-14 | ふてほりすぽすわびみかこむるばわよしじい yudugwgt | 9 | 2017-08-21 | こんじぅしふ?ゐのぃねびさゑがいからぇヴ lamdwdle | 40 | 2016-12-15 | けぴあさべびみよだゐごしぴへつっさふゅえ prwmbdhq | 35 | 2016-12-03 | ゅろよる?ぎぷぼゅがゅぬゐゆぬぎあずむな 以降、ランダムな データが約1000件
18. デモ環境について user_mst表の作成 Japan PostgreSQL User's Group 18 CREATE TABLE user_mst (user_id text,username text); ALTER TABLE user_mst ADD CONSTRAINT user_id_uk unique(user_id); INSERT INTO user_mst SELECT user_id,username FROM (SELECT i,(random()*1000)::int%90 user_id,string_agg(str3, '') username FROM ( SELECT i,chr(12354+(random() * 1000)::int % 85 ) as str3 FROM generate_series(1,10) length,generate_series(1,300) num(i))t GROUP BY i) datagen ON CONFLICT ON CONSTRAINT user_id_uk DO NOTHING; UPDATE user_mst SET username = 'koma' WHERE user_id = '0' ; UPDATE user_mst SET username = 'katsu' WHERE user_id = '1' ; UPDATE user_mst SET username = 'soudai' WHERE user_id = '10'; UPDATE user_mst SET username = 'kida' WHERE user_id = '11'; SELECT * FROM user_mst ORDER BY user_id; user_id | username ---------+---------------------- 0 | koma 1 | katsu 10 | soudai 11 | kida 12 | ずふぢぃくよえづびい 13 | ぅえねどねおむゃあぞ 14 | がゅうらおゎてぞはぽ 15 | すゅらずゃぢぴみぼぢ 以降、ランダムな データが約90件
19. デモ環境について article表とuser_mst表 Japan PostgreSQL User's Group 19 article表 Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | ※約10万件の記事データ,80人の執筆者 ※1人あたり1200件程度の記事 user_mst表 Column | Type | Modifiers ----------+------+----------- user_id | text | username | text | Indexes: “user_id_uk”UNIQUE CONSTRAINT ・・・ ※87件のユーザーデータ ユーザーはユニーク article_id user_id regdate maintext aaaa 0 2017-10-29 チューニングのセッシ bbbb 1 2017-10-29 SQLの話をしてきま xxxx 1 2017-10-28 ご挨拶。 cccc 10 2017-10-29 監視の話を・・・ yyyy 10 2017-10-25 そーだいなる生誕祭 zzzz 10 2017-10-22 マカレルが・・・ ： user_id username 0 koma 1 katsu 10 soudai 11 kida このように、データの中身まで 想像してみると良いですね！
20. デモ環境について article表とuser_mst表 Japan PostgreSQL User's Group 20 article表 Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | ※約10万件の記事データ,80人の執筆者 ※1人あたり1200件程度の記事 user_mst表 Column | Type | Modifiers ----------+------+----------- user_id | text | username | text | Indexes: “user_id_uk”UNIQUE CONSTRAINT ・・・ ※87件のユーザーデータ ユーザーはユニーク article_id user_id regdate maintext aaaa 0 2017-10-29 チューニングのレクチ bbbb 1 2017-10-29 SQLの話をしてきま xxxx 1 2017-10-28 ご挨拶。 cccc 10 2017-10-29 監視の話を・・・ yyyy 10 2017-10-25 そーだいなる生誕祭 zzzz 10 2017-10-22 マカレルが・・・ ： user_id username 0 koma 1 katsu 10 soudai 11 kida このように、データの中身まで 想像してみると良いですね！ 質疑応答をうけて追記 ------------------------------------------------------------ 本来、IDなどの整数値で表現出来るデータは、integer型 またはbigint型などの整数値型のほうが検索、ソート性能 に優れます。
21. インデックスを作成する 1行を絞りこむような検索の対象列 Japan PostgreSQL User's Group 21 まずはインデックスを作って試してみましょう！ /* article表のarticle_id列にインデックスを作成 */ CREATE INDEX article_id_idx ON article(article_id); demo=# ¥d article Table "public.article" Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | Indexes: "article_id_idx" btree (article_id) CREATE INDEX <インデックス名> ON 表名(列名); SELECT * FROM article WHERE article_id = 'aaaa'; article_id_idx article_id user_id regdate maintext aaaa 0 2017-10-29 チューニングのセッシ articl e_id regdate maintext aaaa 0 2017-10-29 チューニングのレク チ bbbb 1 2017-10-29 SQLの話をしてきま xxxx 1 2017-10-28 ご挨拶。 cccc 1 0 2017-10-29 監視の話を・・・ yyyy 1 0 2017-10-25 そーだいなる生誕祭 zzzz 1 0 2017-10-22 マカレルが・・・ ：
22. インデックスを使用する WHERE句の検索条件にarticle_id列を指定 Japan PostgreSQL User's Group 22 article_id列のインデックスを使用してみる SELECT * FROM article; SELECT * FROM article WHERE article_id = 'aaaa'; article_id | user_id | regdate | maintext ------------+---------+------------+---------------------------------------- aaaa | 0 | 2017-10-29 | チューニングのセッションを担当しました bbbb | 1 | 2017-10-29 | SQLの解説をしました cccc | 10 | 2017-10-29 | データベース監視の話をしました dddd | 11 | 2017-10-29 | チュートリアルのプロデュースしました ： article_id | user_id | regdate | maintext ------------+---------+------------+---------------------------------------- aaaa | 0 | 2017-10-29 | チューニングのセッションを担当しました 「このインデックスを使う」 なんてことは、SQLでは 指示しないんですね！
23. インデックスを使用する 実行計画で内部の動きを確認する Japan PostgreSQL User's Group 23 インデックスを作ったことで何が変わったの？ SELECT * FROM article WHERE article_id = 'aaaa'; EXPLAIN ANALYZE SELECT * FROM article WHERE article_id = 'aaaa'; article_id | user_id | regdate | maintext ------------+---------+------------+---------------------------------------- aaaa | 0 | 2017-10-29 | チューニングのセッションを担当しました QUERY PLAN ------------------------------------------------------- Index Scan using article_id_idx on article (cost=0.42..8.44 rows=1 width=74) (actual time=0.037..0.038 rows=1 loops=1) Index Cond: (article_id = 'aaaa'::text) Planning time: 0.143 ms Execution time: 0.132 ms
24. インデックスを使用する インデックスが無い場合、全件取得してからフィルタリング Japan PostgreSQL User's Group 24 インデックスを作ったことで何が変わったの？ EXPLAIN ANALYZE SELECT * FROM article WHERE article_id = 'aaaa'; QUERY PLAN ------------------------------------------------------- Seq Scan on article (cost=0.00..2584.05 rows=1 width=74) (actual time=26.852..26.854 rows=1 loops=1) Filter: (article_id = 'aaaa'::text) Rows Removed by Filter: 100003 Planning time: 0.230 ms Execution time: 26.891 ms QUERY PLAN ------------------------------------------------------- Index Scan using article_id_idx on article (cost=0.42..8.44 rows=1 width=74) (actual time=0.037..0.038 rows=1 loops=1) Index Cond: (article_id = 'aaaa'::text) Planning time: 0.143 ms Execution time: 0.132 ms article表の件数が増えれば 増えるほど、この性能差は 開いていきますよね！
25. アジェンダ データベースの性能について考える インデックスを使ったSQLチューニング SQLチューニング実践 インデックスが有効な例 インデックスが使えない例 Japan PostgreSQL User's Group 25
26. 多数の行が返る検索（user_id） 結果の行数や列の使い方を考える 実行計画の例（BitmapScan） Japan PostgreSQL User's Group 26 article表のどの列にインデックスを作る？ CREATE INDEX article_user_id ON article(user_id); EXPLAIN ANALYZE select * from article where user_id = '0'; QUERY PLAN ------------------------------------------------------------------------------------------ Bitmap Heap Scan on article (cost=26.16..1411.31 rows=1273 width=74) (actual time=0.770..3.141 rows=1225 loops=1) Recheck Cond: (user_id = '0'::text) Heap Blocks: exact=820 -> Bitmap Index Scan on article_user_id (cost=0.00..25.84 rows=1273 width=0) (actual time=0.539..0.539 rows=1225 loops=1) Index Cond: (user_id = '0'::text) Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | 1ユーザーを条件にすると約1200件が該当しますが、 全体の1.2%でしかないですね。 ユーザーは検索条件に必須だったり、ユーザーIDそ のものの更新は無いのは使い方の特徴ですね。
27. JOINの実行計画（user_id） 結果の行数や列の使い方を考える 実行計画の例（BitmapScan） Japan PostgreSQL User's Group 27 article表のどの列にインデックスを作る？ /* SET random_page_cost = 2 を実行した上で実行 */ Nested Loop (cost=17.98..2190.22 rows=4598 width=105) (actual time=0.848..13.232 rows=5143 loops=1) -> Seq Scan on user_mst (cost=0.00..2.30 rows=4 width=31) (actual time=0.056..0.069 rows=4 loops=1) Filter: (username = ANY ('{koma,katsu,soudai,kida}'::text[])) -> Bitmap Heap Scan on article (cost=17.98..534.48 rows=1250 width=76) (actual time=0.672..2.757 rows=1286 loops=4) -> Bitmap Index Scan on article_user_id (cost=0.00..17.67 rows=1250 width=0) (actual time=0.434..0.434 rows=1286 loops=4) 複数のユーザー名を指定して、 それらのユーザーが書いた記事を取得したい SELECT * FROM article NATURAL JOIN user_mst WHERE username IN ('koma','katsu','soudai','kida'); user_ mst 4名 article 1200記事 × 4名分
28. JOINの実行計画（user_id） 結果の行数や列の使い方を考える 実行計画の例（BitmapScan） Japan PostgreSQL User's Group 28 article表のどの列にインデックスを作る？ Nested Loop (cost=17.98..2190.22 rows=4598 width=105) (actual time=0.848..13.232 rows=5143 loops=1) -> Seq Scan on user_mst (cost=0.00..2.30 rows=4 width=31) (actual time=0.056..0.069 rows=4 loops=1) Filter: (username = ANY ('{koma,katsu,soudai,kida}'::text[])) -> Bitmap Heap Scan on article (cost=17.98..534.48 rows=1250 width=76) (actual time=0.672..2.757 rows=1286 loops=4) -> Bitmap Index Scan on article_user_id (cost=0.00..17.67 rows=1250 width=0) (actual time=0.434..0.434 rows=1286 loops=4) 複数のユーザー名を指定して、 それらのユーザーが書いた記事を取得したい SELECT * FROM article NATURAL JOIN user_mst WHEREusername IN ('koma','katsu','soudai','kida'); user_ mst 4件 article 1200記事 * 4名 article_id user_id regdate maintext aaaa 0 2017-10-29 チューニングのセッシ bbbb 1 2017-10-29 SQLの話をしてきま oooo 1 2017-10-25 ご挨拶。 xxxx 1 2017-10-28 こんにちは cccc 10 2017-10-29 監視の話を・・・ yyyy 10 2017-10-25 そーだいなる生誕祭 zzzz 10 2017-10-22 マカレルが・・・ ： user_id username 0 koma 1 katsu 10 soudai 11 kida ユーザー1人に対して1回Bitmap Scanして、それを4回loopしている JOINの問合せでは、 あるプランがloopした回数分 実行されるので、余計に 性能影響が大きくなります！ 補足説明を追記： 本スライド趣旨はインデックスの有無が性能（実行時間）に 影響する可能性を述べるものです。本来はこのようなケースで Hash Join等が選択され、特別に「悪い状態」とは限りません。
29. 時系列データ（regdate） 結果の行数や列の使い方を考える 実行計画の例（BitmapScan） Japan PostgreSQL User's Group 29 article表のどの列にインデックスを作る？ CREATE INDEX article_regdate_idx ON article(regdate); EXPLAIN ANALYZE select * from article where regdate=now()::date - interval '1 day'; QUERY PLAN ------------------------------------------------------------------------------------------------ Bitmap Heap Scan on article (cost=6.38..674.68 rows=268 width=74) (actual time=0.279..1.116 rows=257 loops=1) Recheck Cond: (regdate = ((now())::date - '1 day'::interval)) Heap Blocks: exact=238 -> Bitmap Index Scan on article_regdate_idx (cost=0.00..6.31 rows=268 width=0) (actual time=0.204..0.204 rows=257 loops=1) Index Cond: (regdate = ((now())::date - '1 day'::interval)) Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | 日付列は、ユーザーが指定するよりも、 月別表示だったり、日別のアクセス数集計のような 管理画面で使われますよね。
30. 並び替え（regdate） 結果の行数や列の使い方を考える 実行計画の例（sortの省略） Japan PostgreSQL User's Group 30 article表のどの列にインデックスを作る？ EXPLAIN ANALYZE select * from article ORDER BY regdate; -------------------------------------------------------------------------------------------------- Sort (cost=15084.22..15334.23 rows=100004 width=76) (actual time=136.998..157.123 rows=100・・ -> Seq Scan on article (cost=0.00..2334.04 rows=100004 width=76) (actual time=0.059..23.118 ・・ CREATE INDEX article_regdate_idx ON article(regdate); EXPLAIN ANALYZE select * from article ORDER BY regdate; -------------------------------------------------------------------------------------------------- Index Scan using article_regdate_idx on articl (cost=0.29..7940.32 rows=100004 width=76) (actual time=0.044..94.565 rows=100004 loops=1) Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | 日付列は、ユーザーが指定するよりも、 月別表示だったり、日別のアクセス数集計のような 管理画面で使われますよね。
31. インデックスが使えない例 結果の行数や列の使い方を考える 実行計画の例（中間一致） Japan PostgreSQL User's Group 31 article表のどの列にインデックスを作る？ CREATE INDEX article_main_idx ON article(maintext); EXPLAIN ANALYZE select * from article WHERE maintext LIKE 'あい%'; -------------------------------------------------------------------------------- Index Scan using article_main_idx on article (cost=0.42..8.44 rows=10 width=76) (actual time=0.021..0.044 rows=12 loops=1) Index Cond: ((maintext >= 'あい'::text) AND (maintext < 'あぅ'::text)) EXPLAIN ANALYZE select * from article WHERE maintext LIKE '%あい%'; -------------------------------------------------------------------------------- Seq Scan on article (cost=0.00..2584.05 rows=10 width=76) (actual time=0.699..121.068 rows=254 loops=1) Rows Removed by Filter: 99750 Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | 本文は、できることならフリーワード検索したいの だけど、中間一致や後方一致では、インデックスを 使った検索は難しいんですね。
32. インデックスが使えない例 結果の行数や列の使い方を考える 全文検索エンジンを導入 Japan PostgreSQL User's Group 32 article表のどの列にインデックスを作る？ 全文検索エンジン・・・文章などの長い文字列をコマ切れのキーワードにして、 そのキーワードが登場する位置と対応付ける、 独自のインデックスを作成できる拡張機能 PostgreSQLでは、標準機能では英語に対応した全文検索エンジンが提供されています。 日本語の全文検索を行うには、 「pgroonga（ぴーじーるんが）」や「pg_bigm（ぴーじーばいぐらむ）」を利用しましょう。 pgroonga https://pgroonga.github.io/ja/ pg_bigm http://pgbigm.osdn.jp/index.html Column | Type | Modifiers ------------+------+----------- article_id | text | user_id | text | regdate | date | maintext | text | 本文は、できることならフリーワード検索したいの だけど、中間一致や後方一致では、インデックスを 使った検索は難しいんですね。
33. インデックスが使えない例 結果の行数や列の使い方を考える  WHERE date_part(‘month’,regdate) = 10  WHERE regdate BETWEEN ’20171001’ AND ‘20171031’ 実行計画の例（関数の結果で検索） Japan PostgreSQL User's Group 33 article表のどの列にインデックスを作る？ EXPLAIN ANALYZE select * from article WHERE date_part('month',regdate) = 10; -------------------------------------------------------------------------------- Seq Scan on article (cost=0.00..3084.07 rows=500 width=76) (actual time=0.061..61.974 rows=90・・ Filter: (date_part(‘month’::text,(regdate)::timestamp without time zone) =‘10’::double ・・ Execution time: 62.954 ms EXPLAIN ANALYZE select * from article WHERE regdate between '20171001' and '20171031'; -------------------------------------------------------------------------------------------------- Bitmap Heap Scan on article (cost=183.62..1645.42 rows=8520 width=76) (actualtime=2.446 ・・ Recheck Cond: ((regdate >= ‘2017-10-01’::date) AND (regdate <= ‘2017-10-31’::date)) ・・ -> Bitmap Index Scan on article_regdate_idx (cost=0.00..181.49 rows=8520 width=0) ・・ Index Cond: ((regdate >= '2017-10-01'::date) AND (regdate <= '2017-10-31'::date)) Execution time: 9.194 ms 「10月分」の記事を一覧表示したい！
34. インデックスが使えない例 結果の行数や列の使い方を考える  WHERE date_part(‘month’,regdate) = 10 実行計画の例（関数の結果で検索） Japan PostgreSQL User's Group 34 article表のどの列にインデックスを作る？ /* 関数の結果を使ったインデックスを作成 */ CREATE INDEX article_regdatem_idx ON article(date_part('month',regdate)); EXPLAIN ANALYZE select * from article WHERE date_part('month',regdate) = 10; -------------------------------------------------------------------------------------------------- Bitmap Heap Scan on article (cost=12.17..996.86 rows=500 width=76) (actual time=2.939..8.71・・ Recheck Cond: (date_part('month'::text, (regdate)::timestamp without time zone) = '10'::double Heap Blocks: exact=1334 -> Bitmap Index Scan on article_regdatem_idx (cost=0.00..12.04 rows=500 width=0) (actual・・ Index Cond: (date_part('month'::text,(regdate)::timestamp without time zone) = ‘10’・・ Execution time: 9.671 ms 「10月分」の記事を一覧表示したい！ こんな検索が頻繁にあるんです！
35. 【T2】 PostgreSQLチューニング Japan PostgreSQL User's Group 35  セッションテーマ SQLでデータベースに対して検索/操作を指示することができますが、その所要 時間は環境によって様々です。データベースは、正しくメンテナンスやチューニ ングされ、性能面でも実用レベルであることが期待されます。  まとめ 性能の悪いSQLを改善するために基本となる、インデックスをつかった 「SQLチューニング」の方法をお話しました。 今日お話ししたことはチューニングの第一歩です。 実際にはもっと多くの苦労があるかもしれません。 ・どのSQLが悪いの？ ・どの列にインデックスを作成する？ ・本当にDBの問題？ コミュニティに参加して質問してみたり、解決できたら 今度は発表していただくなんて、いかがでしょうか。