Date: Thu, 11 Apr 2013 09:38:39 -0700 (PDT)
From: androclos <celgene121@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1365698318594-5751814.post@n5.nabble.com>
Subject: Slow update with ST_Contians()
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

UPDATE tbl
SET city=s.city_name
FROM shp AS s
WHERE
ST_CONTAINS(s.city_geom,geom);

With the code above i can add exact city to a GPS point. It runs about 45-50
min on 50 million rows. There are about 4000 cities in the "city" table that
have to be checked.

I have another shape file with 19 counties in a given country(only 1
country). It takes it about 1,5 hour to add counties to points.

i have a third shape file with 52 EU countries. It runs almost 25 hours with
the same sql query.

Every table has index by geom, like:

CREATE INDEX idx_txt_geom ON txt USING GIST(geom);

Q: Why is it so slow when it has to check only a few polygons ?


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-update-with-ST-Contians-tp5751814.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql