2011年7月22日

[pgsql-jp: 40870] Re:PostgreSQLにおける複合PKと複合INDEXの選択基準

武田様、板垣様、川田様

野沢です。
ご回答いただき、ありがとうございます。


ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
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@xxxxx [mailto:pgsql-jp-bounces@xxxxx] 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@xxxxx 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)
>
>
>
> 以上、よろしくお願い致します。
>
>

投稿者 xml-rpc : 2011年7月22日 22:32
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/105266
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。