0

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.

Soatl
  • 10,224
  • 28
  • 95
  • 153
  • 1
    what is the original SRID for `DestinTable.geom` and why are you transforming it? – Mike T Mar 13 '14 at 03:47
  • Please use fully qualified column names in your query. (add table aliases) It is unclear from your question from which of the two tables the columns stem from. – joop Mar 13 '14 at 09:25

1 Answers1

0

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

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47