空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , Greenplum , PostGIS , GiST , R-Tree , BRIN , 相关性 , 网格 , BOX , K-Mean


背景

空间数据的搜索需求通常包括:

1、平面、三维、多维对象 几何相交、不相交、相邻。

2、平面、三维、多维对象的方位判断(相交或严格在左边、右边、上边、下边),类似数值的大于、小于、大于等于、小于等于。

3、平面、三维、多维对象 包含 另一个对象

4、平面、三维、多维对象 等于 另一个对象

5、平面、三维、多维对象 与另一个对象的(边、最近、中心点的)距离,按距离排序输出满足条件的行,输出距离在XX以内的行。

操作符如下

http://postgis.net/docs/manual-2.3/reference.html#Operators

&& — Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.  
&&(geometry,box2df) — Returns TRUE if a geometry's (cached) 2D bounding box intersects a 2D float precision bounding box (BOX2DF).  
&&(box2df,geometry) — Returns TRUE if a 2D float precision bounding box (BOX2DF) intersects a geometry's (cached) 2D bounding box.  
&&(box2df,box2df) — Returns TRUE if two 2D float precision bounding boxes (BOX2DF) intersect each other.  
&&& — Returns TRUE if A's n-D bounding box intersects B's n-D bounding box.  
&&&(geometry,gidx) — Returns TRUE if a geometry's (cached) n-D bounding box intersects a n-D float precision bounding box (GIDX).  
&&&(gidx,geometry) — Returns TRUE if a n-D float precision bounding box (GIDX) intersects a geometry's (cached) n-D bounding box.  
&&&(gidx,gidx) — Returns TRUE if two n-D float precision bounding boxes (GIDX) intersect each other.  
&< — Returns TRUE if A's bounding box overlaps or is to the left of B's.  
&<| — Returns TRUE if A's bounding box overlaps or is below B's.  
&> — Returns TRUE if A' bounding box overlaps or is to the right of B's.  
<< — Returns TRUE if A's bounding box is strictly to the left of B's.  
<<| — Returns TRUE if A's bounding box is strictly below B's.  
= — Returns TRUE if A's bounding box is the same as B's. Uses double precision bounding box.  
>> — Returns TRUE if A's bounding box is strictly to the right of B's.  
@ — Returns TRUE if A's bounding box is contained by B's.  
@(geometry,box2df) — Returns TRUE if a geometry's 2D bounding box is contained into a 2D float precision bounding box (BOX2DF).  
@(box2df,geometry) — Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into a geometry's 2D bounding box.  
@(box2df,box2df) — Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into another 2D float precision bounding box.  
|&> — Returns TRUE if A's bounding box overlaps or is above B's.  
|>> — Returns TRUE if A's bounding box is strictly above B's.  
~ — Returns TRUE if A's bounding box contains B's.  
~(geometry,box2df) — Returns TRUE if a geometry's 2D bonding box contains a 2D float precision bounding box (GIDX).  
~(box2df,geometry) — Returns TRUE if a 2D float precision bounding box (BOX2DF) contains a geometry's 2D bonding box.  
~(box2df,box2df) — Returns TRUE if a 2D float precision bounding box (BOX2DF) contains another 2D float precision bounding box (BOX2DF).  
~= — Returns TRUE if A's bounding box is the same as B's.  
<-> — Returns the 2D distance between A and B.  
|=| — Returns the distance between A and B trajectories at their closest point of approach.  
<#> — Returns the 2D distance between A and B bounding boxes.  
<<->> — Returns the n-D distance between the centroids of A and B bounding boxes.  
<<#>> — Returns the n-D distance between A and B bounding boxes.  
AI 代码解读

这些操作符是否支持索引,支持哪些索引接口,索引是如何做到这些操作符的高效率减少的,各种索引的效率如何,数据编排上有什么优化手段?

PostgreSQL 支持的索引种类

关于PostgreSQL支持的9种索引接口(实际上还可以继续扩展更多接口),我以前写过文档,详细的描述。这里不再赘述。

《PostgreSQL 9种索引的原理和应用场景》

B-Tree

B-Tree毫无疑问是最简单,最容易理解的索引,按顺序将KEY分成几个层次(根、分支、叶子)组织起来,在VALUE中存储数据的实际行号。

这种索引结构在排序、范围查询(大于、小于、大于等于、小于等于)、精确查询(等于)的场景中非常有效。

但是它没有办法支持空间搜索中提到的几何对象的搜索需求(相交,包含,距离等)。

R-Tree

将空间数据按长方形组织,大的长方形套小的长方形。顶层是最大的长方形,接下来是很多小的长方形,再接下来更小,不停的切成多个层次。类似B-Tree的根、分支、叶子结构。

很早以前PostgreSQL中支持R-Tree,但是后来被更好的GiST接口取代了。

GiST

GiST是一个通用的索引接口,全称Generalized Search Trees。不仅仅适合空间数据类型的检索,同样适合其他数据类型。

在GIS数据的GiST索引实现中,将空间数据按“在另一边(上、下、左、右)”,“相交”,“包含”使用R-Tree结构组织。

同时,GIS索引能够支持在一个索引结构中,存储平面、三维、多维的数据。这是很了不起的功能。

BRIN

BRIN是块级索引(9.5以上版本),存储每个(或每连续几个)块的边界值,例如:

数值类型:存储每个BLOCK(或每连续若干BLOCK)包含的记录中,最大值和最小值。(理论上还可以扩展BRIN索引的功能,例如包含记录数、平均值、SUM等信息,但是索引会更重)

几何类型:存储每个BLOCK(或每连续若干BLOCK)包含的记录中,能够包含下这些几何对象的最小的BOX。例如这些记录中(最左下的点、最右上的点 造成的BOX)。

《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》

GiST和BRIN 如何选择

GiST和BRIN索引分别支持什么空间搜索需求?

1、GiST索引支持什么查询需求?

除了前面提到的哪些几何操作符(相交、包含、方位、距离、距离排序等),还有大量的函数支持索引的使用。

详见

http://postgis.net/docs/manual-2.3/reference.html

2、BRIN索引支持什么查询需求?

BRIN索引中存储的信息量比较有限,实际上就是包含每个(每连续几个)BLOCK中所有几何数据的最小BOX(或多维BOX)。

现在能使用BRIN来优化 包含、相交、不相交 的几何搜索。

&&, ~ and @ operators can be used for the 2D cases (both for "geometry" and for "geography"),   
  
&&& operator can be used for the 3D geometries.  
AI 代码解读

GiST和BRIN索引的overhead

索引都会引入Overhead,那么哪个引入的OVERHEAD比较大呢?

GiST索引引入的overhead比较大,它需要存储的信息量较大,支持更多的搜索需求。

举例

创建PostGIS扩展。

create extension postgis;  
AI 代码解读

创建测试表。

postgres=# create table gis_idx_test(id int, info text, pos geometry) with (autovacuum_enabled=off,toast.autovacuum_enabled=off);  
CREATE TABLE  
AI 代码解读

1、写入速度对比,(写入1000万随机POINT):

GiST,219秒。

postgres=# \timing  
Timing is on.  
  
create index idx_gis_idx_test on gis_idx_test using gist (pos);  
insert into gis_idx_test select id, md5(random()::text), ST_SetSRID(ST_Point(180-random()*360, 90-random()*180),4326) from generate_series(1,10000000) t(id);  
  
INSERT 0 10000000  
Time: 219213.503 ms  
AI 代码解读

BRIN(每个BLOCK统计一个BOX),33秒。

drop index idx_gis_idx_test;  
truncate gis_idx_test;  
create index idx_gis_idx_test on gis_idx_test using brin (pos) with (pages_per_range =1);  
insert into gis_idx_test select id, md5(random()::text), ST_SetSRID(ST_Point(180-random()*360, 90-random()*180),4326) from generate_series(1,10000000) t(id);  
  
INSERT 0 10000000  
Time: 32621.684 ms  
AI 代码解读

2、无索引插入速度,30秒。BRIN索引对插入几乎没有影响。

drop index idx_gis_idx_test;  
truncate gis_idx_test;  
insert into gis_idx_test select id, md5(random()::text), ST_SetSRID(ST_Point(180-random()*360, 90-random()*180),4326) from generate_series(1,10000000) t(id);  
  
INSERT 0 10000000  
Time: 29696.020 ms  
AI 代码解读

3、创建速度对比(1000万随机POINT):

GiST,164秒。

drop index idx_gis_idx_test;  
create index idx_gis_idx_test on gis_idx_test using gist (pos);  
  
CREATE INDEX  
Time: 163988.002 ms  
AI 代码解读

BRIN,3.5秒。

drop index idx_gis_idx_test;  
create index idx_gis_idx_test on gis_idx_test using brin (pos) with (pages_per_range =1);  
  
CREATE INDEX  
Time: 3491.662 ms  
AI 代码解读

4、空间对比(1000万随机POINT):

GiST,522MB。

postgres=# \di+ idx_gis_idx_test   
                                 List of relations  
 Schema |       Name       | Type  |  Owner   |    Table     |  Size  | Description   
--------+------------------+-------+----------+--------------+--------+-------------  
 public | idx_gis_idx_test | index | postgres | gis_idx_test | 522 MB |   
(1 row)  
AI 代码解读

BRIN,5MB。

                                     List of relations  
 Schema |         Name         | Type  |  Owner   |      Table      |  Size   | Description   
--------+----------------------+-------+----------+-----------------+---------+-------------  
 public | idx_gis_idx_test     | index | postgres | gis_idx_test    | 5136 kB |   
AI 代码解读

5、检索速度对比:

5.1 包含查询,查询在某个多边形中的所有POINT。

GiST,136毫秒。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from gis_idx_test where st_within (pos, ST_SetSRID(ST_MakePolygon(st_setsrid(ST_GeomFromText('LINESTRING(0 0, 15 0, 7.5 10, 0 0)'),4326)),4326));  
                                                                                                        QUERY PLAN                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.gis_idx_test  (cost=344.25..33368.43 rows=3333 width=68) (actual time=9.180..135.562 rows=11392 loops=1)  
   Output: id, info, pos  
   Recheck Cond: ('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry ~ gis_idx_test.pos)  
   Filter: _st_contains('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry, gis_idx_test.pos)  
   Rows Removed by Filter: 11495  
   Heap Blocks: exact=20944  
   Buffers: shared hit=6 read=21117 written=2  
   ->  Bitmap Index Scan on idx_gis_idx_test  (cost=0.00..343.42 rows=10000 width=0) (actual time=5.550..5.550 rows=22887 loops=1)  
         Index Cond: ('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry ~ gis_idx_test.pos)  
         Buffers: shared hit=6 read=173  
 Planning time: 0.187 ms  
 Execution time: 136.564 ms  
(12 rows)  
AI 代码解读

BRIN,3.2秒。

注意到,recheck阶段移除了9977113行,也就是说数据的空间线性不好。后面讲优化。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from gis_idx_test where st_within (pos, ST_SetSRID(ST_MakePolygon(st_setsrid(ST_GeomFromText('LINESTRING(0 0, 15 0, 7.5 10, 0 0)'),4326)),4326));  
                                                                                                        QUERY PLAN                    
---------------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.gis_idx_test  (cost=2631.83..35656.01 rows=3333 width=68) (actual time=67.964..3277.244 rows=11392 loops=1)  
   Output: id, info, pos  
   Recheck Cond: ('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry ~ gis_idx_test.pos)  
   Rows Removed by Index Recheck: 9977113  
   Filter: _st_contains('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry, gis_idx_test.pos)  
   Rows Removed by Filter: 11495  
   Heap Blocks: lossy=123457  
   Buffers: shared hit=819 read=123465 written=238  
   ->  Bitmap Index Scan on idx_gis_idx_test  (cost=0.00..2631.00 rows=10000 width=0) (actual time=67.473..67.473 rows=1234570 loops=1)  
         Index Cond: ('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry ~ gis_idx_test.pos)  
         Buffers: shared hit=819 read=8  
 Planning time: 0.250 ms  
 Execution time: 3278.503 ms  
(13 rows)  
AI 代码解读

注意为了让BRIN索引得到好的查询效率,需要在数据编排上面优化。比如标量数据需要时序化(即提高物理存储和逻辑值的线性相关性)。而空间数据则需要网格化,因为BRIN的空间数据存储的是范围的BOX。

下面再来看如何优化。

5.2 KNN检索,查询某个点方圆100(单位与SRID有关)以内的点,并按由近到远的顺序输出。

《PostGIS 空间数据学习建议》

GiST 支持距离索引,距离排序。BRIN暂时不支持(但是它支持包含,因此我们可以构造一个圆形来实现同等效果,过滤后的数据再排序输出)。

GiST

postgres=# set enable_bitmapscan=off;  
SET  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from gis_idx_test where ST_DWithin (pos, ST_SetSRID(ST_Point(70,10),4326), 1) order by pos <-> ST_SetSRID(ST_Point(70,10),4326);  
                                                                   QUERY PLAN       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_gis_idx_test on public.gis_idx_test  (cost=0.42..1097597.75 rows=133333 width=76) (actual time=0.139..2.362 rows=476 loops=1)  
   Output: id, info, pos, (pos <-> '0101000020E610000000000000008051400000000000002440'::geometry)  
   Index Cond: (gis_idx_test.pos && '0103000020E6100000010000000500000000000000004051400000000000002240000000000040514000000000000026400000000000C0514000000000000026400000000000C05140000000000000224000000000004051400000000000002240'::geometry)  
   Order By: (gis_idx_test.pos <-> '0101000020E610000000000000008051400000000000002440'::geometry)  
   Filter: (('0101000020E610000000000000008051400000000000002440'::geometry && st_expand(gis_idx_test.pos, '1'::double precision)) AND _st_dwithin(gis_idx_test.pos, '0101000020E610000000000000008051400000000000002440'::geometry, '1'::double precision))  
   Rows Removed by Filter: 130  
   Buffers: shared hit=617  
 Planning time: 0.181 ms  
 Execution time: 2.445 ms  
(9 rows)  
AI 代码解读

BRIN,(根据后面的章节,对数据重排后的性能)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from gis_idx_test_brin where ST_DWithin (pos, ST_SetSRID(ST_Point(70,10),4326), 1) order by pos <-> ST_SetSRID(ST_Point(70,10),4326);  
                                                                            QUERY PLAN     
-------------------------------------------------------------------------------------------------------------------------------------------------  
 Sort  (cost=4818.33..4818.42 rows=37 width=77) (actual time=57.087..57.144 rows=476 loops=1)  
   Output: id, info, pos, ((pos <-> '0101000020E610000000000000008051400000000000002440'::geometry))  
   Sort Key: ((gis_idx_test_brin.pos <-> '0101000020E610000000000000008051400000000000002440'::geometry))  
   Sort Method: quicksort  Memory: 91kB  
   Buffers: shared hit=836  
   ->  Bitmap Heap Scan on public.gis_idx_test_brin  (cost=2560.18..4817.37 rows=37 width=77) (actual time=55.785..56.898 rows=476 loops=1)  
         Output: id, info, pos, (pos <-> '0101000020E610000000000000008051400000000000002440'::geometry)  
         Recheck Cond: (gis_idx_test_brin.pos && '0103000020E6100000010000000500000000000000004051400000000000002240000000000040514000000000000026400000000000C0514000000000000026400000000000C05140000000000000224000000000004051400000000000002240'::geometry)  
         Rows Removed by Index Recheck: 852  
         Filter: (('0101000020E610000000000000008051400000000000002440'::geometry && st_expand(gis_idx_test_brin.pos, '1'::double precision)) AND _st_dwithin(gis_idx_test_brin.pos, '0101000020E610000000000000008051400000000000002440'::geometry, '1'::double precision))  
         Rows Removed by Filter: 130  
         Heap Blocks: lossy=18  
         Buffers: shared hit=836  
         ->  Bitmap Index Scan on idx_gis_idx_test_brin  (cost=0.00..2560.17 rows=556 width=0) (actual time=55.700..55.700 rows=180 loops=1)  
               Index Cond: (gis_idx_test_brin.pos && '0103000020E6100000010000000500000000000000004051400000000000002240000000000040514000000000000026400000000000C0514000000000000026400000000000C05140000000000000224000000000004051400000000000002240'::geometry)  
               Buffers: shared hit=818  
 Planning time: 0.313 ms  
 Execution time: 57.232 ms  
(18 rows)  
AI 代码解读

BRIN 数据优化数据规划

为了让BRIN索引得到好的查询效率,需要在数据编排上面优化。比如标量数据需要时序化(即提高物理存储和逻辑值的线性相关性)。而空间数据则需要网格化,因为BRIN的空间数据存储的是范围的BOX。

PostGIS提供了几种数据聚集的窗口函数,可以将数据按网格聚集起来。按照这种方法重排数据,可以让BRIN索引得到很好的查询效率。

我之前写过一篇文档,讲空间数据的聚集。《蜂巢的艺术与技术价值 - PostgreSQL PostGIS's hex-grid》,如果数据按GRID组织,是不是BRIN索引就很好了呢。

数据重排,方法很多:

1、可以用到的数据空间聚集函数如下:

pic

pic

http://postgis.net/docs/manual-dev/ST_ClusterKMeans.html

http://postgis.net/docs/manual-dev/ST_ClusterDBSCAN.html

2、可以用geohash的VALUE进行重排。

geohash重排举例。

postgres=# create table gis_idx_test_brin(like gis_idx_test);  
CREATE TABLE  
postgres=# insert into gis_idx_test_brin select * from gis_idx_test order by st_geohash(pos);  
INSERT 0 10000000  
postgres=# create index idx_gis_idx_test_brin on gis_idx_test_brin using brin (pos) with (pages_per_range =1);  
CREATE INDEX  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from gis_idx_test_brin where st_within (pos, ST_SetSRID(ST_MakePolygon(st_setsrid(ST_GeomFromText('LINESTRING(0 0, 15 0, 7.5 10, 0 0)'),4326)),4326));  
                                    QUERY PLAN           
----------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.gis_idx_test_brin  (cost=2631.83..35656.01 rows=3333 width=68) (actual time=56.683..76.280 rows=11392 loops=1)  
   Output: id, info, pos  
   Recheck Cond: ('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry ~ gis_idx_test_brin.pos)  
   Rows Removed by Index Recheck: 2952  
   Filter: _st_contains('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry, gis_idx_test_brin.pos)  
   Rows Removed by Filter: 11495  
   Heap Blocks: lossy=319  
   Buffers: shared hit=825 read=321  
   ->  Bitmap Index Scan on idx_gis_idx_test_brin  (cost=0.00..2631.00 rows=10000 width=0) (actual time=56.550..56.550 rows=3190 loops=1)  
         Index Cond: ('0103000020E61000000100000004000000000000000000000000000000000000000000000000002E4000000000000000000000000000001E40000000000000244000000000000000000000000000000000'::geometry ~ gis_idx_test_brin.pos)  
         Buffers: shared hit=825 read=2  
 Planning time: 0.402 ms  
 Execution time: 77.244 ms  
(13 rows)  
AI 代码解读

重排后,响应时间从3278毫秒缩短到了77毫秒。

给力。

索引创建方法举例

1、GiST

The syntax for building a GiST index on a "geometry" column is as follows:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );   
AI 代码解读

The above syntax will always build a 2D-index. To get the an n-dimensional index supported in PostGIS 2.0+ for the geometry type, you can create one using this syntax

CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);  
AI 代码解读

2、BRIN

The syntax for building a BRIN index on a "geometry" column is as follows:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );   
AI 代码解读

The above syntax will always build a 2D-index. To get a 3d-dimensional index, you can create one using this syntax

CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d);  
AI 代码解读

You can also get a 4d-dimensional index using the 4d operator class

CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d);  
AI 代码解读

These above syntaxes will use the default number or block in a range, which is 128. To specify the number of blocks you want to summarise in a range, you can create one using this syntax

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]);   
AI 代码解读

Also, keep in mind that a BRIN index will only store one index value for a large number of rows. If your table stores geometries with a mixed number of dimensions, it's likely that the resulting index will have poor performance. You can avoid this drop of performance by choosing the operator class whith the least number of dimensions of the stored geometries

Also the "geography" datatype is supported for BRIN indexing. The syntax for building a BRIN index on a "geography" column is as follows:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geographyfield] );   
AI 代码解读

如何选择GiST和BRIN索引

从搜索需求、搜索速度两个方面来考虑。

1、如果有kNN或者距离排序的查询需求。你只能使用 GiST索引。

2、如果有几何特性(包含、在左边、右边、上、下等查询)可以使用GiST也可以使用BRIN。

但是请注意BRIN要得到好的过滤效果,请对数据按BOX进行重排。这样得到的效果比GIST还要好。

3、如果你很在意空间,或者在意数据写入的OVERHEAD,建议BRIN(考虑到空间相关性的前提下)。

阿里云相关数据库产品

阿里云 RDS PostgreSQL

阿里云 HybridDB for PostgreSQL

小结

PostgreSQL 1000万空间数据的GiST, BRIN索引对比:

1、空间对比,BRIN存储的是块级BOX边界,非常节省空间。

pic

2、写入时延对比,BRIN对写入几乎没有影响。

pic

3、创建索引时延对比,创建BRIN索引特别快。

pic

4、包含查询响应时间对比,BRIN索引,在数据物理存储空间线性的情况下,包含查询、KNN检索性能都特别好。

pic

5、KNN检索性能对比,BRIN的KNN检索,通过包含以及SORT来实现。比GIST略差。

pic

总之BRIN特别适合空间数据连续分布的数据,适合高速范围搜索(即使点查也至少返回一个数据块)。建议用户的GIS轨迹数据(历史静态数据),可以按时间分区,按GEOHASH重排,使用BRIN索引。

而GIST适合任意分布的数据,支持更多的空间检索需求,并且在点查方面效果很好,但是空间占用大,给写入带来的overhead更大。适合更新频繁的当前状态数据的高效率检索。

参考

http://postgis.net/docs/manual-dev/using_postgis_dbmanagement.html#brin_indexes

http://postgis.net/docs/manual-dev/ST_ClusterKMeans.html

http://postgis.net/docs/manual-dev/ST_ClusterDBSCAN.html

https://en.wikipedia.org/wiki/K-means_clustering

《蜂巢的艺术与技术价值 - PostgreSQL PostGIS's hex-grid》

《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
1
0
0
20702
分享
相关文章
B-tree不是万能药:PostgreSQL索引失效的7种高频场景与破解方案
在PostgreSQL优化实践中,B-tree索引虽承担了80%以上的查询加速任务,但因多种原因可能导致索引失效,引发性能骤降。本文深入剖析7种高频失效场景,包括隐式类型转换、函数包裹列、前导通配符等,并通过实战案例揭示问题本质,提供生产验证的解决方案。同时,总结索引使用决策矩阵与关键原则,助你让索引真正发挥作用。
从Explain到执行:手把手优化PostgreSQL慢查询的5个关键步骤
本文深入探讨PostgreSQL查询优化的系统性方法,结合15年数据库优化经验,通过真实生产案例剖析慢查询问题。内容涵盖五大关键步骤:解读EXPLAIN计划、识别性能瓶颈、索引优化策略、查询重写与结构调整以及系统级优化配置。文章详细分析了慢查询对资源、硬件成本及业务的影响,并提供从诊断到根治的全流程解决方案。同时,介绍了索引类型选择、分区表设计、物化视图应用等高级技巧,帮助读者构建持续优化机制,显著提升数据库性能。最终总结出优化大师的思维框架,强调数据驱动决策与预防性优化文化,助力优雅设计取代复杂补救,实现数据库性能质的飞跃。
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
云数据库实战:基于阿里云RDS的Python应用开发与优化
在互联网时代,数据驱动的应用已成为企业竞争力的核心。阿里云RDS为开发者提供稳定高效的数据库托管服务,支持多种数据库引擎,具备自动化管理、高可用性和弹性扩展等优势。本文通过Python应用案例,从零开始搭建基于阿里云RDS的数据库应用,详细演示连接、CRUD操作及性能优化与安全管理实践,帮助读者快速上手并提升应用性能。
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
130 2
PostgreSQL 9种索引的原理和应用场景
PostgreSQL 支持九种主要索引类型,包括 B-Tree、Hash、GiST、SP-GiST、GIN、BRIN、Bitmap、Partial 和 Unique 索引。每种索引适用于不同场景,如 B-Tree 适合范围查询和排序,Hash 仅用于等值查询,GiST 支持全文搜索和几何数据查询,GIN 适用于多值列和 JSON 数据,BRIN 适合非常大的表,Bitmap 适用于低基数列,Partial 只对部分数据创建索引,Unique 确保列值唯一。
【PG锦囊】阿里云 RDS PostgreSQL 版插件—AI 插件(rds_ai)
本文介绍了AI 插件(rds_ai)的核心优势、适用场景等,帮助您更好地了解 rds_ai 插件。想了解更多 RDS 插件信息和讨论交流,欢迎加入 RDS PG 插件用户专项服务群(103525002795)
市场领先者MySQL的挑战者:PostgreSQL的崛起
PostgreSQL(简称PG)是世界上最先进的开源对象关系型数据库,起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称,支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL,但PG在全球范围内广泛应用,受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
239 4
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
466 4
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
265 1

相关产品

  • 云数据库 RDS
  • 云数据库 RDS PostgreSQL 版
  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多
    AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等

    登录插画

    登录以查看您的控制台资源

    管理云资源
    状态一览
    快捷访问