title: Slow update with ST_Contains()
body: <pre><code>UPDATE tbl
  SET city=s.city_name
  FROM shp AS s
  WHERE
  ST_CONTAINS(s.city_geom,geom);
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>i have a third shape file with 52 EU countries.
  It runs almost 25 hours with the same sql query.</p>
  
  <p>Every table has index by geom, like:</p>
  
  <pre><code>CREATE INDEX idx_txt_geom ON txt USING GIST(geom);
  </code></pre>
  
  <p><strong>Q</strong>: Why is it so slow when it has to check only a few polygons ?</p>
  
  <p>Explain Analyze:</p>
  
  <pre><code>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
    -&gt;  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
          -&gt;  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
          -&gt;  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 &amp;&amp; prob.geom)
                Buffers: shared hit=533
  Total runtime: 354.640 ms
  </code></pre>