greenplum分区

创建分区表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE SCHEMA employee;
CREATE TABLE "employee"."employee" (
"name" VARCHAR,
"ei" int4 NOT NULL,
"employeeid" int4 NOT NULL,
"department" TEXT,
"updatetime" TIMESTAMP (0),
"isstop" int2,
CONSTRAINT "employee_pk" PRIMARY KEY ("ei", "employeeid")
) WITH (OIDS = FALSE) DISTRIBUTED BY (ei) PARTITION BY RANGE (ei)(
START (0)
END (500000) EVERY (10000),
DEFAULT PARTITION extra
);

分区前后性能对比

分区前后复杂sql的执行时间对比,单位是秒

分区前 分区后
3.666 2.836
7.105 2.873
4.763 2.624
6.018 3.070
5.024 2.310
3.101 1.967
5.357 3.095

查看表空间大小

  1. 查看指定表大小

    1
    select pg_size_pretty(pg_relation_size('test'));
  2. 查看指定schema下所有表大小

    1
    select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

附录

copy

1
copy (select ei, contactid, isdeleted, name, tel, mobile from contact) to '/data/contact.txt';

检查schema是否存在

1
select exists (select * from pg_catalog.pg_namespace where nspname = '$schema') as schema_exists

检查table是否存在

1
select exists (select 1 from information_schema.tables where table_schema = '$schema' and table_name = '$table') as table_exists

参考

  1. Pivotal Greenplum Docs: CREATE TABLE
  2. Pivotal Greenplum Docs: DELETE
  3. Pivotal Greenplum Docs: Partitioning Large Tables
  4. PostgreSQL 查看数据库,索引,表,表空间大小
  5. How to check if PostgreSQL public schema exists? - Stack Overflow