[postgis-users] Small speed comparison

[strk at refractions.net]

On Thu, Feb 17, 2005 at 04:09:45PM +0100, Markus Schaber wrote:
> 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)

Oops.. sorry, you're right, its our only way to get a binary 
extended form actually ;)

It seems you've found the reason, as they really should be comparable
in speed, except an additional memcpy done by AsEWKB() which is
not performed by the geometry_out().
The memcpy is needed because geometry_out() returns a NULL-terminated
string (as the unparser produces) while AsEWKB has to construct
a varlena type (byta) to be returned.
The same memcpy happens in geometry::text cast, where HEXWKB is
memcopyied to be returned as TEXT instead of CSTRING.

As for the function call overhead I think that's due to different
caching strategies between directly-type-related functions (in/out/send/recv)
and general functions... but you should really use the pgsql mailing
lists...

--strk;


> 	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



> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users