いつもお世話になっております。
投稿するのは初となるので至らぬ所があったらご容赦下さい。
###前提・実現したいこと
年月日の入ったcharacter型カラムを基にレンジパーティションを行っているが、
この時各パーティションに付与するCHECK制約に型キャストを明示せずに定義追加を行いたい。
###発生している問題・エラーメッセージ
CHECK制約の定義が想定した通りとならないため全パーティションをスキャン(実行計画上)してしまう。
###該当のテーブル
そのままではかけないので、
サンプルコードとなりますがに下記のようなテーブル定義となります。
SQL
1CREATE TABLE "親テーブル" 2( 3 -- 問題のテーブルはテーブル名・カラム名など全て日本語なのでそれっぽくしている 4 "ほげコード" character(4) NOT NULL, 5 "ふがコード" character(4) NOT NULL, 6 "ぴよ年月日" character(8) NOT NULL, 7 "重量" numeric(11, 3) DEFAULT 0, 8 -- 以下何カラムかは省略しています 9 PRIMARY KEY ("ほげコード", "ふがコード", "ぴよ年月日") 10); 11 12CREATE TABLE "子テーブル1" 13( 14 "ほげコード" character(4) NOT NULL, 15 "ふがコード" character(4) NOT NULL, 16 "ぴよ年月日" character(8) NOT NULL, 17 "重量" numeric(11, 3) DEFAULT 0, 18 -- 以下何カラムかは省略しています 19 PRIMARY KEY ("ほげコード", "ふがコード", "ぴよ年月日"), 20 CONSTRAINT ”パーティション用1_check” CHECK ("ぴよ年月日"::text >= '20160101'::text AND "ぴよ年月日" < '20160201'::text) 21) 22INHERITS ("親テーブル"); 23 24CREATE TABLE "子テーブル2" 25( 26 "ほげコード" character(4) NOT NULL, 27 "ふがコード" character(4) NOT NULL, 28 "ぴよ年月日" character(8) NOT NULL, 29 "重量" numeric(11, 3) DEFAULT 0, 30 -- 以下何カラムかは省略しています 31 PRIMARY KEY ("ほげコード", "ふがコード", "ぴよ年月日"), 32 CONSTRAINT ”パーティション用2_check” CHECK ("ぴよ年月日"::text >= '20160201'::text AND "ぴよ年月日" < '20160301'::text) 33) 34INHERITS ("親テーブル"); 35 36--以下年月ごとにパーティションテーブルあり
各パーティションテーブル作成時には、実際は以下のような作成スクリプト順で作成しています。
SQL
1CREATE TABLE "子テーブル1"( 2 LIKE "親テーブル" INCLUDING ALL 3) 4INHERITS("親テーブル"); 5 6-- パーティション用制約の追加(ここが恐らく今回の問題の要) 7ALTER TABLE "子テーブル1" 8 ADD CONSTRAINT "パーティション用1_check" 9 CHECK ("ぴよ年月日" >= '20160101' AND "ぴよ年月日" < '20160201');
スキャンが適切に行われていないSQLを1つ抜粋します。
SQL
1SELECT 2 "ほげコード" 3, "ふがコード" 4, "ぴよ年月日" 5FROM 6 "親テーブル" 7WHERE 8 "ぴよ年月日" BETWEEN '20160101' AND '20160110'
以下実行計画のサンプルです。
本来は子テーブル1だけ検索させる必要があり、
子テーブル2と子テーブル3のスキャンは余計です。
Append (cost=0.00..54.20 rows=10 width=76) +-Seq Scan on "親テーブル" (cost=0.00..0.00 rows=1 width=76) Filter: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) +-Bitmap Heap Scan on "子テーブル1" (cost=10.95..18.07 rows=3 width=76) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) | +-Bitmap Index Scan on "子テーブル1_pkey" (cost=0.00..10.95 rows=3 width=0) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) +-Bitmap Heap Scan on "子テーブル2" (cost=10.95..18.07 rows=3 width=76) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) | +-Bitmap Index Scan on "子テーブル2_pkey" (cost=0.00..10.95 rows=3 width=0) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) +-Bitmap Heap Scan on "子テーブル3" (cost=10.95..18.07 rows=3 width=76) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) +-Bitmap Index Scan on "子テーブル3_pkey" (cost=0.00..10.95 rows=3 width=0) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar))
###試したこと
- ぴよ年月日をtext型にキャストする
CHECK制約を観察してると「"ぴよ年月日"::text」となっていることに気づいたため、
もしやと試しに以下のクエリを実行してみました。
(これを書いてる途中で気づきました・・・^^;)
SQL
1SELECT 2 "ほげコード" 3, "ふがコード" 4, "ぴよ年月日" 5FROM 6 "親テーブル" 7WHERE 8 CAST("ぴよ年月日" AS text) BETWEEN '20160101' AND '20160110'
読みどおりチェック制約によるフィルタリングかかるにはかかりましたが、
今度は主キーによるインデックススキャンが走りません。(当然といえばそうですが^^;)
以下がその時の実行計画です。
SQL
1Append (cost=0.00..23.60 rows=4 width=76) 2 +-Seq Scan on "親テーブル" (cost=0.00..0.00 rows=1 width=76) Filter: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text)) 3 +-Seq Scan on "子テーブル1" (cost=0.00..23.60 rows=3 width=76) Filter: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text))
- CHECK制約に明示的な型キャストを含み追加する
チェック制約のつき方自体が何とかならないかとチェック制約を付与するスクリプトを以下のように変更しました。
SQL
1ALTER TABLE "子テーブル1" 2 ADD CONSTRAINT "パーティション用1_check" 3 CHECK ("ぴよ年月日" >= '20160101'::bpchar AND "ぴよ年月日" < '20160201'::bpchar);
上記は想定どおりに動作し、
フィルタリングの方もキャストなしで動作するようにはなりました。
###お聞きしたいこと
自分の家の端末上のPostgreSQLで動作確認をしてみたのですが、
上記で記載したとおり明示的にキャストしなくても想定どおり、
CHECK'("ぴよ年月日" >= '20160101'::bpchar AND "ぴよ年月日" < '20160201'::bpchar)
と定義されます。
それに対して問題が発生している環境では、
CHECK ("ぴよ年月日"::text >= '20160101'::text AND "ぴよ年月日" < '20160201'::text)
と定義されるのが解せない所です。
何かこのあたりに関わる設定ファイル上の項目名など存在したりするのでしょうか?
###補足情報(言語/FW/ツール等のバージョンなど)
PostgreSQL 9.5
恐らくvarchar型を利用したならば今回のようなトラブルもないように思われるのですが、
あえてchar型を利用する前提でお答えいただけたらと存じます。
以上長文となり申し訳ありませんが、
PostgreSQLに詳しい方、ご助力をお願い致しますm(__)m
回答1件
あなたの回答
tips
プレビュー