PosgtreSQL Optimize Query with st_transform, st_ma

问题:

I have the following query:

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));

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.

回答1:

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;

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