Shulou(Shulou.com)06/01 Report--

-- partition table

CREATE TABLE users (

User_id serial not null

User_name varchar (20)

Logdate timestamp (0) with time zone not null

) PARTITION BY RANGE (logdate)

-- table partied by month, create competition

CREATE TABLE users_y2017m01 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-01-01') TO ('2017-02-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m02 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-02-01') TO ('2017-03-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m03 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-03-01') TO ('2017-04-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m04 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-04-01') TO ('2017-05-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m05 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-05-01') TO ('2017-06-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m06 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-06-01') TO ('2017-07-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m07 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-07-01') TO ('2017-08-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m08 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-08-01') TO ('2017-09-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m09 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-09-01') TO ('2017-10-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m10 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-10-01') TO ('2017-11-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m11 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-11-01') TO ('2017-12-01') WITH (parallel_workers = 4)

CREATE TABLE users_y2017m12 PARTITION OF users (user_id primary key, user_name, logdate) FOR VALUES FROM ('2017-12-01') TO ('2018-01-01') WITH (parallel_workers = 4)

-- index on partied table

CREATE INDEX ON users_y2017m01 (logdate)

CREATE INDEX ON users_y2017m02 (logdate)

CREATE INDEX ON users_y2017m03 (logdate)

CREATE INDEX ON users_y2017m04 (logdate)

CREATE INDEX ON users_y2017m05 (logdate)

CREATE INDEX ON users_y2017m06 (logdate)

CREATE INDEX ON users_y2017m07 (logdate)

CREATE INDEX ON users_y2017m08 (logdate)

CREATE INDEX ON users_y2017m09 (logdate)

CREATE INDEX ON users_y2017m10 (logdate)

CREATE INDEX ON users_y2017m11 (logdate)

CREATE INDEX ON users_y2017m12 (logdate)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.