[pgsql-jp: 40871] Re: PostgreSQLにおける複合PKと複合INDEXの選択基準
川田 寛
kawada.hiroshi ＠ nttcom.co.jp
2011年 7月 24日 (日) 09:47:38 JST
野沢様
川田です。
> ＞idx_t_z_02の索引はa→bのパスを経由できなくなるため、
> ＞必然的に主キーの索引(a→b→c)を使うことになると思います。
>
> 下記のように順番を入替えましたが
> 実行計画は変わりませんでした。
説明が不足して申し訳ありません。
SELECT文自体は一度順序を意識させないツリーに変換されてから実行されるた
め、順序を変更しても、索引の利用方法に影響は現れないと考えられます。
# 但し、テーブル結合順序も含めた場合は、「〜_collapse_limit」の
閾値次第で実行計画にインパクトがありますが。。。
私がお伝えしたかったことですが、
「idx_t_z_02」をcreateする際ですが、
索引の順序を「a→c→b」で作成です。
CREATE INDEX idx_t_z_02 on t_z ( a, c, d );
テーブル・索引作成時に、上記のDDLで実行して下さい。
SELECT文とは異なり、
DDLは宣言したカラムの順番が物理的な「並び」に影響を与えるため、
実行計画にも影響が現れます。
もし他のクエリでa→bのペアで検索が必要になった場合に、
idx_t_z_02が利用できなくなりますが、
主キー「pk_t_z」の複合索引の最初2つのカラムがa→bの順で並んでいるため、
こちらが利用され、問題にならないと考えらえられます。
以上、説明不足で申し訳ございませんでした。
(2011/07/22 22:32), nozawakz ＠ nttdata.co.jp wrote:
> 武田様、板垣様、川田様
>
> 野沢です。
> ご回答いただき、ありがとうございます。
>
>
> ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
> C言語には疎いですが、、
> 手探りでソース(8.4.8)の方を調べてみました。
> prepunion.cの下記のところが該当すると考えております。
>
> 下記のコストによって、Planが選択され、
> startup_cost : 最初の行を取得するまでのコスト
> total_cost : 全行を取得するコスト
>
> allpaths.cのset_baserel_size_estimates
> でどのINDEXを選択するか決定していると予想しています。
>
> 最終的にはどの個所で決定しているか、
> 支配項（インデックスサイズ等）が記載されている場所が特定できないのですが、
> お手数ですが、どの場所になるかご教授お願いできないでしょうか。
>
> ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
>
> ●武田様●
>
> ＞DDL（各カラムのデータ型）が解らないので何とも言えないのですが、
>
> where句のカラムのデータ型は下記の通りとなっております。
>
> a -> bigint NOT NULL,
> c -> character(15) NOT NULL,
> b -> details_snum integer NOT NULL,
> e -> logic_del_flag character(1) DEFAULT 'n'::bpchar,
>
> なお、実際のSQLは下記のように
> Where句でないですが、X3〜X12もございます。
> （実行計画には関係ないと思いますが、念のため）
>
> SELECT /* SELECT_SMS_0110 */
> a,
> b,
> c,
> x1,
> x2,
> x3,
> x4,
> x5,
> x6,
> x7,
> x8,
> x9,
> x10,
> x11,
> x12
> FROM t_z
> WHERE a = CAST('86129' AS BIGINT)
> AND c = '10086063 '
> AND b = CAST('1' AS INTEGER)
> AND e = 'n' ;
>
> 追加で気付いた点がございましたら、
> アドバイスをよろしくお願い致します。
>
>
> ●板垣様●
>
> ＞(a, b) までで、十分な絞込みができると判断されたのだと思います。
> ＞インデックスの選択は、確かに絞込みが強いものが優先されることが
> ＞多いのですが、ほかにもインデックスのサイズが考慮されます。
> ＞pg_relation_size() などをつかって、pk_t_z と idx_t_z_02 の
> ＞ファイルサイズを比べてみてください。
>
> 下記の通り、ファイルサイズを比べてみたところ、
>
> SELECT pg_relation_size('pk_t_z'); --> 39813120
> SELECT pg_relation_size('idx_t_z_01'); --> 25264128
> SELECT pg_relation_size('idx_t_z_02'); --> 39813120
>
> とサイズは同じでした。
>
>
> ●川田様●
>
> ＞実行計画最適化のフェーズについて詳細は解りかねる上、
> ＞データの分布についても把握していないため、
> ＞あまり的確なアドバイスが出来かねますが、、、
> ＞頂いた情報のみで判断した場合、、、
>
> 少ない情報でアドバイスをいただき、
> ありがとうございます。
>
> ＞現在idx_t_z_02は、a→b→dの順で宣言されていると思いますが、
> ＞これをa→c→bの順で宣言。
> ＞
> ＞idx_t_z_02の索引はa→bのパスを経由できなくなるため、
> ＞必然的に主キーの索引(a→b→c)を使うことになると思います。
>
> 下記のように順番を入替えましたが
> 実行計画は変わりませんでした。
>
> SELECT
> a,
> b,
> c,
> FROM t_z
> WHERE a = CAST(:subscriberId AS BIGINT)
> AND b = :serviceContactId
> AND c = CAST(:detailsSnum AS INTEGER)
> AND e = 'n';
>
>
> 以上、よろしくお願い致します。
>
> -----Original Message-----
> From: pgsql-jp-bounces ＠ ml.postgresql.jp [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of 川田 寛
> Sent: Sunday, July 17, 2011 5:15 PM
> To: PostgreSQL Japanese Mailing List
> Subject: [pgsql-jp: 40861] Re: PostgreSQLにおける複合PKと複合INDEXの選択基準
>
> 野沢さん
>
>
> 川田です。
>
> 実行計画最適化のフェーズについて詳細は解りかねる上、
> データの分布についても把握していないため、
> あまり的確なアドバイスが出来かねますが、、、
> 頂いた情報のみで判断した場合、、、
>
> 現在idx_t_z_02は、a→b→dの順で宣言されていると思いますが、
> これをa→c→bの順で宣言。
>
> idx_t_z_02の索引はa→bのパスを経由できなくなるため、
> 必然的に主キーの索引(a→b→c)を使うことになると思います。
>
> ---
> 板垣様から索引サイズの確認というアドバイスがありましたが、
> 私も以前試したことがあります。
>
> 今回のケースのような、索引指定されたカラムの型、数、が同じの場合、
> 索引ファイルのサイズが同じになりました。
> 10万レコードほど登録しanalyzeしても、
> 最適と考えられる索引が選択されず、野沢さんと同じような状態に陥りました。
>
> PostgreSQLの索引の作りについては、
> コードを読んでいないため今の所把握していませんが、
> 今回のようなケースにおいて、
> 索引選択のミスに繋がることがあると、私は認識しています。
> # 以前同じような問題で悩まされたことがあります。
>
> お詳しいかたがいれば、是非アドバイスいただきたいところですが。
>
>
> 以上、共有でした。
>
> (2011/07/14 22:14), nozawakz ＠ nttdata.co.jp wrote:
>> お世話になっております。野沢と申します。
>>
>>
>> PostgreSQLにおける複合PKと複合INDEXの選択基準について
>> 質問させてください。
>>
>> 下記のSQL（※）では、第1PK、第2PK、第3PKで一意に絞り込まれるため、複合PK（pk_t_z）を使っての実行計画が選択されることを
>> 期待していたのですがExplain文で取得みると複合INDEX（idx_t_z_02）の方が選択されておりました。
>>
>> PostgreSQLではHOTの「インデックス・エントリの追加をスキップ」する機能があるため、
>> 優先的に複合PKよりも複合INDEXが選択されるのでしょうか。
>> PostgreSQLにおける複合PKと複合INDEXの選択基準があれば合わせてご教授ください。
>>
>> Explain対象SQL、実行計画、実行時INDEX情報は下記のとおりです。
>>
>> ※[Explain対象SQL]
>> SELECT
>> a,
>> b,
>> c,
>> FROM t_z
>> WHERE a = CAST(:subscriberId AS BIGINT)
>> AND c = CAST(:detailsSnum AS INTEGER)
>> AND b = :serviceContactId
>> AND e = 'n';
>>
>> [実行計画]
>> Index Scan using idx_t_z_02 on t_z (cost=0.00..8.47 rows=1 width=505) (actual time=74.420..74.421 rows=1 loops=1)
>> Index Cond: ((a = 86129::bigint) AND (b = '10086063 '::bpchar))
>> Filter: ((c = 1) AND (e = 'n'::bpchar))
>> Total runtime: 74.490 ms
>> (4 行)
>>
>> [実行時INDEX情報]
>> インデックス:
>> "pk_t_z" PRIMARY KEY, btree (a, b, c)
>> "idx_t_z_01" btree (b)
>> "idx_t_z_02" btree (a, b, d)
>>
>>
>>
>> 以上、よろしくお願い致します。
>>
>>
>
>
>
pgsql-jp メーリングリストの案内