From 5aac209e8f1fe3c64a62ae2c51dac46d5b05a585 Mon Sep 17 00:00:00 2001
From: Sandro Santilli <strk@keybit.net>
Date: Tue, 31 May 2016 07:22:50 +0000
Subject: [PATCH] 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.
git-svn-id: http://svn.osgeo.org/postgis/trunk@14924 b70326c6-7e19-0410-871a-916f4a2858ee
---
topology/postgis_topology.c | 38 ++++++++++++++++++++++++++-----------
1 file changed, 27 insertions(+), 11 deletions(-)
diff --git a/topology/postgis_topology.c b/topology/postgis_topology.c
index f0fbcc8fc..13fd5f394 100644
--- a/topology/postgis_topology.c
+++ b/topology/postgis_topology.c
@@ -79,6 +79,7 @@ struct LWT_BE_TOPOLOGY_T {
int srid;
double precision;
int hasZ;
+ Oid geometryOID;
};
/* utility funx */
@@ -156,7 +157,8 @@ cb_loadTopologyByName(const LWT_BE_DATA* be, const char *name)
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 @@ cb_loadTopologyByName(const LWT_BE_DATA* be, const char *name)
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 @@ cb_getFaceContainingPoint( const LWT_BE_TOPOLOGY* topo, const LWPOINT* pt )
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);
-
- spi_result = SPI_execute(sql->data, !topo->be_data->data_changed, 1);
+ 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);
+
+ 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",
--
2.40.0