[postgis-tickets] r14924 - Improve query performance in cb_getFaceContainingPoint

[Sandro]

Author: strk
Date: 2016-05-31 00:22:50 -0700 (Tue, 31 May 2016)
New Revision: 14924

Modified:
   trunk/topology/postgis_topology.c
Log:
Improve query performance in cb_getFaceContainingPoint

Avoids 1 output and 2 parse calls for hexwkb.
Was found reducing runtime from 23 seconds to 16 seconds for
adding a single polygon with a large shell and 129 holes
to an empty topology.

Modified: trunk/topology/postgis_topology.c
===================================================================
--- trunk/topology/postgis_topology.c	2016-05-31 07:22:42 UTC (rev 14923)
+++ trunk/topology/postgis_topology.c	2016-05-31 07:22:50 UTC (rev 14924)
@@ -79,6 +79,7 @@
   int srid;
   double precision;
   int hasZ;
+  Oid geometryOID;
 };
 
 /* utility funx */
@@ -156,7 +157,8 @@
   MemoryContext oldcontext = CurrentMemoryContext;
 
   initStringInfo(sql);
-  appendStringInfo(sql, "SELECT id,srid,precision FROM topology.topology "
+  appendStringInfo(sql, "SELECT id,srid,precision,null::geometry"
+                        " FROM topology.topology "
                         "WHERE name = '%s'", name);
   spi_result = SPI_execute(sql->data, !be->data_changed, 0);
   MemoryContextSwitchTo( oldcontext ); /* switch back */
@@ -216,6 +218,9 @@
     topo->precision = DatumGetFloat8(dat);
   }
 
+  /* we're dynamically querying geometry type here */
+  topo->geometryOID = SPI_tuptable->tupdesc->attrs[3]->atttypid;
+
   POSTGIS_DEBUGF(1, "cb_loadTopologyByName: topo '%s' has "
                     "id %d, srid %d, precision %g",
              name, topo->id, topo->srid, topo->precision);
@@ -2418,20 +2423,31 @@
   Datum dat;
   LWT_ELEMID face_id;
   size_t hexewkb_size;
-  char *hexewkb;
+  SPIPlanPtr plan;
+  GSERIALIZED *pts;
+  Datum values[1];
+  Oid argtypes[1];
 
   initStringInfo(sql);
 
-  hexewkb = lwgeom_to_hexwkb(lwpoint_as_lwgeom(pt), WKB_EXTENDED, &hexewkb_size);
+  pts = geometry_serialize(lwpoint_as_lwgeom(pt));
+  if ( ! pts ) {
+    cberror(topo->be_data, "%s:%d: could not serialize query point",
+            __FILE__, __LINE__);
+    return -2;
+  }
   /* TODO: call GetFaceGeometry internally, avoiding the round-trip to sql */
-  appendStringInfo(sql, "SELECT face_id FROM \"%s\".face "
-                        "WHERE mbr && '%s'::geometry AND ST_Contains("
-     "topology.ST_GetFaceGeometry('%s', face_id), "
-     "'%s'::geometry) LIMIT 1",
-      topo->name, hexewkb, topo->name, hexewkb);
-  lwfree(hexewkb);
+  appendStringInfo(sql,
+                   "SELECT face_id FROM \"%s\".face "
+                   "WHERE mbr && $1 AND _ST_Contains("
+                   "topology.ST_GetFaceGeometry('%s', face_id), $1)"
+                   " LIMIT 1",
+                   topo->name, topo->name);
 
-  spi_result = SPI_execute(sql->data, !topo->be_data->data_changed, 1);
+  values[0] = PointerGetDatum(pts);
+  argtypes[0] = topo->geometryOID;
+  spi_result = SPI_execute_with_args(sql->data, 1, argtypes, values, NULL,
+                                     !topo->be_data->data_changed, 1);
   MemoryContextSwitchTo( oldcontext ); /* switch back */
   if ( spi_result != SPI_OK_SELECT ) {
 		cberror(topo->be_data, "unexpected return (%d) from query execution: %s",