質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

Q&A

解決済

1回答

2794閲覧

char型カラムを利用したパーティショニングのCHECK制約について

Panzer_vor

総合スコア1636

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

0グッド

0クリップ

投稿2016/10/29 18:03

いつもお世話になっております。
投稿するのは初となるので至らぬ所があったらご容赦下さい。

###前提・実現したいこと
年月日の入った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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

A.Ichi

2016/10/30 01:42

親テーブルの”ぴよ年月日”が初期にはvarcharで後で、character(8)に変更された事は有りませんでしょうか?
Panzer_vor

2016/10/30 02:54

コメントありがとうございます。テーブル定義スクリプトを渡された段階ではcharacter(8)でしたが、変更があったかまでは把握できてないので週明けに確認してみます。こちらで既にchar型でとなっているもののCHECK制約をDROPしてからALTERで定義し直した際はダメでした。
A.Ichi

2016/10/30 03:31

V9.3でのテストですが、check制約作成する時点でTYPEを決定している様な動きをしてましたので、お尋ねしました。
guest

回答1

0

ベストアンサー

V9.3上で下記のテストを行ってみました。実行計画は、check制約を作成したTYPEを使って行われる様になりました。

(1)親テーブルを"ぴよ年月日" varchar(8) NOT NULL&作成子テーブルを作成
(2)親テーブルの"ぴよ年月日"をcharacter(8)に変更
(3)check制約をDROP&CREATE

以下テストログです。
(1)親テーブルを"ぴよ年月日" varchar(8) NOT NULL&作成子テーブルを作成

Table "public.子テーブル1" Column | Type | Modifiers | Storage | Stats target | Description ------------+----------------------+-----------+----------+--------------+------------- ほげコード | character(4) | not null | extended | | ふがコード | character(4) | not null | extended | | ぴよ年月日 | character varying(8) | not null | extended | | 重量 | numeric(11,3) | default 0 | main | | Indexes: "子テーブル1_pkey" PRIMARY KEY, btree ("ほげコード", "ふがコード", "ぴよ年月日") Check constraints: "パーティション用1_check" CHECK ("ぴよ年月日"::text >= '20160101'::text AND "ぴよ年月日"::text < '20160201'::text) Inherits: "親テーブル" Has OIDs: no QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ ----------- Append (cost=0.00..18.07 rows=4 width=74) (actual time=0.021..0.021 rows=0 loops=1) -> Seq Scan on "親テーブル" (cost=0.00..0.00 rows=1 width=74) (actual time=0.003..0.003 rows=0 loops=1) Filter: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text)) -> Bitmap Heap Scan on "子テーブル1" (cost=10.95..18.07 rows=3 width=74) (actual time=0.009..0.009 rows=0 loops=1) Recheck Cond: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text)) -> Bitmap Index Scan on "子テーブル1_pkey" (cost=0.00..10.95 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text)) Total runtime: 0.090 ms (8 rows)

(2)親テーブルの"ぴよ年月日"をcharacter(8)に変更

QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ ----------- Append (cost=0.00..53.60 rows=10 width=76) (actual time=0.028..0.028 rows=0 loops=1) -> Seq Scan on "親テーブル" (cost=0.00..0.00 rows=1 width=76) (actual time=0.002..0.002 rows=0 loops=1) Filter: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Heap Scan on "子テーブル1" (cost=10.75..17.87 rows=3 width=76) (actual time=0.009..0.009 rows=0 loops=1) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Index Scan on "子テーブル1_pkey" (cost=0.00..10.75 rows=3 width=0) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Heap Scan on "子テーブル2" (cost=10.75..17.87 rows=3 width=76) (actual time=0.005..0.005 rows=0 loops=1) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Index Scan on "子テーブル2_pkey" (cost=0.00..10.75 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Heap Scan on "子テーブル3" (cost=10.75..17.87 rows=3 width=76) (actual time=0.004..0.004 rows=0 loops=1) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Index Scan on "子テーブル3_pkey" (cost=0.00..10.75 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) Total runtime: 0.079 ms (16 rows)

(3)check制約をDROP&CREATE

QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ ----------- Append (cost=0.00..19.86 rows=4 width=76) (actual time=0.019..0.063 rows=8 loops=1) -> Seq Scan on "親テーブル" (cost=0.00..1.99 rows=1 width=76) (actual time=0.013..0.025 rows=8 loops=1) Filter: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) Rows Removed by Filter: 16 -> Bitmap Heap Scan on "子テーブル1" (cost=10.75..17.87 rows=3 width=76) (actual time=0.008..0.008 rows=0 loops=1) Recheck Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) -> Bitmap Index Scan on "子テーブル1_pkey" (cost=0.00..10.75 rows=3 width=0) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (("ぴよ年月日" >= '20160101'::bpchar) AND ("ぴよ年月日" <= '20160110'::bpchar)) Total runtime: 0.124 ms (9 rows)

投稿2016/10/30 22:55

A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Panzer_vor

2016/11/01 23:20

> A.Ichiさん ご回答ありがとうございます。 また、ご丁寧に検証していただきありがとうございます。 以前修正依頼にてご指摘していただいた内容の確認が取れたのですが、 ご指摘通りCHECK制約を付与された時点でvarcharだったとのことでした。 こちらでも改めて確認し直した所、 カラム側のキャストがされずCHECK制約の定義を行えることが確認できました。 以前確認した際は、 当方の確認が不足しており、 そのまま勘違いしていたようです。 思い込みはダメですね・・・^^; 改めてこの度は、ご丁寧な検証・回答のほどありがとうございました!
A.Ichi

2016/11/01 23:24

ご丁寧に有難うございます。私も運用中のDBを改めて確認してしまいました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問