PosgtreSQL使用st_transform，st_makepoint和st_contains优化查询 [英] PosgtreSQL Optimize Query with st_transform, st_makepoint, and st_contains

问题描述

我有以下查询:

UPDATE  DestinTable
SET  destin = geomVal 
FROM GeomTable
WHERE st_contains(st_transform(geom, 4326), st_setsrid(
      st_makepoint(d_lon::double precision, d_lat::double precision), 4326));

此查询有效，但速度很慢.我必须在一个非常大的表上运行更新，并且需要8个多小时才能完成更新(我在5个不同的列上运行该更新). 我想知道是否有一种方法可以优化此查询以使其运行更快.我没有意识到与st_contains()方法相关的幕后工作，因此可能缺少一些明显的解决方案.

This query works, but it is very slow. I have to run an update on a very large table, and it is taking a 8+ hours to complete (I run this on 5 different columns). I wanted to know if there was a way to optimize this query to make it run faster. I am unaware of the behind the scenes work associated with an st_contains() method, so there may be some obvious solutions that I am missing.

推荐答案

最简单的方法是在ST_TRANSFORM上创建索引

The easiest way is to create an index on ST_TRANSFORM

CREATE INDEX idx_geom_4326_geomtable
  ON GeomTable
  USING gist
  (ST_Transform(geom, 26986))
  WHERE geom IS NOT NULL;

如果表中的所有字段都在一个SRID中，则在该表上创建普通的GIST索引并将要提供的点转换为本地SRID甚至会更加容易

If you have all the fields in one SRID in the table it will be even easier to create a normal GIST index on that table and transform the point you're supplying to the local SRID

这篇关于PosgtreSQL使用st_transform，st_makepoint和st_contains优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！