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

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

ただいまの
回答率

90.35%

  • PostgreSQL

    1462questions

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

  • データベース

    901questions

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

  • データベース設計

    214questions

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

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

解決済

回答 1

投稿

  • 評価
  • クリップ 0
  • VIEW 1,114

Panzer_vor

score 1612

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

前提・実現したいこと

年月日の入ったcharacter型カラムを基にレンジパーティションを行っているが、
この時各パーティションに付与するCHECK制約に型キャストを明示せずに定義追加を行いたい。

発生している問題・エラーメッセージ

CHECK制約の定義が想定した通りとならないため全パーティションをスキャン(実行計画上)してしまう。

該当のテーブル

そのままではかけないので、
サンプルコードとなりますがに下記のようなテーブル定義となります。

CREATE TABLE "親テーブル"
(
  -- 問題のテーブルはテーブル名・カラム名など全て日本語なのでそれっぽくしている
  "ほげコード" character(4) NOT NULL,     
  "ふがコード" character(4) NOT NULL,
  "ぴよ年月日" character(8) NOT NULL,
  "重量" numeric(11, 3) DEFAULT 0,
  -- 以下何カラムかは省略しています
  PRIMARY KEY ("ほげコード", "ふがコード", "ぴよ年月日")
);

CREATE TABLE "子テーブル1"
(
  "ほげコード" character(4) NOT NULL,     
  "ふがコード" character(4) NOT NULL,
  "ぴよ年月日" character(8) NOT NULL,
  "重量" numeric(11, 3) DEFAULT 0,
  -- 以下何カラムかは省略しています
  PRIMARY KEY ("ほげコード", "ふがコード", "ぴよ年月日"),
  CONSTRAINT ”パーティション用1_check” CHECK ("ぴよ年月日"::text >= '20160101'::text AND "ぴよ年月日" < '20160201'::text)  
)
INHERITS ("親テーブル");

CREATE TABLE "子テーブル2"
(
  "ほげコード" character(4) NOT NULL,     
  "ふがコード" character(4) NOT NULL,
  "ぴよ年月日" character(8) NOT NULL,
  "重量" numeric(11, 3) DEFAULT 0,
  -- 以下何カラムかは省略しています
  PRIMARY KEY ("ほげコード", "ふがコード", "ぴよ年月日"),
  CONSTRAINT ”パーティション用2_check” CHECK ("ぴよ年月日"::text >= '20160201'::text AND "ぴよ年月日" < '20160301'::text)  
)
INHERITS ("親テーブル");

--以下年月ごとにパーティションテーブルあり

各パーティションテーブル作成時には、実際は以下のような作成スクリプト順で作成しています。

CREATE TABLE "子テーブル1"(
  LIKE "親テーブル" INCLUDING ALL
)
INHERITS("親テーブル"); 

-- パーティション用制約の追加(ここが恐らく今回の問題の要)
ALTER TABLE "子テーブル1" 
  ADD CONSTRAINT "パーティション用1_check"
  CHECK ("ぴよ年月日" >= '20160101' AND "ぴよ年月日" < '20160201');

スキャンが適切に行われていないSQLを1つ抜粋します。

SELECT
    "ほげコード"
,   "ふがコード"
,   "ぴよ年月日" 
FROM
    "親テーブル" 
WHERE
    "ぴよ年月日" 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」となっていることに気づいたため、
    もしやと試しに以下のクエリを実行してみました。
    (これを書いてる途中で気づきました・・・^^;)
SELECT
    "ほげコード"
,   "ふがコード"
,   "ぴよ年月日" 
FROM
    "親テーブル" 
WHERE
    CAST("ぴよ年月日" AS text) BETWEEN '20160101' AND '20160110'


読みどおりチェック制約によるフィルタリングかかるにはかかりましたが、
今度は主キーによるインデックススキャンが走りません。(当然といえばそうですが^^;)
以下がその時の実行計画です。

Append  (cost=0.00..23.60 rows=4 width=76)
 +-Seq Scan on "親テーブル"  (cost=0.00..0.00 rows=1 width=76)  Filter: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text))
 +-Seq Scan on "子テーブル1"  (cost=0.00..23.60 rows=3 width=76)  Filter: ((("ぴよ年月日")::text >= '20160101'::text) AND (("ぴよ年月日")::text <= '20160110'::text))
  • CHECK制約に明示的な型キャストを含み追加する
    チェック制約のつき方自体が何とかならないかとチェック制約を付与するスクリプトを以下のように変更しました。
ALTER TABLE "子テーブル1" 
  ADD CONSTRAINT "パーティション用1_check"
  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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • A.Ichi

    2016/10/30 10:42

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

    キャンセル

  • Panzer_vor

    2016/10/30 11:54

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

    キャンセル

  • A.Ichi

    2016/10/30 12:31

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

    キャンセル

回答 1

checkベストアンサー

+2

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/11/02 08:20

    > A.Ichiさん
    ご回答ありがとうございます。
    また、ご丁寧に検証していただきありがとうございます。

    以前修正依頼にてご指摘していただいた内容の確認が取れたのですが、
    ご指摘通りCHECK制約を付与された時点でvarcharだったとのことでした。

    こちらでも改めて確認し直した所、
    カラム側のキャストがされずCHECK制約の定義を行えることが確認できました。

    以前確認した際は、
    当方の確認が不足しており、
    そのまま勘違いしていたようです。
    思い込みはダメですね・・・^^;

    改めてこの度は、ご丁寧な検証・回答のほどありがとうございました!

    キャンセル

  • 2016/11/02 08:24

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

    キャンセル

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

  • ただいまの回答率 90.35%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • PostgreSQL

    1462questions

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

  • データベース

    901questions

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

  • データベース設計

    214questions

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