From: androclos <celgene121@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Slow update with ST_Contians()
Date: Thu, 11 Apr 2013 09:38:39 -0700 (PDT)
Message-ID: <1365698318594-5751814.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: celgene121@gmail.com
Subject: Re: Slow update with ST_Contians()
In-Reply-To: <1365698318594-5751814.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox