优化PostGIS查询，ST_Intersects
优化PostGIS查询，ST_Intersects
提问于 2015-07-01 01:27:29
我正在尝试在两个表之间运行空间查询。表一(prism_ppt_monthly -详情见下文)是月度降水量数据。表二(usgs_basin_boundary -详情见下文)是水文流域边界的多边形。
我想为每个流域创建一个总降水量的时间序列。我有一个查询可以做到这一点(参见下面的详细信息)，但对于单个计算，它需要近4.75秒。考虑到我有1440个月的降水数据和近40个盆地，这个查询将花费: 4.75秒* 1440 * 40 = 77小时。
下面是关于查询和表的信息。我在每个表上都有空间索引(gist)，并对每个表进行了真空分析。任何关于我如何能够加速这件事的想法都将非常感谢！
查询：
EXPLAIN ANALYZE
SELECT filename,date_from,date_to,site_no,sqmi,(ST_SummaryStats(rast)).* FROM prism_ppt_monthly, usgs_basin_boundary WHERE ST_Intersects(rast,ST_Transform(geom,4269)) LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=65964.53..66000.10 rows=1 width=81) (actual time=4764.969..4764.972 rows=1 loops=1)
-> Nested Loop (cost=65964.53..66782.60 rows=23 width=81) (actual time=4764.963..4764.963 rows=1 loops=1)
Join Filter: _st_intersects(st_transform(usgs_basin_boundary.geom, 4269), prism_ppt_monthly.rast, NULL::integer)
-> Hash Semi Join (cost=65964.53..66610.73 rows=47 width=126256) (actual time=4587.961..4587.961 rows=1 loops=1)
Hash Cond: ((usgs_basin_boundary.site_no)::text = df_flow.code)
-> Seq Scan on usgs_basin_boundary (cost=0.00..639.09 rows=2509 width=126256) (actual time=0.007..1.279 rows=595 loops=1)
-> Hash (cost=65963.94..65963.94 rows=47 width=9) (actual time=4585.313..4585.313 rows=47 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> HashAggregate (cost=65963.00..65963.47 rows=47 width=9) (actual time=4585.126..4585.215 rows=47 loops=1)
-> Seq Scan on df_flow (cost=0.00..63244.20 rows=1087520 width=9) (actual time=5.826..2367.593 rows=1087520 loops=1)
-> Index Scan using prism_ppt_monthly_rast_gist on prism_ppt_monthly (cost=0.00..0.40 rows=1 width=64) (actual time=0.034..0.034 rows=1 loops=1)
Index Cond: ((rast)::geometry && st_transform(usgs_basin_boundary.geom, 4269))
Total runtime: 4765.151 ms表1：
\d+ prism_ppt_monthly
Table "public.prism_ppt_monthly"
Column | Type | Modifiers | Storage | Description
-----------+---------+-----------------------------------------------------------------+----------+-------------
rid | integer | not null default nextval('prism_ppt_monthly_rid_seq'::regclass) | plain |
rast | raster | | extended |
filename | text | | extended |
date_from | date | | plain |
date_to | date | | plain |
Indexes:
"prism_ppt_monthly_pkey" PRIMARY KEY, btree (rid)
"prism_ppt_monthly_rast_gist" gist (st_convexhull(rast))
Check constraints:
"enforce_height_rast" CHECK (st_height(rast) = 621)
"enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast), '0103000020AD10000001000000050000005555555555415FC01E01000000F8484060A9AAAAAA9E50C01E01000000F8484060A9AAAAAA9E50C0F5FFFFFFFF0F38405555555555415FC0F5FFFFFFFF0F38405555555555415FC01E01000000F84840'::geometry))
"enforce_nodata_values_rast" CHECK (_raster_constraint_nodata_values(rast)::numeric(16,10)[] = '{-9999}'::numeric(16,10)[])
"enforce_num_bands_rast" CHECK (st_numbands(rast) = 1)
"enforce_out_db_rast" CHECK (_raster_constraint_out_db(rast) = '{f}'::boolean[])
"enforce_pixel_types_rast" CHECK (_raster_constraint_pixel_types(rast) = '{32BF}'::text[])
"enforce_same_alignment_rast" CHECK (st_samealignment(rast, '0100000000365755555555A53F365755555555A5BF5555555555415FC01E01000000F8484000000000000000000000000000000000AD10000001000100'::raster))
"enforce_scalex_rast" CHECK (st_scalex(rast)::numeric(16,10) = 0.04166666666667::numeric(16,10))
"enforce_scaley_rast" CHECK (st_scaley(rast)::numeric(16,10) = (-0.04166666666667)::numeric(16,10))
"enforce_srid_rast" CHECK (st_srid(rast) = 4269)
"enforce_width_rast" CHECK (st_width(rast) = 1405)
Has OIDs: no表2：
\d+ usgs_basin_boundary
Table "public.usgs_basin_boundary"
Column | Type | Modifiers | Storage | Description
----------+-----------------------------+-------------------------------------------------------------------+----------+-------------
gid | integer | not null default nextval('usgs_basin_boundary_gid_seq'::regclass) | plain |
site_no | character varying(15) | | extended |
sqmi | numeric | | main |
abs_diff | numeric | | main |
geom | geometry(MultiPolygon,5070) | | main |
Indexes:
"usgs_basin_boundary_pkey" PRIMARY KEY, btree (gid)
"usgs_basin_boundary_shape_gist" gist (geom)
Has OIDs: no回答 2
Stack Overflow用户
发布于 2015-07-01 17:52:57
没有使用usgs_basin_boundary.geom上的索引，因为您正在调用ST_Transform(geom,4269)，所以应该在转换结果(as mentioned in the manual)上创建索引
CREATE INDEX jkb_usgs_basin_boundary_geom_t_4269
ON usgs_basin_boundary
USING gist
(ST_Transform(geom,4269))Stack Overflow用户
发布于 2015-07-01 17:53:10
它不会解决您的所有问题，但我只是在您的查询中偶然发现了一些东西：
SELECT (...) WHERE ST_Intersects(rast,ST_Transform(geom,4269)) LIMIT 1;重要的部分是这一点：
ST_Transform(geom,4269)您正在查询的正中间投影一个几何图形。虽然这肯定是可能的，但它可能不是一个好的实践。您可以预先将'geom‘映射到'SRID:4269’到另一个表中。之后，您只需从另一个表访问转换后的几何图形即可。
将geom转换为新表后，您可能还希望在该表上创建索引。这可能会稍微提高性能。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31144593
复制相关文章
相似问题
腾讯云开发者
