In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on i5-3317U CPU @1,7GHz and 4GB RAM.

UPDATE pvanlagen p1
SET    buildid = sub.b_gid
     , dist    = sub.dist  -- actual distance
          pv_gid, b_gid, dist
   FROM  (
      SELECT gid AS pv_gid, ST_Transform(geom, 31467) AS geom31467
      FROM   pvanlagen
      WHERE  buildid IS NULL  -- not assigned yet
      ) p
        , LATERAL (
      SELECT b.gid AS b_gid
           , round(ST_Distance(p.geom31467
                         , ST_Transform(b.centroid, 31467))::numeric, 2) AS dist  -- see below
      FROM   buildings      b
      LEFT   JOIN pvanlagen p1 ON p1.buildid = b.gid  -- also not assigned ...
      WHERE  p1.buildid IS NULL                       -- ... yet  
      ORDER  BY p.geom31467 <-> ST_Transform(b.centroid, 31467)
      LIMIT  1
      ) b
   ORDER  BY b_gid, dist, pv_gid  -- tie breaker
   ) sub
WHERE   p1.gid = sub.pv_gid;

Details on the query on stackoverflow. I am already working with an spatial GiST Index.

CREATE INDEX build_centroid_gix ON buildings USING gist (ST_Transform(centroid, 31467));

I have to repeat the query, until all pvanlagen.gid are assigned. The query itself seems to be efficient, so that I concluded that the problem has to relate to my server configuration.

The effectiveness of the query follows a logarithmic curve. By now I have 261548 of 269019 assigned, altogether with an execution time of ~76h. First run 158376 rows affected, 02:40:7247 hours execution time. Last but one run (16th run) 984 rows affected, 06:12:21648 hours execution time. Following the logarithmic curve, there is no finishing in sight.

I ran the last Repeat after a manual SET work_mem='2000MB' and 743 rows were affected within 03:52:10851 hours execution time. This is an improvement in the duration, but still not efficient.

I tested EXPLAIN ANALYZEfor 100 Rows, where 37 rows were assigned (which is why I have to repeat the query again and again):

"Update on pvanlagen p1  (cost=555193.42..555201.96 rows=1 width=329) (actual time=333059.379..333059.379 rows=0 loops=1)"
"  ->  Nested Loop  (cost=555193.42..555201.96 rows=1 width=329) (actual time=333057.605..333058.210 rows=37 loops=1)"
"        ->  Subquery Scan on sub  (cost=555192.99..555193.50 rows=1 width=104) (actual time=333057.551..333057.618 rows=37 loops=1)"
"              ->  Unique  (cost=555192.99..555193.49 rows=1 width=40) (actual time=333057.529..333057.575 rows=37 loops=1)"
"                    ->  Sort  (cost=555192.99..555193.24 rows=100 width=40) (actual time=333057.527..333057.540 rows=100 loops=1)"
"                          Sort Key: b.gid, (round((st_distance((st_transform(pvanlagen.geom, 31467)), st_transform(b.centroid, 31467)))::numeric, 2)), pvanlagen.gid"
"                          Sort Method: quicksort  Memory: 29kB"
"                          ->  Nested Loop  (cost=0.41..555189.67 rows=100 width=40) (actual time=4100.470..333057.142 rows=100 loops=1)"
"                                ->  Limit  (cost=0.00..200.34 rows=100 width=36) (actual time=1.705..2.972 rows=100 loops=1)"
"                                      ->  Seq Scan on pvanlagen  (cost=0.00..20749.08 rows=10357 width=36) (actual time=1.703..2.953 rows=100 loops=1)"
"                                            Filter: (buildid IS NULL)"
"                                            Rows Removed by Filter: 625"
"                                ->  Limit  (cost=0.41..5549.86 rows=1 width=36) (actual time=3330.538..3330.538 rows=1 loops=100)"
"                                      ->  Nested Loop Anti Join  (cost=0.41..16411135680.91 rows=2957254 width=36) (actual time=3330.536..3330.536 rows=1 loops=100)"
"                                            Join Filter: (p1_1.buildid = b.gid)"
"                                            Rows Removed by Join Filter: 15714446"
"                                            ->  Index Scan using build_centroid_gix on buildings b  (cost=0.41..594040.19 rows=3226273 width=36) (actual time=0.292..3.760 rows=109 loops=100)"
"                                                  Order By: (st_transform(centroid, 31467) <-> (st_transform(pvanlagen.geom, 31467)))"
"                                            ->  Materialize  (cost=0.00..23119.29 rows=269019 width=4) (actual time=0.011..15.920 rows=143604 loops=10943)"
"                                                  ->  Seq Scan on pvanlagen p1_1  (cost=0.00..20723.19 rows=269019 width=4) (actual time=0.004..215.509 rows=269019 loops=1)"
"        ->  Index Scan using pv_bis2010_bayern_wgs84_pkey on pvanlagen p1  (cost=0.42..8.44 rows=1 width=229) (actual time=0.011..0.011 rows=1 loops=37)"
"              Index Cond: (gid = sub.pv_gid)"
"Planning time: 4.141 ms"
"Execution time: 333067.649 ms"

Any idea on how to improve the efficiency is welcome!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.