index検索が遅い理由とは？
- テーブルの抽出結果が思ったよりも遅い場合、indexの使用を考慮してください。
- indexはデータの特定の列に対して高速な検索を可能にするためのデータ構造です。
- しかし、適切なindexが作成されていない場合や、データの分布が偏っている場合、indexの使用によってパフォーマンスが悪化する可能性があります。
- ベストアンサー
indexを使おうとしない間違ったcost計算
postgres 7.2.4で質問させてください。 以下のような構成のテーブルの抽出をしようとしています。 テーブル名 ：t_name カラム名 ：k1、k2、k3 インデックス：t_name_idx テーブルt_nameの全体件数は約70万件で、上記のSQLから期待 される抽出結果件数は約1万5千件です。t_nameにはk3のみを対象としたインデックス、t_name_idxが作成してあります。次のようなSQLを実行した際に、シーケンス検索になり応答までに６秒ほどかかってしまいます。 select k1,k2 from t_name where k3 in ("100","200"); explain analyzeで実行計画を見ると (cost=26.29..15304.62 rows=523 width=104) Total runtime: 6427.32 msec です。シーケンスをSET enable_seqscan TO off; で使用しないようにして、強制的にt_name_idxを使うと次のような結果になります。 (cost=0.00..42009.05 rows=523 width=104) Total runtime: 423.81 msec 実際にはindexを使用したほうが10倍以上も速いのに、プランナはシーケンス検索のほうがコストが小さいと判断しています。何故このようになってしまうのでしょうか？VACUUM ANALYZEを行っても結果は変わりませんでした。 私のイメージでは、index検索のほうがコストが小さく計算されて、それを使うべき。というイメージなのですが、考え方自体が間違っているのでしょうか？ 原因を調べ始めて１週間経ち、行き詰っています。 どなたかヒントだけでもいいので教えてください。
- rinet8223
- お礼率67% (23/34)
- PostgreSQL
- 回答数1
- ありがとう数6
- みんなの回答 （1）
- 専門家の回答
質問者が選んだベストアンサー
(1)7.2ではvacuum,analyzeに加えreindexも必要、一度すべて試行してからEXPLAINをしてみる。 (2)7.2ではIN関数が不得意なので where k3='100' or k3='200'・・・ に置き換えるか、PGversionを8.2などに上げてみる
関連するQ&A
- 速度差の理由を教えてください。
データベースの構造とインデックス、検索クエリー 全て同じ条件でテストサーバと実際のサーバで 検索を行っているのですが以下のような結果がでます。 なぜ以下の様に10倍まで差が生まれるのかを 教えていただければと思っております。 違う箇所としては、サーバのスペックですが 実際のサーバのCPU：Xeon 3.2GHz メモリ：2GB テストサーバのCPU：Pentium4 3.2GHz メモリ：2GB となります。 //テストサーバでの結果 Limit (cost=99427.05..99427.25 rows=4 width=112) (actual time=4022.29..4022.31 rows=4 loops=1) -> Unique (cost=99427.05..99438.37 rows=226 width=112) (actual time=4022.29..4022.30 rows=5 loops=1) -> Sort (cost=99427.05..99432.71 rows=2265 width=112) (actual time=4022.29..4022.29 rows=6 loops=1) Sort Key: s.saorderid, s.saserialid, o.odlastupdate -> Merge Join (cost=96997.70..99300.85 rows=2265 width=112) (actual time=3363.93..4022.11 rows=40 loops=1) Merge Cond: ("outer".odid = "inner".saorderid) -> Sort (cost=8054.50..8059.58 rows=2033 width=68) (actual time=13.52..13.55 rows=38 loops=1) Sort Key: o.odid -> Index Scan using order1_odctmid_index on order1 o (cost=0.00..7942.81 rows=2033 width=68) (actual time=13.13..13.47 rows=38 loops=1) Index Cond: (odctmid = 3403::bigint) -> Sort (cost=88943.20..90075.54 rows=452935 width=44) (actual time=3344.05..3688.13 rows=452002 loops=1) Sort Key: s.saorderid -> Seq Scan on sale s (cost=0.00..12792.35 rows=452935 width=44) (actual time=0.02..756.03 rows=452935 loops=1) Total runtime: 4050.02 msec //実際の本番サーバでの結果 Limit (cost=73933.37..73933.39 rows=1 width=128) (actual time=43102.58..43102.59 rows=4 loops=1) -> Unique (cost=73933.37..73933.39 rows=1 width=128) (actual time=43102.58..43102.59 rows=5 loops=1) -> Sort (cost=73933.37..73933.38 rows=5 width=128) (actual time=43102.57..43102.58 rows=6 loops=1) Sort Key: s.saorderid, s.saserialid, o.odlastupdate -> Nested Loop (cost=0.00..73933.32 rows=5 width=128) (actual time=998.20..43102.38 rows=40 loops=1) Join Filter: ("outer".odid = "inner".saorderid) -> Index Scan using order1_odctmid_index on order1 o (cost=0.00..18.12 rows=4 width=68) (actual time=6.01..6.99 rows=38 loops=1) Index Cond: (odctmid = 3403::bigint) -> Seq Scan on sale s (cost=0.00..12797.54 rows=448254 width=60) (actual time=0.01..756.51 rows=455437 loops=38) Total runtime: 43102.69 msec もしクエリー文もあった方がよければ お知らせ下さい。 すみませんが、どなたか教えて頂ければ と思います。よろしくお願い致します。
- ベストアンサー
- PostgreSQL
- 前方一致が動的に変更される場合にINDEXは有効になりません。
いつもお世話になっております。 テーブル同士のLIKE検索（前方一致）を行った場合、 INDEXが有効になりません。 LIKE検索文字列を固定にした場合は、INDEXが有効 になります。 テーブル同士のLIKE（前方一致）でINDEXを 有効にする手段をご教授頂けないでしょうか。 以下、実行結果です。 (1)LIKE検索文字列が固定 ---------------------------------------------------------------------------- EXPLAIN SELECT * FROM wk_dss_data WHERE sys_id LIKE 'M004964%'; ---------------------------------------------------------------------------- Index Scan using i_wk_dss_data_02 on wk_dss_data (cost=0.00..5.25 rows=1 width=311) Index Cond: (((sys_id)::text >= 'M004964'::character varying) AND ((sys_id)::text < 'M004965'::character varying)) Filter: ((sys_id)::text ~~ 'M004964%'::text) (2)テーブル同士のLIKE検索 ---------------------------------------------------------------------------- EXPLAIN SELECT wk_dss_data.comp_id, wk_dss_data.user_id FROM wk_dss_data, t_update WHERE wk_dss_data.sys_id LIKE t_update.sys_id||'%'; ---------------------------------------------------------------------------- Nested Loop (cost=157.00..12505636.00 rows=2500000 width=20) Join Filter: (("outer".sys_id)::text ~~ (("inner".sys_id)::text || '%'::text) ) -> Seq Scan on wk_dss_data (cost=0.00..5479.00 rows=100000 width=31) -> Materialize (cost=157.00..207.00 rows=5000 width=11) -> Seq Scan on t_update (cost=0.00..157.00 rows=5000 width=11)
- ベストアンサー
- PostgreSQL
- 【ANALYZE】 テーブル・インデックス数が多い
過去の質問を検索しても該当するものが無かったのでご質問させていただきます。 現在、オプティマイザ（コストベース）について調べています。 ANALYZEコマンドを使用して、テーブルおよびインデックスの 統計情報を取りたいのですが、以下のコマンドのようにテーブル名もしくはインデックス名を指定することしかできないのでしょうか？ ANALYZE TABLE テーブル名 COMPUTE STATISTICS; ANALYZE INDEX インデックス名 COMPUTE STATISTICS; テーブル数及びインデックス数が非常に多いため、上記の方法では手間がかかります。 もう少し効率の良い方法を探しているのですが、ご存知であれば教えてください。 よろしくお願いいたします。
- ベストアンサー
- Oracle
- インデックスについて
テーブルは2つ ・offacc_siwakedata ・off_projectmster とあり、それぞれ下記インデックスを張っております。 create index siwakedata_index ON offacc_siwakedata( siwakedata_date, siwakedata_hdcd, siwakedata_cd, siwakedata_kamokucode, siwakedata_projectcode, siwakedata_exzankada, siwakedata_default, siwakedata_compcd, siwakedata_del ); CREATE INDEX projectmster_index ON off_projectmster( projectmster_cd, projectmster_del, projectmster_compcd ); ２つのテーブルを結合させる下記SQLを発行 explain select offacc_siwakedata.*, off_projectmster.projectmster_name from offacc_siwakedata,off_projectmster where off_projectmster.projectmster_cd=siwakedata_projectcode and off_projectmster.projectmster_del='1' and off_projectmster.projectmster_compcd='200' and siwakedata_compcd='200' and siwakedata_del='1' and siwakedata_default='1' and siwakedata_kamokucode='26300' and siwakedata_exzankada<>0 order by siwakedata_date, siwakedata_hdcd, siwakedata_cd ; explainするとoff_projectmsterのインデックスが有効になっていません。 何ででしょうか？ 教えてください。 宜しくお願いします。 "Nested Loop (cost=0.00..17438.04 rows=1 width=1307)" " Join Filter: ((off_projectmster.projectmster_cd)::text = (offacc_siwakedata.siwakedata_projectcode)::text)" " -> Index Scan using siwakedata_index on offacc_siwakedata (cost=0.00..17436.98 rows=1 width=1244)" " Index Cond: (((siwakedata_kamokucode)::text = '26300'::text) AND ((siwakedata_default)::text = '1'::text) AND ((siwakedata_compcd)::text = '200'::text) AND (siwakedata_del = 1))" " Filter: (siwakedata_exzankada <> 0::numeric)" " -> Seq Scan on off_projectmster (cost=0.00..1.04 rows=1 width=96)" " Filter: ((projectmster_del = 1) AND ((projectmster_compcd)::text = '200'::text))"
- 締切済み
- PostgreSQL
- テーブル定義書作成時のIndex付加について
基礎的なことで申し訳ございませんが、 お力添えよろしくお願いいたします。 CentOs 5 & PostgreSQL 8.4.9にてDB構築しております。 DB構築から、テーブル作成・項目追加・キー設定まで行いまして、 検索する際に、抽出件数が多く負荷のかかりそうなものに Indexを作成しようと考えております。 ■本題 １．Indexを作成する対象となる項目の、目安としてどのようなものが考えられるでしょうか。 ・キー対象の項目になっているもの ・検索条件の対象となる項目なども当たるでしょうか ２．項目をまとめて一つのIndexにするメリット・デメリットはどのようなものでしょうか ３．テーブル結合したときのIndexの動作 例) 下記のようなテーブルがあった場合 tablea(データ50000件程度) hoge_id pkey1 hoge_no hoge_nm ... tableb(データ150000件程度) test_id pkey1 hoge_id pkey2 hoge_betu_kb ... select t1.hoge_id, t1.hoge_nm, t2.hoge_betu_kb rom tablea as t1 leftjoin tableb as t2 on t1.hoge_id = t2.hoge_id where t1.hoge_id > '100' and t1.hoge_nm like 'あいう%' このような場合のIndex作成は、 A. Create index idx_tablea_hoge_id ON tablea (hoge_id); B. Create index idx_tablea_hoge_id ON tablea (hoge_nm); C. Create index idx_tableb_hoge_id ON tableb (hoge_id); ←tableaで指定しているのでこれは不要？ ４．A.B.C.のようなIndex作成した際のテーブル定義の書き方 テーブル定義書のフォーマットは定められており、お門違いな質問かとは存じてはおりますが、 テーブル定義にIndex定義を書く場合に、添付した画像のようなフォーマットの書き方が よく理解できずお気づきの点などございましたら、ご指摘願えたら助かります。 （以前は、CSEツールなどで自動出力される定義書のような書き方をしていたもので・・・） 例）tablea IDX1 IDX2 IDX3 hoge_id 1 hoge_nm 2 このような書き方になるのかどうか？ IDX2, IDX3の項目とは？どのようなIndex定義をした際に書き込むことになるのかが判らず。。。 基礎から勉強しなおすべきとは存じておりますが。。。 現状の期間あまり時間がないことと、 DB構築を一人で行っており、このような内容を相談できるものがおりませんゆえ、 改めて、Index付加することを考えてみると、いまさら聞けないような内容かもしれませんが ちゃんと把握したうえで、テーブル構築したいと思いますので 今までの他の皆様の経験からのノウハウなども踏まえて、 ご教授いただけると大変助かります。
- ベストアンサー
- PostgreSQL
- mysqlのインデックスについて質問です。
mysqlのインデックスについて質問です。 http://archiva.jp/web/server-side/sql_02.html に、 『mysqlでは1つのクエリ実行で、1つのテーブルにつき1つのインデックスしか使用できない』 とあります。 よく以下のようなテーブル定義を見かけますが、 CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) NOT NULL default '0', `user_id` int(10) NOT NULL default '0', `file_id` int(10) NOT NULL default '0', `name` text collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; idとuser_idを条件にするクエリであれば、上記制約を満たすため、 KEY `id_user_id_idx` (`id`,`user_id`) の複合クエリを定義すれば良いのでしょうか？ また、idとuser_idとfile_idを条件にするクエリも考慮に入れた場合、 テーブル定義は以下のようにするのでしょうか？ CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) NOT NULL default '0', `user_id` int(10) NOT NULL default '0', `file_id` int(10) NOT NULL default '0', `name` text collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `id_user_id_idx` (`id`,`user_id`), KEY `id_user_id_file_id_idx` (`id`,`user_id`,`file_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 最後に、create table 時のインデックスの指定は、 INDEX `user_id` (`user_id`), のようにもできますが、INDEXとKEYの違いは何かあるのでしょうか。
- ベストアンサー
- MySQL
- Oracle 実行計画について
数千万件が格納されているテーブルにINDEXキーを新設して そのINDEXキーを条件句にもつSQL文にヒント句をつけて必ず参照するように変更し、 実行計画を取得したら、新設したINDEXキーを参照してはいるのですが、 逆にRowsやBYTESなどが増加してしまいました。 Rows | Bytes | Cost ⇒ Rows | Bytes | Cost 1 | 23 | 5 2862 |65826 | 16 ただ、体感速度はINDEXキーを新設した方が早いです。 この場合考えられる原因とは何がありますか？ Oracle１１gです。
- 締切済み
- Oracle
- 順序のEXP/IMPについて
Oracle9i 9.2.0 テーブルのデータも権限もなく、順序のみをEXPしてIMPしたいのですが、どのようにしたらよいでしょうか？ //EXP C:\oracle\ora92\bin\EXP.EXE ユーザ/パス@サービス file='C:\DB移行作業 \SEQUENCE_EXP.DMP' ROWS=N INDEXES=N TRIGGERS=N //IMP C:\oracle\ora92\bin\imp.exe toshi/toshi@grjctc2 file='DB移行作業\SEQUENCE_EXP.DMP' FEEDBACK=100000 IGNORE=Y ROWS=N INDEXES=N CONSTRAINTS=N ANALYZE=N TABLES=(住所テーブル) 上記で一応うまく登録されたようですが、 よくわかりません。 どなたかアドバイスお願いします。
- 締切済み
- Oracle
- HTML文書の文法をチェックの事で
HTML文書の文法をチェックの事でお伺いします。 xhtmlでページを作成しています。 質問なのですがこのサイトで「Another HTML-lint gateway」（HTML文書の文法をチェックし、採点します） http://openlab.ring.gr.jp/k16/htmllint/htmllint.html 文法をチェックしているのですがアクセス解析のタグを付けていると、かなりマイナス点となってしまいます。 以下はあるアクセス解析のタグなのですが、なるべく減点されないようにするにはどうすればいいのでしょうか？ <!-- アクセス解析のタグ --> <img name="PageNo" src="dummy" alt="1" width="1" height="1"> <script type="text/javascript">document.write('<img src="http://*********/access/other/analyze.cgi?1_' + screen.width + 'x' + screen.height + '&' + screen.colorDepth + '&' + document.referrer + '" alt="" width="1" height="1">');</script> <noscript><img src="http://*********/access/other/analyze.cgi" alt="dummy" width="1" height="1"></noscript> <img name="home_url" src="http://*********/access/other" alt="dummy" width="1" height="1"><img name="1st-biz-img" alt="dummy" width="1" height="1"><SCRIPT type="text/javascript" src="http://*********/access/other/analyze/seidoku.js"></SCRIPT> <img name="1st-biz-img2" alt="dummy" width="1" height="1"><SCRIPT type="text/javascript" src="http://*********/access/other/analyze/taizai.js"></SCRIPT> <img src="http://*********/access/other/analyze/getrank.cgi" alt="dummy" width="1" height="1"> アドバス等ありましたら教えてください。よろしくお願いします。
- 締切済み
- HTML
- コストベース・オプティマイザについて。
オプティマイザには、 1)ルールベース・オプティマイザ(RBO) 2)コストベース・オプティマイザ(CBO) があります。 RBO は、決められたアクセスパスの優先順位に従って 実行計画を選択することが分かりました。 CBO は、最適なアクセスパスを選択する為に、 オプティマイザ統計を取得し、実行計画のコストを 見積もることが分かりました。 ★このオプティマイザ統計に関して、質問があります。 オプティマイザ統計は、ANALYZE や DBMS_STATS パッケージを 使用することで取得する統計情報ということが分かりました。 しかし、統計情報を取得することでどのように実行計画が 改善されるのか、この部分のイメージが掴めません。 ================================================================ 例えば、表の統計情報を取得すると、以下のようになりました。 SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT 2 ,AVG_ROW_LEN 3 from user_tables where table_name='EMP'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ------ ------------ --------- ---------- ----------- 15 5 0 0 0 35 ================================================================ それぞれのカラムの意味は以下であることが分かりました。 《上記のカラムの説明》 NUM_ROWS = 行数 BLOCKS = 使用ブロック数 EMPTY_BLOCKS = 未使用ブロック数 AVG_SPACE = 空き領域の平均サイズ(bytes) CHAIN_CNT = 行連鎖・行移行の行数 AVG_ROW_LEN = 行の平均長(bytes) 上記の統計情報を取得することで、どのように実行計画を定めているのでしょうか。 統計情報を取得することで、どのような意味があるのでしょうか。 宜しければ、教えて頂きたいと思います。
- ベストアンサー
- Oracle
お礼
お礼が遅くなり、申し訳ありません。 ＞(1)7.2ではvacuum,analyzeに加えreindexも必要、一度すべて試行し ＞てからEXPLAINをしてみる。 reindexは初耳でした、少し調べてから使用して問題なさそうならやってみます。 ＞(2)7.2ではIN関数が不得意なので ＞ where k3='100' or k3='200'・・・ ＞に置き換えるか、PGversionを8.2などに上げてみる inのほうが速いと思っていたのですが、違うのですね。 PGversionは保守の観点からむやみに変更できないので、（１）を試してみます。ありがとうございました。