 复制postgres=# createtable r (r_id int,分区 r_name name, r_date date) partition by range (r_id); CREATETABLE postgres=# createtable r1 partition of r forvaluesfrom (1) to (10); CREATETABLE postgres=# createtable r2 partition of r forvaluesfrom (10) to (20); CREATETABLE postgres=# createtable r3 partition of r forvaluesfrom (20) to (30); CREATETABLE postgres=# insertinto r select id, md5(random()::text), now() + (id||day)::interval from generate_series(1, 29) t(id); INSERT 0 29 postgres=# select *, tableoid::regclass from r; r_id | r_name | r_date | tableoid ------+----------------------------------+------------+---------- 1 | 1d0d0680930198d2962b3b5f9cf82083 | 2017-08-09 | r1 2 | 47ba81de41d71bd51b18c7861a594bdf | 2017-08-10 | r1 3 | 820b0b1affe3bf0e5705aee3e77b0b29 | 2017-08-11 | r1 4 | 0cc06451bd0652d2583a733374d787b3 | 2017-08-12 | r1 5 | 642108381b2fc203b830f1215a0d7c6a | 2017-08-13 | r1 6 | 57e3869b2ab8ee1c0bca96b1cf022a5d | 2017-08-14 | r1 7 | 5357fa6de3c1c559edb78cddb4eae902 | 2017-08-15 | r1 8 | 6ea5a7dba4dfc6c81ca5932be86a9341 | 2017-08-16 | r1 9 | d3d4dcb9dc48e0629042ede7ed9c7a33 | 2017-08-17 | r1 10 | 248d6f3e072c6c137a3402d11fc5b1d7 | 2017-08-18 | r2 11 | ae3a671045ded43260bc4d0bbcb7e428 | 2017-08-19 | r2 12 | acdc89bb326d9f0caaeeb86bfeac3a76 | 2017-08-20 | r2 13 | 147b6e975d7299db66e170874b913b25 | 2017-08-21 | r2 14 | 6041a6b84b1af615bdb34a5926d72a33 | 2017-08-22 | r2 15 | 3d96e08395af120dd36e10a0252ce29c | 2017-08-23 | r2 16 | 5e613d10c9cac126453413ddfc17c210 | 2017-08-24 | r2 17 | e92fc34d180be652e72a63b92d327f1b | 2017-08-25 | r2 18 | 3109c4e8f4da701721151df11a4d266f | 2017-08-26 | r2 19 | 35ba5892f3b88aa3254445fbf5267eea | 2017-08-27 | r2 20 | c92d1df47257784bb11d7bfbb52b5710 | 2017-08-28 | r3 21 | d076a5498d17ade8f317bf47cfa322c3 | 2017-08-29 | r3 22 | a66c2e83f1e54e1392964ed71d5b8e20 | 2017-08-30 | r3 23 | 6a94df0f08921728aa0af9455d05c9f8 | 2017-08-31 | r3 24 | 248c46d80b926c66c093c500f309614d | 2017-09-01 | r3 25 | 4da3be147fd1831e8605fc400e7a7503 | 2017-09-02 | r3 26 | 3029d7e22b7c963e8983200a93894669 | 2017-09-03 | r3 27 | 720d6d04249e9f3595a19cf59f075332 | 2017-09-04 | r3 28 | 95b5e5492591c38ddd864d83265e26c4 | 2017-09-05 | r3 29 | 2628c14bd3f67699ab0411b6fd402460 | 2017-09-06 | r3 (29 rows) postgres=# explain select * from r where id = 20; ERROR: column"id" does not exist LINE 1: explain select * from r where id = 20; ^ postgres=# explain select * from r where r_id = 20; QUERY PLAN ---------------------------------------------------------- Append (cost=0.00..20.12 rows=4 width=72) -> Seq Scan on r3 (cost=0.00..20.12 rows=4 width=72) Filter: (r_id = 20) (3 rows) postgres=# set constraint_exclusion = off; SET postgres=# explain select * from r where r_id = 20; QUERY PLAN ---------------------------------------------------------- Append (cost=0.00..60.38 rows=12 width=72) -> Seq Scan on r1 (cost=0.00..20.12 rows=4 width=72) Filter: (r_id = 20) -> Seq Scan on r2 (cost=0.00..20.12 rows=4 width=72) Filter: (r_id = 20) -> Seq Scan on r3 (cost=0.00..20.12 rows=4 width=72) Filter: (r_id = 20) (7 rows) postgres=# postgres=# createindexon r1 (r_id); CREATEINDEX postgres=# explain select * from r where r_id = 5; QUERY PLAN ---------------------------------------------------------------------------------- Append (cost=5.53..25.54 rows=161 width=72) -> Bitmap Heap Scan on r1 (cost=5.53..25.54 rows=161 width=72) Recheck Cond: (r_id = 5) -> Bitmap Index Scan on r1_r_id_idx (cost=0.00..5.48 rows=161 width=0) Index Cond: (r_id = 5) (5 rows) postgres=# \d+ r* Table"public.r" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- r_id | integer | | | | plain | | r_name | name| | | | plain | | r_date | date| | | | plain | | Partition key: RANGE (r_id) Partitions: r1 FORVALUESFROM (1) TO (10), r2 FORVALUESFROM (10) TO (20), r3 FORVALUESFROM (20) TO (30) Table"public.r1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- r_id | integer | | | | plain | | r_name | name| | | | plain | | r_date | date| | | | plain | | Partition of: r FORVALUESFROM (1) TO (10) Partition constraint: ((r_id ISNOTNULL) AND (r_id >= 1) AND (r_id < 10)) Indexes: "r1_r_id_idx" btree (r_id) Index"public.r1_r_id_idx" Column | Type | Definition | Storage --------+---------+------------+--------- r_id | integer | r_id | plain btree, fortable"public.r1" Table"public.r2" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- r_id | integer | | | | plain | | r_name | name| | | | plain | | r_date | date| | | | plain | | Partition of: r FORVALUESFROM (10) TO (20) Partition constraint: ((r_id ISNOTNULL) AND (r_id >= 10) AND (r_id < 20)) Table"public.r3" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- r_id | integer | | | | plain | | r_name | name| | | | plain | | r_date | date| | | | plain | | Partition of: r FORVALUESFROM (20) TO (30) Partition constraint: ((r_id ISNOTNULL) AND (r_id >= 20) AND (r_id < 30)) 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130. |