CODEWITHSUNDEEP
postgresqlpostgispostgresql-performance
Boppa
Boppa

Reputation: 23

Slow update with ST_Contains()

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 ?

Explain Analyze:

Update  (cost=0.00..324.85 rows=1 width=286) (actual time=353.932..353.932 rows=0 loops=1)
  Buffers: shared hit=73090 read=1
  ->  Nested Loop  (cost=0.00..324.85 rows=1 width=286) (actual time=0.544..341.936 rows=471 loops=1)
        Join Filter: _st_contains(s.geom, prob.geom)
        Buffers: shared hit=69985
        ->  Seq Scan on world s  (cost=0.00..83.44 rows=244 width=48) (actual time=0.009..0.319 rows=244 loops=1)
              Buffers: shared hit=81
        ->  Index Scan using idx_prob_geom on prob  (cost=0.00..0.73 rows=1 width=270) (actual time=0.003..0.024 rows=9 loops=244)
              Index Cond: (s.geom && prob.geom)
              Buffers: shared hit=533
Total runtime: 354.640 ms

Upvotes: 2

Views: 1420

Answers (1)

monoid
monoid

Reputation: 1671

ST_CONTAINS cannot use index. Try this:

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

&& checks bounding boxes and does use index.

Upvotes: 2

Related Questions