PostgreSQL CREATE TABLE Partitions _postgresql

Source: Internet
Author: User
Tags create index postgresql
The steps to create a table partition are as follows:
1. Create the Main table
CREATE TABLE users (UID int not null primary key, name varchar (20));
2. Create the partition table (you must inherit the main table above)
CREATE TABLE users_0 (check (UID >= 0 and uid<)) INHERITS (users);
CREATE TABLE users_1 (check (uid >=)) INHERITS (users);
3. The index on the partition table, in fact, this step can be omitted Oh
CREATE INDEX Users_0_uidindex on Users_0 (UID);
CREATE INDEX Users_1_uidindex on Users_1 (UID);
4. Creating Rule Rules
CREATE Rule Users_insert_0 as
On INSERT to users WHERE
(UID >= 0 and UID < 100)
Do INSTEAD
INSERT into Users_0 VALUES (new.uid,new.name);
CREATE Rule users_insert_1 as
On INSERT to users WHERE
(UID >= 100)
Do INSTEAD
INSERT into Users_1 VALUES (new.uid,new.name);
Here you can test the write data:
postgres=# INSERT into Users VALUES (M, ' smallfish ');
INSERT 0 0
postgres=# INSERT into Users VALUES (' aaaaa ');
INSERT 0 0
postgres=# select * from users;
UID | Name
-----+-----------
20 | Aaaaa
100 | Smallfish
(2 data columns)
postgres=# select * from Users_0;
UID | Name
-----+-------
20 | Aaaaa
(1 data columns)
postgres=# select * from Users_1;
UID | Name
-----+-----------
100 | Smallfish
(1 data columns)
Here the table partition can be finished, but there is a place to change, first look at the count query.
postgres=# EXPLAIN SELECT Count (*) from users where uid<100;
QUERY Plan
---------------------------------------------------------------------------------------------
Aggregate (cost=62.75..62.76 Rows=1 width=0)
-> Append (cost=6.52..60.55 rows=879 width=0)
-> Bitmap Heap Scan on users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (UID < 100)
-> Bitmap Index Scan on Users_pkey (cost=0.00..6.45 rows=293 width=0)
Index Cond: (UID < 100)
-> Bitmap Heap Scan on users_0 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (UID < 100)
-> Bitmap Index Scan on Users_0_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (UID < 100)
-> Bitmap Heap Scan on users_1 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (UID < 100)
-> Bitmap Index Scan on Users_1_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (UID < 100)
(14 data columns)
According to the idea that the UID is less than 100, the theory should simply be to query the Users_0 table, and through explain you can see other tables where he scanned all the partitions.
postgres=# SET constraint_exclusion = on;
SET
postgres=# EXPLAIN SELECT Count (*) from users where uid<100;
QUERY Plan
---------------------------------------------------------------------------------------------
Aggregate (cost=41.83..41.84 Rows=1 width=0)
-> Append (cost=6.52..40.37 rows=586 width=0)
-> Bitmap Heap Scan on users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (UID < 100)
-> Bitmap Index Scan on Users_pkey (cost=0.00..6.45 rows=293 width=0)
Index Cond: (UID < 100)
-> Bitmap Heap Scan on users_0 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (UID < 100)
-> Bitmap Index Scan on Users_0_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (UID < 100)
(10 data columns)
The whole process here is OK!
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.