[pgsql-jp: 40870] Re: PostgreSQLにおける複合PKと複合INDEXの選択基準
nozawakz ＠ nttdata.co.jp
nozawakz ＠ nttdata.co.jp
2011年 7月 22日 (金) 22:32:50 JST
武田様、板垣様、川田様
野沢です。
ご回答いただき、ありがとうございます。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
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 メーリングリストの案内