[Geoserver-devel] Performance: ST_Force_2D vs. ST_Force2D

GeoServer GeoServer Developer
,
1

Hey All,

I just upgraded to a new machine (newer PostgreSQL, newer PostGIS, faster
CPU) and was dismayed to see my GeoServer performed a lot worse.

One of the problems turned out to be that the ST_Force_2D has been
depreciated, and now looks like this:

CREATE OR REPLACE FUNCTION postgis.st_force_2d(geometry)
RETURNS geometry
LANGUAGE sql
IMMUTABLE STRICT
AS $function$ SELECT _postgis_deprecate('ST_Force_2d', 'ST_Force2D',
'2.1.0');
    SELECT ST_Force2D($1);
  $function$

The outcome of this is that it is much slower, as shown:

snapmaps=# select count(st_force2d(the_geom)) from water_area;
count
--------
927254
(1 row)

Time: 521.223 ms

snapmaps=# select count(st_force_2d(the_geom)) from water_area;
count
--------
927254
(1 row)

Time: 4139.768 ms

A test (not particularly big) test through Geoserver:

time wget -qO-
"http://localhost:8080/geoserver/psma/wms?LAYERS=terrapsma&STYLES=&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A3857&BBOX=10736535.265029,-5473866.3665441,17796334.870301,-976310.63345588&WIDTH=518&HEIGHT=330"

/dev/null

real 0m2.733s
user 0m0.001s
sys 0m0.005s

Compared with a test when I have removed the call to _postgis_deprecate:

time wget -qO-
"http://localhost:8080/geoserver/psma/wms?LAYERS=terrapsma&STYLES=&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A3857&BBOX=10736535.265029,-5473866.3665441,17796334.870301,-976310.63345588&WIDTH=518&HEIGHT=330"

/dev/null

real 0m2.037s
user 0m0.000s
sys 0m0.006s

All commands were run multiple times to avoid any cache warming issues.

If anything maybe a warning about this needs to be put in the documentation
somewhere?

Cheers,

James Sewell

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-ST-Force-2D-vs-ST-Force2D-tp5156337.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.

2

Hi James,
I’m wondering if the issue should be reported to the PostGIS developers first?
If I look at the PostGIS documentation I see that in 2.0 the new function name did not yet exist,
replacing a name is fine, but doing so in a way that incurs in so much overhead seems really bad to me.

Not sure how much good documenting this will do to us, since the function name is hard coded,
I guess we’ll need someone to update the PostgisDialect back in GeoTools and
use the right function name depending on the current postgis version (we already have the version
parsed due to the changes that occurred in postgis 2.0)

A ticket in jira, and a pull request, would be much appreciated

Cheers
Andrea

···

On Thu, Aug 14, 2014 at 7:01 AM, james.sewell@anonymised.com <James.Sewell@anonymised.com> wrote:

Hey All,

I just upgraded to a new machine (newer PostgreSQL, newer PostGIS, faster
CPU) and was dismayed to see my GeoServer performed a lot worse.

One of the problems turned out to be that the ST_Force_2D has been
depreciated, and now looks like this:

CREATE OR REPLACE FUNCTION postgis.st_force_2d(geometry)
RETURNS geometry
LANGUAGE sql
IMMUTABLE STRICT
AS $function$ SELECT _postgis_deprecate(‘ST_Force_2d’, ‘ST_Force2D’,
‘2.1.0’);
SELECT ST_Force2D($1);
$function$

The outcome of this is that it is much slower, as shown:

snapmaps=# select count(st_force2d(the_geom)) from water_area;
count

927254
(1 row)

Time: 521.223 ms

snapmaps=# select count(st_force_2d(the_geom)) from water_area;
count

927254
(1 row)

Time: 4139.768 ms

A test (not particularly big) test through Geoserver:

time wget -qO-
“http://localhost:8080/geoserver/psma/wms?LAYERS=terrapsma&STYLES=&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A3857&BBOX=10736535.265029,-5473866.3665441,17796334.870301,-976310.63345588&WIDTH=518&HEIGHT=330”

/dev/null

real 0m2.733s
user 0m0.001s
sys 0m0.005s

Compared with a test when I have removed the call to _postgis_deprecate:

time wget -qO-
“http://localhost:8080/geoserver/psma/wms?LAYERS=terrapsma&STYLES=&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A3857&BBOX=10736535.265029,-5473866.3665441,17796334.870301,-976310.63345588&WIDTH=518&HEIGHT=330”

/dev/null

real 0m2.037s
user 0m0.000s
sys 0m0.006s

All commands were run multiple times to avoid any cache warming issues.

If anything maybe a warning about this needs to be put in the documentation
somewhere?

Cheers,

James Sewell

–
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-ST-Force-2D-vs-ST-Force2D-tp5156337.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

==

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it