PostgreSQL 多维空间几何对象 相交、包含 高效率检索实践

  • 时间:
  • 浏览:0

https://www.postgresql.org/docs/devel/static/cube.html

转自阿里云德哥

create index idx_test2_cb on test2 using gist(cb);

5、中有 查询性能

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb && cube '[(37,367,948), (93,372,989)]';

中有 :

(xmin1 <= xmin2 and xmax1 >= xmax2)

and

(ymin1 <= ymin2 and ymax1 >= ymax2)

and

(zmin1 <= zmin2 and zmax1 >= zmax2)

相交:

每个坐标都相交,注意任意坐标相交的方位有

1、创建 cube 插件

《PostgreSQL 黑科技 range 类型及 gist index 20x+ speedup than Mysql index combine query》

《PostgreSQL 10 参数模板 - 珍藏级》

每条边全是相交即CUBE相交,表达如下

Index Scan using idx_test2_cb on public.test2 (cost=0.25..76.66 rows=60 00 width=60 ) (actual time=0.086..0.943 rows=483 loops=1)

Output: id, cb

Index Cond: (test2.cb && '(37, 367, 948),(93, 372, 989)'::cube)

Buffers: shared hit=60 5

Planning Time: 0.085 ms

Execution Time: 1.011 ms

(6 rows)

参考

《PostgreSQL 之类人群圈选,人群扩选,向量之类 使用实践》

Time: 79.947 ms

使用CUBE插件,我们都我们都还还才能 用来计算多维对象的向量之类性,按向量之类性排序。参考末尾连接。

postgres=# select * from test1 limit 10;

https://www.postgresql.org/docs/devel/static/cube.html

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb @> cube '(37,367,948)';

Time: 0.893 ms

优化

不可能 SQL请求返回的记录数非常多,建议流式返回,一同建议根据BLOCK设备的随机IO能力设置正确的random_page_cost参数。

(15 rows)

select * from test1 where

((x_min >= 37 and x_min <= 93) or (x_max >= 37 and x_max <= 93) or (x_min <= 37 and x_max >= 93))

and

((y_min >= 367 and y_min <= 372) or (y_max >= 367 and y_max <= 372) or (y_min <= 367 and y_max >= 372))

and

((z_min >= 948 and z_min <= 989) or (z_max >= 948 and z_max <= 989) or (z_min <= 948 and z_max >= 989))

;

Index Scan using idx_test2_cb on public.test2 (cost=0.25..20.65 rows=60 0 width=60 ) (actual time=0.153..0.420 rows=107 loops=1)

Output: id, cb

Index Cond: (test2.cb @> '(37, 367, 948)'::cube)

Buffers: shared hit=121

Planning Time: 0.077 ms

Execution Time: 0.448 ms

(6 rows)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test1 where

((x_min >= 37 and x_min <= 93) or (x_max >= 37 and x_max <= 93) or (x_min <= 37 and x_max >= 93))

and

((y_min >= 367 and y_min <= 372) or (y_max >= 367 and y_max <= 372) or (y_min <= 367 and y_max >= 372))

and

((z_min >= 948 and z_min <= 989) or (z_max >= 948 and z_max <= 989) or (z_min <= 948 and z_max >= 989))

;

(15 rows)

Index Scan using idx_test2_cb on public.test2 (cost=0.25..20.65 rows=60 0 width=60 ) (actual time=0.154..0.247 rows=15 loops=1)

Output: id, cb

Index Cond: (test2.cb @> '(37, 367, 948),(93, 372, 989)'::cube)

Buffers: shared hit=26

Planning Time: 0.196 ms

Execution Time: 0.269 ms

(6 rows)

中有 和相交查询

在介绍CUBE类型前,我们都我们都不可能 使用6个字段来表达立方体,那末 相交,中有 分别如可标示呢?

postgres=# timing

Timing is on.

postgres=# select * from test2 where cb @> cube '[(37,367,948), (93,372,989)]';

在60 万空间对象的请况下,性能提升了60 倍。

PS, test1表(分字段表达)即使使用BTREE索引,效果本来好,不可能 多字段的范围检索,初级索引是要全扫描的,时候有另有有一一个智能DNS的例子之类,使用GIST提升了20多倍性能。

Time: 120.283 ms

cube 类型

cube的多维体表达妙招如下

Time: 60 .269 ms

4、相交查询

create table test1 (

id int primary key,

x_min int,

y_min int,

z_min int,

x_max int,

y_max int,

z_max int

);

2、写入60 万记录

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb @> cube '[(37,367,948), (93,372,989)]';

alter system set random_page_cost=1.3;

select pg_reload_conf();

小结

使用cube插件,我们都我们都在对多维几何空间对象进行查询时,还才能 使用GIST索引,性能非常棒。

create table test2 (

id int primary key,

cb cube

);

3、将数据导入test2 cube表

Seq Scan on public.test1 (cost=0.00..39229.87 rows=4364 width=28) (actual time=0.026..119.539 rows=483 loops=1)

Output: id, x_min, y_min, z_min, x_max, y_max, z_max

Filter: ((((test1.x_min >= 37) AND (test1.x_min <= 93)) OR ((test1.x_max >= 37) AND (test1.x_max <= 93)) OR ((test1.x_min <= 37) AND (test1.x_max >= 93))) AND (((test1.y_min >= 367) AND (test1.y_min <= 372)) OR ((test1.y_max >= 367) AND (test1.y_max <= 372)) OR ((test1.y_min <= 367) AND (test1.y_max >= 372))) AND (((test1.z_min >= 948) AND (test1.z_min <= 989)) OR ((test1.z_max >= 948) AND (test1.z_max <= 989)) OR ((test1.z_min <= 948) AND (test1.z_max >= 989))))

Rows Removed by Filter: 999517

Buffers: shared hit=1835

Planning Time: 0.135 ms

Execution Time: 119.621 ms

(7 rows)

---

Time: 0.685 ms

6、相交查询性能

select * from test1 where

(x_min <= 37 and x_max >= 93)

and

(y_min <= 367 and y_max >= 372)

and

(z_min <= 948 and z_max >= 989);



多维空间对象的几何运算,高传输速率检索实践。

Time: 1.60 6 ms

7、除此以外,CUBE还支持本来的几何计算操作符,也还才能 做中有 点的查询。

(10 rows)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test1 where

(x_min <= 37 and x_max >= 93)

and

(y_min <= 367 and y_max >= 372)

and

(z_min <= 948 and z_max >= 989);

It does not matter which order the opposite corners of a cube are entered in.

((xmin1 >= xmin2 and xmin1 <= xmax2) or (xmax1 >= xmin2 and xmax1 <= xmax2) or (xmin1 <= xmin2 and xmax1 >= xmax2))

and

((ymin1 >= ymin2 and ymin1 <= ymax2) or (ymax1 >= ymin2 and ymax1 <= ymax2) or (ymin1 <= ymin2 and ymax1 >= ymax2))

and

((zmin1 >= zmin2 and zmin1 <= zmax2) or (zmax1 >= zmin2 and zmax1 <= zmax2) or (zmin1 <= zmin2 and zmax1 >= zmax2))

使用6个字段的空间计算性能

1、创建测试表

---

Time: 0.297 ms

postgres=# end;

COMMIT

Time: 0.138 ms

不可能 是SSD盘,建议random_page_cost设置为1.1-1.3

insert into test2 select id, cube(array[x_min,y_min,z_min], array[x_max,y_max,z_max]) from test1;

4、给CUBE类型创建gist索引

When the corners coincide, cube stores only one corner along with an “is point” flag to avoid wasting space.

之类我们都我们都在数据库中存储了多维几何对象,还才能 使用lower, upper的数组来表达,之类3维度对象:

《PostgreSQL 黑科技 range 类型及 gist index 20x+ speedup than Mysql index combine query》

《通过空间思想理解GiST索引的构造》

CUBE

[

xmin1

ymin1

zmin1

,

xmax1

ymax1

zmax1

]

在介绍CUBE类型前,我们都我们都还才能 使用6个字段(xmin,xmax,ymin,ymax,zmin,zmax)来表达另有有一一个立方体。

insert into test1 select id, x, y, z, x+1+(random()60 )::int, y+1+(random()60 )::int, z+1+(random()*60 )::int

from (select id, (random()60 0)::int x, (random()60 0)::int y, (random()*60 0)::int z from generate_series(1,60 0000) t(id)) t ;

记录如下

explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb @> cube '[(37,367,948), (93,372,989)]';

postgres=# begin;

BEGIN

postgres=# declare cur1 cursor for select * from test2 where cb && cube '[(37,367,948), (93,372,989)]';

DECLARE CURSOR

postgres=# timing

Timing is on.

postgres=# fetch 10 from cur1;

-----

(10 rows)

3、中有 查询

select * from test2 where cb && cube '[(37,367,948), (93,372,989)]';

流式返回例子

The cube functions automatically swap values if needed to create a uniform “lower left — upper right” internal representation.

create extension cube;

2、创建测试表

Seq Scan on public.test1 (cost=0.00..13220.05 rows=539 width=28) (actual time=0.024..79.397 rows=15 loops=1)

Output: id, x_min, y_min, z_min, x_max, y_max, z_max

Filter: ((test1.x_min <= 37) AND (test1.x_max >= 93) AND (test1.y_min <= 367) AND (test1.y_max >= 372) AND (test1.z_min <= 948) AND (test1.z_max >= 989))

Rows Removed by Filter: 999985

Buffers: shared hit=1835

Planning Time: 0.103 ms

Execution Time: 79.421 ms

(7 rows)