Slow update with ST_Contians()

[androclos]

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 (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql