postgresql 9.6パーティションテーブルテストスキームと記録
27917 ワード
一、pg 9パーティションテーブル試験環境の準備
テスト環境でpg 9テスト環境を作成し、pg 9インスタンスに接続します.
/usr/local/pgsql101/bin/psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
--テストライブラリの作成
\l
CREATE DATABASE pg_9_db;
\l
\c pg_9_db
一、pg 9新しいパーティションテーブルの作成
1.親テーブルの定義
CREATE TABLE pg_9_tab( id serial,uid int4,username varchar,create_time bigint);
CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);
2.サブテーブルの定義:inheritsでパーティションテーブルを作成する
3.サブテーブル制約の定義:データに対応するパーティションを制約するルール
--タイムスタンプによるパーティション化、対応するパーティション表とタイムスタンプ
2019-09-15 00:00:00 1568476800
2019-10-01 00:00:00 1569859200
2019-11-01 00:00:00 1572537600
2019-12-01 00:00:00 1575129600
2019-12-15 00:00:00 1576339200
2020-01-01 00:00:00 1577808000
2020-02-01 00:00:00 1580486400
上の手順2と手順3を2つにまとめてパーティションテーブルを作成
CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time < 1572537600 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time < 1575129600 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time < 1577808000 ) ) INHERITS(pg_9_tab);
4.サブテーブル索引の作成:サブテーブルは親テーブルの索引を継承しません
CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);
5.パーティション挿入、変更、関数およびトリガの削除
--サブテーブルinsertのルーティング関数の作成
CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.create_time < 1569859200 ) THEN
INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$function$;
関数のnew.*は、挿入するデータ航路を指し、親テーブルで挿入トリガを定義します.
CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW
EXECUTE PROCEDURE pg_9_tab_insert_trigger();
6.パーティションクエリーパラメータの有効化:constraint_の設定Exclusionパラメータ
show constraint_exclusion;
SET constraint_exclusion = off;##すべてのテーブルが制約なしでクエリーを最適化
SET constraint_exclusion = on;##すべてのテーブルが制約によってクエリーを最適化
SET constraint_exclusion = partition;##の継承テーブルとUNIOALLサブクエリの検索制約によるクエリの最適化
--親テーブルと子テーブルのどちらを表示する計画を実行しますか
EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
二、pg 9挿入データとデータ分部確認
--データの挿入
INSERT INTO pg_9_tab(uid,username,create_time)
SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);
INSERT INTO pg_9_tab(uid,username,create_time) SELECT 346,'F',1292083200;
--テーブルの表示
SELECT count(*) FROM pg_9_tab;
SELECT count(*) FROM ONLY pg_9_tab;
\d+ pg_9_tab*
SELECT * FROM pg_9_tab LIMIT 2;
SELECT * FROM pg_9_tab_p_201911 LIMIT 2;
三、pg 9パーティションテーブルに新しいパーティションを追加する
--パーティションの追加
1.パーティション表の作成
CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
2.制約の追加
ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check
CHECK (create_time >= 1577808000 and create_time < 1580486400);
3.フリップフロップのリフレッシュ
CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.create_time < 1569859200 ) THEN
INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400 ) THEN
INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$function$;
4.親テーブルへの新規パーティションの継承
ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab;
四、pg 9パーティションテーブル削除パーティション
--パーティションの削除
DROP TABLE pg_9_tab_p_hisotry;
ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab;
ALTER TABLE pg_9_tab_p_hisotry DETACH PARTITION pg_9_tab;
--パーティションクエリー
\d pg_9_tab
五、pg 9実際のテスト結果
上記の手順では、実際のテスト結果は次のとおりです.
テスト環境でpg 9テスト環境を作成し、pg 9インスタンスに接続します.
/usr/local/pgsql101/bin/psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
--テストライブラリの作成
\l
CREATE DATABASE pg_9_db;
\l
\c pg_9_db
一、pg 9新しいパーティションテーブルの作成
1.親テーブルの定義
CREATE TABLE pg_9_tab( id serial,uid int4,username varchar,create_time bigint);
CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);
2.サブテーブルの定義:inheritsでパーティションテーブルを作成する
3.サブテーブル制約の定義:データに対応するパーティションを制約するルール
--タイムスタンプによるパーティション化、対応するパーティション表とタイムスタンプ
2019-09-15 00:00:00 1568476800
2019-10-01 00:00:00 1569859200
2019-11-01 00:00:00 1572537600
2019-12-01 00:00:00 1575129600
2019-12-15 00:00:00 1576339200
2020-01-01 00:00:00 1577808000
2020-02-01 00:00:00 1580486400
上の手順2と手順3を2つにまとめてパーティションテーブルを作成
CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time < 1572537600 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time < 1575129600 ) ) INHERITS(pg_9_tab);
CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time < 1577808000 ) ) INHERITS(pg_9_tab);
4.サブテーブル索引の作成:サブテーブルは親テーブルの索引を継承しません
CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);
CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);
5.パーティション挿入、変更、関数およびトリガの削除
--サブテーブルinsertのルーティング関数の作成
CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.create_time < 1569859200 ) THEN
INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$function$;
関数のnew.*は、挿入するデータ航路を指し、親テーブルで挿入トリガを定義します.
CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW
EXECUTE PROCEDURE pg_9_tab_insert_trigger();
6.パーティションクエリーパラメータの有効化:constraint_の設定Exclusionパラメータ
show constraint_exclusion;
SET constraint_exclusion = off;##すべてのテーブルが制約なしでクエリーを最適化
SET constraint_exclusion = on;##すべてのテーブルが制約によってクエリーを最適化
SET constraint_exclusion = partition;##の継承テーブルとUNIOALLサブクエリの検索制約によるクエリの最適化
--親テーブルと子テーブルのどちらを表示する計画を実行しますか
EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
二、pg 9挿入データとデータ分部確認
--データの挿入
INSERT INTO pg_9_tab(uid,username,create_time)
SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);
INSERT INTO pg_9_tab(uid,username,create_time) SELECT 346,'F',1292083200;
--テーブルの表示
SELECT count(*) FROM pg_9_tab;
SELECT count(*) FROM ONLY pg_9_tab;
\d+ pg_9_tab*
SELECT * FROM pg_9_tab LIMIT 2;
SELECT * FROM pg_9_tab_p_201911 LIMIT 2;
三、pg 9パーティションテーブルに新しいパーティションを追加する
--パーティションの追加
1.パーティション表の作成
CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
2.制約の追加
ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check
CHECK (create_time >= 1577808000 and create_time < 1580486400);
3.フリップフロップのリフレッシュ
CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF ( NEW.create_time < 1569859200 ) THEN
INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400 ) THEN
INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$function$;
4.親テーブルへの新規パーティションの継承
ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab;
四、pg 9パーティションテーブル削除パーティション
--パーティションの削除
DROP TABLE pg_9_tab_p_hisotry;
ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab;
ALTER TABLE pg_9_tab_p_hisotry DETACH PARTITION pg_9_tab;
--パーティションクエリー
\d pg_9_tab
五、pg 9実際のテスト結果
上記の手順では、実際のテスト結果は次のとおりです.
postgresql 9
：
pg_9_db=# select extract(epoch from to_timestamp('2019-09-15 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1568476800
(1 row)
pg_9_db=# select extract(epoch from to_timestamp('2019-10-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1569859200
(1 row)
pg_9_db=# select extract(epoch from to_timestamp('2019-11-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1572537600
(1 row)
pg_9_db=# select extract(epoch from to_timestamp('2019-12-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1575129600
(1 row)
pg_9_db=# select extract(epoch from to_timestamp('2019-12-15 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1576339200
(1 row)
pg_9_db=# select extract(epoch from to_timestamp('2020-01-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1577808000
(1 row)
pg_9_db=# select extract(epoch from to_timestamp('2020-02-01 00:00:00','yyyy-MM-DD hh24:mi:ss'));
date_part
------------
1580486400
(1 row)
postgres=# CREATE DATABASE pg_9_db;
CREATE DATABASE
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+------------+------------+-----------------------
dba_test_db | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
pg_9_db | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=# \c pg_9_db
You are now connected to database "pg_9_db" as user "postgres".
pg_9_db=#
pg_9_db=# \dt
Did not find any relations.
pg_9_db=#
pg_9_db=#
pg_9_db=# CREATE TABLE pg_9_tab(
pg_9_db(# id serial,
pg_9_db(# uid int4,
pg_9_db(# username varchar,
pg_9_db(# create_time bigint
pg_9_db(# );
CREATE TABLE
pg_9_db=#
pg_9_db=# CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);
CREATE INDEX
pg_9_db=#
pg_9_db=# \d+ pg_9_tab
Table "public.pg_9_tab"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Indexes:
"idx_pg_9_tab_ctime" btree (create_time)
pg_9_db=#
pg_9_db=# CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >= 1569859200 and create_time < 1572537600 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >= 1572537600 and create_time < 1575129600 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=# CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >= 1575129600 and create_time < 1577808000 ) ) INHERITS(pg_9_tab);
CREATE TABLE
pg_9_db=#
pg_9_db=# \d+ pg_9_tab_p_hisotry
Table "public.pg_9_tab_p_hisotry"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Check constraints:
"pg_9_tab_p_hisotry_create_time_check" CHECK (create_time < 1569859200)
Inherits: pg_9_tab
pg_9_db=#
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);
CREATE INDEX
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);
CREATE INDEX
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);
CREATE INDEX
pg_9_db=# CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);
CREATE INDEX
pg_9_db=#
pg_9_db=# \d+ pg_9_tab_p_hisotry
Table "public.pg_9_tab_p_hisotry"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Indexes:
"idx_pg_9_tab_p_hisotry_ctime" btree (create_time)
Check constraints:
"pg_9_tab_p_hisotry_create_time_check" CHECK (create_time < 1569859200)
Inherits: pg_9_tab
pg_9_db=#
pg_9_db=#
pg_9_db=#
pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
pg_9_db-# RETURNS trigger
pg_9_db-# LANGUAGE plpgsql
pg_9_db-# AS $function$
pg_9_db$# BEGIN
pg_9_db$# IF ( NEW.create_time < 1569859200 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
pg_9_db$# ELSE
pg_9_db$# RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
pg_9_db$# END IF;
pg_9_db$# RETURN NULL;
pg_9_db$# END;
pg_9_db$# $function$;
CREATE FUNCTION
pg_9_db=#
pg_9_db=#
pg_9_db=#
pg_9_db=# select count(*) from pg_9_tab;
count
-------
0
(1 row)
pg_9_db=#
pg_9_db=# select count(*) from ONLY pg_9_tab;
count
-------
0
(1 row)
pg_9_db=# INSERT INTO pg_9_tab(uid,username,create_time)
pg_9_db-# SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);
INSERT 0 0
pg_9_db=#
pg_9_db=# select count(*) from pg_9_tab;
count
-------
21841
(1 row)
pg_9_db=# select count(*) from ONLY pg_9_tab;
count
-------
0
(1 row)
pg_9_db=#
pg_9_db=# select * from pg_9_tab limit 2;
id | uid | username | create_time
--------+-----+----------+-------------
131227 | 638 | Y | 1568476800
131228 | 523 | E | 1568477160
(2 rows)
pg_9_db=#
pg_9_db=# select * from pg_9_tab_p_201912 limit 2;
id | uid | username | create_time
--------+-----+----------+-------------
149707 | 892 | [ | 1575129600
149708 | 28 | V | 1575129960
(2 rows)
pg_9_db=#
pg_9_db=# \dt+ pg_9_tab*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------+-------+----------+------------+-------------
public | pg_9_tab | table | postgres | 8192 bytes |
public | pg_9_tab_p_201910 | table | postgres | 416 kB |
public | pg_9_tab_p_201911 | table | postgres | 400 kB |
public | pg_9_tab_p_201912 | table | postgres | 208 kB |
public | pg_9_tab_p_hisotry | table | postgres | 232 kB |
(5 rows)
--
pg_9_db=# show constraint_exclusion;
constraint_exclusion
----------------------
partition
(1 row)
pg_9_db=#
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..72.41 rows=3362 width=18) (actual time=0.017..1.424 rows=3359 loops=1)
-> Seq Scan on pg_9_tab (cost=0.00..0.00 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=1)
Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
-> Seq Scan on pg_9_tab_p_201912 (cost=0.00..72.41 rows=3361 width=18) (actual time=0.010..1.007 rows=3359 loops=1)
Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
Rows Removed by Filter: 2
Planning time: 0.982 ms
Execution time: 1.720 ms
(8 rows)
pg_9_db=#
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910 (cost=0.28..8.30 rows=1 width=18) (actual time=0.039..0.03
9 rows=0 loops=1)
Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
Planning time: 0.271 ms
Execution time: 0.066 ms
(4 rows)
pg_9_db=#
pg_9_db=# SET constraint_exclusion = off;
SET
pg_9_db=#
pg_9_db=# show constraint_exclusion;
constraint_exclusion
----------------------
off
(1 row)
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..97.32 rows=3365 width=18) (actual time=0.066..1.446 rows=3359 loops=1)
-> Seq Scan on pg_9_tab (cost=0.00..0.00 rows=1 width=48) (actual time=0.005..0.005 rows=0 loops=1)
Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
-> Index Scan using idx_pg_9_tab_p_hisotry_ctime on pg_9_tab_p_hisotry (cost=0.28..8.30 rows=1 width=18) (actual time=0.0
16..0.016 rows=0 loops=1)
Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
-> Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910 (cost=0.28..8.30 rows=1 width=18) (actual time=0.020
..0.020 rows=0 loops=1)
Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
-> Index Scan using idx_pg_9_tab_p_201911_ctime on pg_9_tab_p_201911 (cost=0.28..8.30 rows=1 width=18) (actual time=0.014
..0.014 rows=0 loops=1)
Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
-> Seq Scan on pg_9_tab_p_201912 (cost=0.00..72.41 rows=3361 width=18) (actual time=0.010..0.981 rows=3359 loops=1)
Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
Rows Removed by Filter: 2
Planning time: 0.811 ms
Execution time: 1.796 ms
(14 rows)
pg_9_db=#
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
------------------
Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910 (cost=0.28..8.30 rows=1 width=18) (actual time=0.008..0.00
8 rows=0 loops=1)
Index Cond: ((create_time > 1575129600) AND (create_time < 1576339200))
Planning time: 0.147 ms
Execution time: 0.031 ms
(4 rows)
pg_9_db=#
pg_9_db=# SET constraint_exclusion = on;
SET
pg_9_db=#
pg_9_db=# show constraint_exclusion;
constraint_exclusion
----------------------
on
(1 row)
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time > 1575129600 AND create_time < 1576339200;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..72.41 rows=3362 width=18) (actual time=0.029..1.401 rows=3359 loops=1)
-> Seq Scan on pg_9_tab (cost=0.00..0.00 rows=1 width=48) (actual time=0.012..0.012 rows=0 loops=1)
Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
-> Seq Scan on pg_9_tab_p_201912 (cost=0.00..72.41 rows=3361 width=18) (actual time=0.016..0.982 rows=3359 loops=1)
Filter: ((create_time > 1575129600) AND (create_time < 1576339200))
Rows Removed by Filter: 2
Planning time: 0.402 ms
Execution time: 1.782 ms
(8 rows)
pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time > 1575129600 AND create_time < 1576339200;
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=48) (actual time=0.001..0.001 rows=0 loops=1)
One-Time Filter: false
Planning time: 0.122 ms
Execution time: 0.016 ms
(4 rows)
pg_9_db=#
--
pg_9_db-#
pg_9_db-# \d+ pg_9_tab
Table "public.pg_9_tab"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Des
cription
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+----
---------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Indexes:
"idx_pg_9_tab_ctime" btree (create_time)
Triggers:
insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
pg_9_tab_p_201911,
pg_9_tab_p_201912,
pg_9_tab_p_hisotry
pg_9_db-#
pg_9_db-#
pg_9_db-# CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
ERROR: syntax error at or near "、"
LINE 1: 、
^
pg_9_db=#
pg_9_db=# CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );
CREATE TABLE
pg_9_db=#
pg_9_db=# ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check
pg_9_db-# CHECK (create_time >= 1577808000 and create_time < 1580486400);
ALTER TABLE
pg_9_db=#
pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()
pg_9_db-# RETURNS trigger
pg_9_db-# LANGUAGE plpgsql
pg_9_db-# AS $function$
pg_9_db$# BEGIN
pg_9_db$# IF ( NEW.create_time < 1569859200 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1569859200 and NEW.create_time < 1572537600 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1572537600 and NEW.create_time < 1575129600 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1575129600 and NEW.create_time < 1577808000 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);
pg_9_db$# ELSIF ( NEW.create_time >= 1577808000 and NEW.create_time < 1580486400 ) THEN
pg_9_db$# INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*);
pg_9_db$# ELSE
pg_9_db$# RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!';
pg_9_db$# END IF;
pg_9_db$# RETURN NULL;
pg_9_db$# END;
pg_9_db$# $function$;
CREATE FUNCTION
pg_9_db=#
pg_9_db=# ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab;
ALTER TABLE
pg_9_db=#
pg_9_db=# \d+ pg_9_tab
Table "public.pg_9_tab"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Indexes:
"idx_pg_9_tab_ctime" btree (create_time)
Triggers:
insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
pg_9_tab_p_201911,
pg_9_tab_p_201912,
pg_9_tab_p_202001,
pg_9_tab_p_hisotry
pg_9_db=#
--
pg_9_db=# \d+ pg_9_tab
Table "public.pg_9_tab"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Indexes:
"idx_pg_9_tab_ctime" btree (create_time)
Triggers:
insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
pg_9_tab_p_201911,
pg_9_tab_p_201912,
pg_9_tab_p_202001,
pg_9_tab_p_hisotry
pg_9_db=#
pg_9_db=# ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab;
ALTER TABLE
pg_9_db=#
pg_9_db=# \d+ pg_9_tab
Table "public.pg_9_tab"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | |
uid | integer | | | | plain | |
username | character varying | | | | extended | |
create_time | bigint | | | | plain | |
Indexes:
"idx_pg_9_tab_ctime" btree (create_time)
Triggers:
insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()
Child tables: pg_9_tab_p_201910,
pg_9_tab_p_201911,
pg_9_tab_p_201912,
pg_9_tab_p_202001
pg_9_db=# DROP TABLE pg_9_tab_p_hisotry;
DROP TABLE
pg_9_db=#