[postgis-users] Small speed comparison

[Markus Schaber]

Hi, strk,

strk at refractions.net schrieb:

>>But while comparing those results, I found some odd thing (that was
>>present before the current changes, too): I wonder why asEWKB and
>>asBinary both are noticably slower than rendering canonical text
>>(hexEWKB), though.
>
> asEWKB does not exist.

Hmm. My version of
-- $Id: lwpostgis.sql.in,v 1.113 2005/02/08 07:59:07 strk Exp $
contains a definition for

CREATE OR REPLACE FUNCTION AsEWKB(geometry)
	RETURNS BYTEA
	AS '/usr/lib/postgresql/lib//liblwgeom.so.1.0','WKBFromLWGEOM'
	LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict,iscachable);

at lines 1713-1716.

> asEWKT must write a string, so contains
> many sprinf calls that hexEWKB doesn't have.
> asBinary() drops SRID and higher dimensions, thus the slowdown.

I know that asEWKT has to be slower compared to the other versions (and
it is still about 8 times faster compared to PostGIS 0.X).

But I wondered why asBinary() and asEWKB() seem to be noticeably slower
compared to hexWKB output. I now noticed that the problem seems to be
related by function call overhead vs. implicit call of the output funciton:

lwgeom=# select geometry_out(geom) from adminbndy1;
Zeit: 7241,612 ms
Zeit: 7122,037 ms
Zeit: 7135,651 ms

lwgeom=# select geom from adminbndy1;
Zeit: 5638,356 ms
Zeit: 5624,068 ms
Zeit: 5604,034 ms

The second call implicitly calls geometry_out as this is the output
function of the geometry type. So calling a function on a row seems to
add a noticeable overhead.

Maybe I should discuss this on pgsql_performance list?

Just for curiosity, I did some additional tests:

lwgeom=# select noop(geom) from adminbndy1;
Zeit: 7259,772 ms
Zeit: 7230,494 ms
Zeit: 7199,697 ms

So calling noop() seems to introduce roughly the same overhead as
calling geometry_out() explicitly.

lwgeom=# select geometry_send(geom) from adminbndy1;
Zeit: 7002,951 ms
Zeit: 6965,926 ms
Zeit: 6962,790 ms

lwgeom=# select asEWKB(geom) from adminbndy1;
Zeit: 7002,905 ms
Zeit: 6967,374 ms
Zeit: 6958,801 ms

As you see, calling geometry_send and asEWKB are both slower than
canonical text output, but (as expected) faster than calling
geometry_out explicitly.

lwgeom=# select asBinary(geom) from adminbndy1;
Zeit: 7194,490 ms
Zeit: 7180,544 ms
Zeit: 7179,425 ms

And here you see the overhead introduced by force_2d() and SRID
dropping, which seems in the same range as hexEWKB vs EWKB, at least for
my 2-dimensional geometries.

Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios at logi-track.com | www.logi-track.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 256 bytes
Desc: OpenPGP digital signature
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050217/aae8314b/attachment.pgp>