陈金豹，VMWare内核工程师 Confidential │ ©2021 VMware, Inc. 目录 Brin Index On Heap AppendOnly Table Brin在AppendOnly Table上的实现 性能测试 5 Confidential │ ©2021 VMware, Inc. Block Range Index 存储数据块中元组字段的最 │ ©2021 VMware, Inc. 目录 Brin Index AppendOnly Table Brin在AppendOnly Table上的实现 性能测试 15 Confidential │ ©2021 VMware, Inc. AoTable AppendOnly Table是一种紧凑的 数据格式，适用于较少进行 Update/Delete的场景 Tuple以紧凑的方式存储在变长 Confidential │ ©2021 VMware, Inc. Append only table Confidential │ ©2021 VMware, Inc. 目录 Brin Index AppendOnly Table Brin在AppendOnly Table上的实现 性能测试 19 Confidential │ ©2021 VMware, Inc

//hostIP:port/dbname",”UserName”,”Password”); stmt = c.createStatement(); String sql = "CREATE TABLE COMPANY " + "(ID INT PRIMARY KEY NOT NULL," + "NAME TEXT NOT NULL," + "AGE INT NOT NULL," + "ADDRESS host="hostIP", port="port") print "Opened database successfully" cur = conn.cursor() cur.execute('''CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, 快速上⼿ Greenplum数据仓库 Greenplum数据仓库 UDW Copyright © 2012-2021 UCloud 优刻得 26/206 ADDRESS CHAR(50), SALARY REAL);''') print "Table created successfully" conn.commit() conn.close() ⽰例3. 插⼊记录 insert.py #!/usr/bin/python import psycopg2

CREATE TABLE products (name varchar(40), prod_id integer, supplier_id integer) DISTRIBUTED BY (prod_id); GP的表增强 greenplum除支持普通的表外，还增加了PostgreSQL 中没有的表类型： append-only table: CREATE TABLE bar (a (a int, b text) WITH (appendonly=true) DISTRIBUTED BY (a); column-oriented table: CREATE TABLE bar (a int, b text) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (a); append-only的表支持压缩： append-only的表支持压缩： CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); GP使用人性化的partion语法支持分区表 CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED

About Database Statistics in Greenplum Database 180 System Statistics 181 Table Size 181 The pg_statistic System Table and pg_stats View 181 Sampling 185 Updating Statistics 185 Analyzing Partitioned 322 About Table Definition Basics 323 Default Column Values 324 Generated Columns 325 Creating and Managing Tables 326 Creating a Table 326 Choosing Column Data Types 326 Setting Table and Column 329 Choosing the Table Distribution Policy 329 Declaring Distribution Keys 330 Custom Distribution Key Hash Functions 331 Example Custom Hash Operator Class 331 Choosing the Table Storage Model 333

Specifying Table Distribution Columns 218 VMware Greenplum v6.19 Documentation VMware, Inc. 8 Resource Groups Features 219 PL/pgSQL Procedural Language Enhancements 219 Replicated Table Data 220 Concurrency Parameters 464 Example: Using the Data Type of a Table Column 464 VMware Greenplum v6.19 Documentation VMware, Inc. 23 Example: Composite Type Based on a Table Row 465 Example: Using a Variable Number of for failed segments using gpstate 532 Check for failed segments using the gp_segment_configuration table 533 Check for failed segments by examining log files 533 To check the log files 533 Understanding

Algorithm 210 Relaxed Rules for Specifying Table Distribution Columns 210 Resource Groups Features 211 PL/pgSQL Procedural Language Enhancements 211 Replicated Table Data 212 Concurrency Improvements in Aliases for Function Parameters 455 Example: Using the Data Type of a Table Column 455 Example: Composite Type Based on a Table Row 456 Example: Using a Variable Number of Arguments 457 Example: Using for failed segments using gpstate 523 Check for failed segments using the gp_segment_configuration table 524 Check for failed segments by examining log files 524 To check the log files 524 Understanding

Aliases for Function Parameters 390 Example: Using the Data Type of a Table Column 390 Example: Composite Type Based on a Table Row 391 Example: Using a Variable Number of Arguments 392 Example: Using Per Host 523 About the Expansion Schema 523 Planning Table Redistribution 524 Managing Redistribution in Large-Scale Greenplum Systems 524 Table Redistribution Methods 524 Systems with Abundant Free gpexpand 534 To redistribute tables with gpexpand 534 Monitoring Table Redistribution 534 Viewing Expansion Status 534 Viewing Table Status 535 Post Expansion Tasks 535 Post Expansion Tasks 0 Removing

Algorithm 215 Relaxed Rules for Specifying Table Distribution Columns 215 Resource Groups Features 216 PL/pgSQL Procedural Language Enhancements 216 Replicated Table Data 217 Concurrency Improvements in Aliases for Function Parameters 458 Example: Using the Data Type of a Table Column 459 Example: Composite Type Based on a Table Row 459 Example: Using a Variable Number of Arguments 460 Example: Using for failed segments using gpstate 525 Check for failed segments using the gp_segment_configuration table 526 Check for failed segments by examining log files 526 To check the log files 526 Understanding

Algorithm 271 Relaxed Rules for Specifying Table Distribution Columns 271 Resource Groups Features 271 PL/pgSQL Procedural Language Enhancements 272 Replicated Table Data 272 VMware Greenplum 6 Documentation About Database Statistics in Greenplum Database 329 System Statistics 330 Table Size 330 The pg_statistic System Table and pg_stats View 330 Sampling 334 Updating Statistics 334 Analyzing Partitioned Setting Up PXF 466 Creating a PXF External Table 468 Mapping the LOCATION Clause 468 Mapping the FORMAT Options 469 Example gphdfs to pxf External Table Mapping for an HDFS Text File 470 Verifying

Algorithm 238 Relaxed Rules for Specifying Table Distribution Columns 239 Resource Groups Features 239 PL/pgSQL Procedural Language Enhancements 239 Replicated Table Data 240 Concurrency Improvements in About Database Statistics in Greenplum Database 297 System Statistics 298 Table Size 298 The pg_statistic System Table and pg_stats View 298 Sampling 302 Updating Statistics 302 Analyzing Partitioned Setting Up PXF 472 Creating a PXF External Table 473 Mapping the LOCATION Clause 474 Mapping the FORMAT Options 475 Example gphdfs to pxf External Table Mapping for an HDFS Text File 475 Verifying