## Brin Index在 Greenplum 7中的理论与实现 --《Greenplum 7 新版本大剧透》系列直播 讲师：陈金豹，Greenplum内核研发 活动时间：2021年4月28日 20:00 - 21:00 #### 技术干货、行业热点、活动预告 欢迎访问Greenplum中文社区：cn.greenplum.org ## Brin Index 在Greenplum 7中的理论与实践 陈金豹, VMWare内核工程师 ## 目录 Brin Index On Heap AppendOnly Table Brin在AppendOnly Table上的实现 性能测试 ## Block Range Index 1c443857/p6_2.jpg) ## Brin的优势和劣势 优势： 占用空间小 创建快 劣势： 只有在数据具有一定分布特点时才有用 ## Brin的体积 Brin tuple: 20bytes Block Range: 8K * 20 = 160K Brin比Heap小8000倍 1 TB的Heap表只需要125M的Brin Block Rang ![Image](/

11. Indexes ..... 371 11.1. Introduction ..... 371 11.2. Index Types ..... 372 11.3. Multicolumn Indexes ..... 374 11.4. Indexes and ORDER BY ..... 375 11.5. Combining Multiple Indexes .... 376 11.6. Unique Indexes ..... 377 11.7. Indexes on Expressions ..... 378 11.8. Partial Indexes ..... 378 11.9. Operator Classes and Operator Families ..... 381 11.10. Indexes and Collations . 12.1.3. Configurations ..... 388 12.2. Tables and Indexes ..... 388 12.2.1. Searching a Table ..... 388 12.2.2. Creating Indexes ..... 389 12.3. Controlling Text Search ..... 390

11. Indexes ..... 444 11.1. Introduction ..... 444 11.2. Index Types ..... 445 11.3. Multicolumn Indexes ..... 447 11.4. Indexes and ORDER BY ..... 448 11.5. Combining Multiple Indexes .... . 449 11.6. Unique Indexes ..... 450 11.7. Indexes on Expressions ..... 450 11.8. Partial Indexes ..... 451 11.9. Index-Only Scans and Covering Indexes ..... 454 11.10. Operator Classes and Operator .... 457 11.11. Indexes and Collations ..... 458 11.12. Examining Index Usage ..... 459 12. Full Text Search ..... 461 12.1. Introduction ..... 461 12.2. Tables and Indexes ..... 465 12.3.

Extract Data 49 4.8.2 Using the Shapefile Dumper 50 4.9 Spatial Indexes 50 4.9.1 GiST Indexes 51 4.9.2 BRIN Indexes 51 4.9.3 SP-GiST Indexes 53 4.9.4 Tuning Index Usage 53 5 Spatial Queries 55 5.1 Determining 5.1.2 Named Spatial Relationships 57 5.1.3 General Spatial Relationships 58 5.2 Using Spatial Indexes 60 5.3 Examples of Spatial SQL 60 6 Performance Tips 64 6.1 Small tables of large geometries 64 ST_SetGeoReference . . . . . 12. 12.4 ST_Intersection . . . . . 12. 16.5 @ . . . . . 14.2.1 Drop_Indexes_Generate_Script 720 14.2.2 Drop_Nation_Tables_Generate_Script 721 14.2.3 Drop_State_Tables_Generate_Script

Extract Data 50 4.8.2 Using the Shapefile Dumper 51 4.9 Spatial Indexes 51 4.9.1 GiST Indexes 52 4.9.2 BRIN Indexes 52 4.9.3 SP-GiST Indexes 54 4.9.4 Tuning Index Usage 55 5 Spatial Queries 56 5.1 Determining 5.1.2 Named Spatial Relationships 58 5.1.3 General Spatial Relationships 59 5.2 Using Spatial Indexes 61 5.3 Examples of Spatial SQL 61 6 Performance Tips 64 6.1 Small tables of large geometries 64 1.4.2 parse_address 751 11.1.4.3 standardize_address 752 11.2 Tiger Geocoder 754 11.2.1 Drop_Indexes_Generate_Script 754 11.2.2 Drop_Nation_Tables_Generate_Script 755 11.2.3 Drop_State_Tables_Generate_Script

Extract Data 51 4.8.2 Using the Shapefile Dumper 52 4.9 Spatial Indexes 52 4.9.1 GiST Indexes 53 4.9.2 BRIN Indexes 53 4.9.3 SP-GiST Indexes 55 4.9.4 Tuning Index Usage 56 5 Spatial Queries 57 5.1 Determining 5.1.2 Named Spatial Relationships 59 5.1.3 General Spatial Relationships 60 5.2 Using Spatial Indexes 62 5.3 Examples of Spatial SQL 62 6 Performance Tips 65 6.1 Small tables of large geometries 65 Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects. ![OCR图片](/uploads/documents/9/c/9/9c

Extract Data 51 4.8.2 Using the Shapefile Dumper 52 4.9 Spatial Indexes 52 4.9.1 GiST Indexes 53 4.9.2 BRIN Indexes 53 4.9.3 SP-GiST Indexes 55 4.9.4 Tuning Index Usage 56 5 Spatial Queries 57 5.1 Determining 5.1.2 Named Spatial Relationships 59 5.1.3 General Spatial Relationships 59 5.2 Using Spatial Indexes 61 5.3 Examples of Spatial SQL 61 6 Performance Tips 64 6.1 Small tables of large geometries 64 Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects. ![OCR图片](/uploads/documents/9/7/1/97

4.1.6.2 Using the Shapefile Dumper 58 4.1.7 Building Spatial Indexes 58 4.1.7.1 GiST Indexes 59 4. 1.7.2 BRIN Indexes . . . . . 4.6 Topology 88 4.6.1 Topology Types 88 4.6.1.1 getfaceedges_returntype 4.7.4.2 standardize_address 136 4.8 PostGIS Extras 138 4.8.1 Tiger Geocoder 138 4.8.1.1 Drop_Indexes_Generate_Script 138 4.8.1.2 Drop_Nation_Tables_Generate_Script 139 4.8.1.3 Drop_State_Tables_Generate_Script 1.8 Install_Missing_Indexes 146 4.8.1.9 Loader_Generate_Census_Script 146 4.8.1.10 Loader_Generate_Script 148 4.8.1.11 Loader_Generate_Nation_Script 150 4.8.1.12 Missing_Indexes_Generate_Script 151

67 4.6 Building Indexes 67 4.6.1 GiST Indexes 67 4.6.2 BRIN Indexes 68 4.6.3 SP-GiST Indexes 69 4.6.4 Using Indexes 70 4.7 Complex Queries 70 4.7.1 Taking Advantage of Indexes 70 4.7.2 Examples 12.4.2 standardize_address 699 **13 PostGIS Extras** 702 13.1 Tiger Geocoder 702 13.1.1 Drop_Indexes_Generate_Script 702 13.1.2 Drop_Nation_Tables_Generate_Script 703 13.1.3 Drop_State_Tables_Generate_Script 13.1.8 Install_Missing_Indexes 710 13.1.9 Loader_Generate_Census_Script 710 13.1.10 Loader_Generate_Script 712 13.1.11 Loader_Generate_Nation_Script 714 13.1.12 Missing_Indexes_Generate_Script 715 13

data) and for new installs. Loader_Generate_Script . . . . . Install_Missing_Indexes ```sql SELECT install_missing_indexes(); ``` Geocode ## 2.4.4 Upgrading your Tiger Geocoder Install and Data First (SRID) SRID SRID. To make querying geometry efficient PostGIS defines various kinds of spatial indexes, and spatial operators to use them. See Section 4.9 and Section 5.2 for details. ## 4.2.1 OpenGIS text cat | character varying(20) geom | geometry(Point,4326) geom_2160 | geometry Indexes: "pois_ny_pkey" PRIMARY KEY, btree (gid) Check constraints: "enforce_dims_geom_2160"