postgresql 具有大几何列性能的PostGIS表

ykejflvf 于 2023-02-15 发布在 PostgreSQL

关注(0)|答案(3)|浏览(170)

routed_way表有一个名为way的列，其中存储了车辆的路径。这些路径的长度可能差异很大，有些很短，有些则包含数百万个点。虽然该表只有数千行，但有两个问题需要解决。
1.从此表中删除行的速度非常慢，可能需要几分钟。
1.使用"st_intersects"函数的查询也很慢，需要将近一分钟才能完成。

create table public.routed_way
(
    id              uuid default gen_random_uuid() not null primary key,
    created_at      timestamp with time zone default CURRENT_TIMESTAMP,
    updated_at      timestamp with time zone,
    sourcer_class   text not null,
    sourcer_id      text  not null,
    route_start     timestamp with time zone,
    route_end       timestamp with time zone not null,
    way             geometry(LineString, 4326) not null,
    time_delta      integer[],
    elevation_delta integer[]
);
alter table public.routed_way owner to postgres;
create index idx_routed_way_created_at    on public.routed_way (created_at);
create index idx_routed_way_sourcer_class on public.routed_way (sourcer_class);
create index idx_routed_way_route_start   on public.routed_way (route_start);
create index idx_routed_way_way           on public.routed_way using gist (way);

select 
    id, 
    route_start, 
    route_end, 
    st_transform(way, 3857) as geometry
from routed_way
where 
    (route_start > now() - interval '7 day' 
    and route_start < now() - interval '3 day')
  and st_intersects(st_transform(way, 3857),!bbox!)

Gather  (cost=1072.66..63827.33 rows=1 width=64)
  Workers Planned: 1
  ->  Parallel Bitmap Heap Scan on routed_way  (cost=72.66..62827.23 rows=1 width=64)
        Recheck Cond: ((route_start > (now() - '7 days'::interval)) AND (route_start < (now() - '3 days'::interval)))
"        Filter: st_intersects(st_transform(way, 3857), '0103000020110F00000100000005000000010000E0FFFFEFC7010000E0FFFFEFC7010000E0FFFFEFC7010000E0FFFFEF47010000E0FFFFEF47010000E0FFFFEF47010000E0FFFFEF47010000E0FFFFEFC7010000E0FFFFEFC7010000E0FFFFEFC7'::geometry)"
        ->  Bitmap Index Scan on idx_routed_way_route_start  (cost=0.00..72.66 rows=2037 width=0)
              Index Cond: ((route_start > (now() - '7 days'::interval)) AND (route_start < (now() - '3 days'::interval)))

我不确定Mapnik在!bbox!中使用了什么值，所以我尝试在st_makeenvelope()中使用一些值，但是根据EXPLAIN的输出，似乎没有应用所需的索引。
一个一个三个一个一个一个一个一个四个一个一个一个一个一个五个一个

postgresql

来源：https://stackoverflow.com/questions/75426065/postgis-table-with-large-geometry-column-performance

3条答案

按热度按时间

ie3xauqp1#

根据您最初展示的内容，geometry类型的列way上没有索引，这导致了堆扫描。
1.您需要在列上应用GiST index，以加快对其执行的所有PostGIS ST_操作。
1.通过跳过吐司机制-set storage external，可以进一步加快速度。
1.使用该索引的cluster
1.使用analyze确保表统计信息是最新的
1.如果表上的流量不高，可以增加索引的fillfactor。

alter table public.routed_way
   alter column way
   set storage external;
create index idx_routed_way_gist_way 
   on public.routed_way 
   using gist(way) 
--Default is 90. 100 is for static, read-only.
   with (fillfactor=100); 
cluster verbose public.routed_way using idx_routed_way_gist_way;
analyze public.routed_way;

您还在整个way列上使用了ST_Transform，而不是只在与之相交的!bbox!几何体上使用，这似乎会产生相反的效果。如果您颠倒过来，则每个语句只需执行一次，而不是每个目标表记录执行一次：

select 
    id, 
    route_start, 
    route_end, 
    st_transform(way, 3857) as geometry
from routed_way
where 
    (route_start > now() - interval '7 day' 
    and route_start < now() - interval '3 day')
  and st_intersects(way,st_transform(!bbox!,4326));

如果您的delete语句使用类似的基于几何的where子句，您可以用同样的方法加快它们的速度。
原则上，几何体越大，索引的用处就越小：如果你的几何体的边界框跨越了整个目标区域，那么它就被认为是与所有东西都有空间关联的。你可以尝试使用ST_Segmentize()将它分割成更小的片段，并使用更小的边界框，这将极大地加快所有东西的速度，类似于ST_Subdivide() is used (on all the Things)。
在极端的情况下，如果要比较的!bbox!几何体足够大，可以考虑将st_subdividing放入一个临时表中，建立索引，然后在st_intersects(way_segmentized,bbox_subdivided)上与该表连接。
几何图形的细分和分割也会加快速度，不仅仅是因为它更容易比较较小的对象（同样，它更容易处理较短的文本和较小的数字），更容易丢弃和忽略那些距离足够远的对象（r树索引），而且这也减少了将它们从页面中删除的需要/机会。
set storage external除外，并确保您的统计信息是最新的（您使用的其他索引也会从中受益），但如果查询的目标是表中的 * 所有内容 *，则上述任何一项都不会提高性能。重新安排数据的存储、访问和处理方式可以帮助选择性查询更快地获取目标记录，但它不会影响底层存储介质的读取速度，如果您要求数据库读取整个内容，则读取速度将决定您的响应时间。
任何足够非特定的过滤器，在您的情况下是一个大的!bbox!几何体，如您提到的全局ST_MakeEnvelope(-180,-90,180,90,4326)，将忽略您的空间索引，因为很明显它们匹配其中的所有内容，所以它们可能根本不被考虑。

赞(0）回复(0）举报 2023-02-15

hts6caw32#

除了@Zegarek答案，如果您无法控制边界框交集子句，因为它是由Map服务器自动添加的，您还可以在转换的方式上创建索引：

create index idx_routed_way_way_3857
    on public.routed_way using gist (st_transform(way,3857));

赞(0）回复(0）举报 2023-02-15

bt1cpqcv3#

在一个记录中包含如此大的几何体几乎总是一个糟糕的选择。
尝试分割大型几何体（例如，小于1000个点且长度小于500 m），您将看到更可预测和更好的性能。
请参见此处使用ST_Segmentize()和ST_Subdivide()的示例-https://postgis.net/docs/ST_Subdivide.html

SELECT ST_Subdivide( 
         ST_Segmentize( 'LINESTRING(0 0, 85 85)'::geography
                      , 1200000 -- max segment length, m
                      ) ::geometry
         , 8 -- max vertices
        );

赞(0）回复(0）举报 2023-02-15

我来回答

postgresql 具有大几何列性能的PostGIS表

3条答案

相关问题

热门标签

最新问答