[postgis-users] Small speed comparison

[Markus Schaber]

Hi,

To estimate the speed gains we get when updating to PostGIS 1.0, I did
some small benchmarks, working on the same table containing 83 large
geometries that you know from the query optimizer vs. TOAST thread. They
consist of running psql with "\timing" and "\o /dev/null" before typing
the queries below.

First, using PostGIS 0.8.1:

logigis=# select geom from adminbndy1;
Zeit: 270145,674 ms
logigis=# select geom from adminbndy1;
Zeit: 260518,695 ms
logigis=# select asBinary(geom) from adminbndy1;
Zeit: 8990,028 ms
logigis=# select asBinary(geom) from adminbndy1;
Zeit: 8111,368 ms
logigis=# select asText(geom) from adminbndy1;
Zeit: 263196,287 ms

Second, using PostGIS 1.0.0 CVS (but some weeks old):

lwgeom=# select geom from adminbndy1;
Zeit: 8629,822 ms
lwgeom=# select geom from adminbndy1;
Zeit: 6401,693 ms
lwgeom=# select asBinary(geom) from adminbndy1;
Zeit: 11648,607 ms
lwgeom=# select asBinary(geom) from adminbndy1;
Zeit: 7845,229 ms
lwgeom=# select asEWKB(geom) from adminbndy1;
Zeit: 7824,009 ms
lwgeom=# select asEWKB(geom) from adminbndy1;
Zeit: 7308,423 ms
lwgeom=# select asText(geom) from adminbndy1;
Zeit: 33655,132 ms
lwgeom=# select asText(geom) from adminbndy1;
Zeit: 33154,569 ms
lwgeom=# select asEWKT(geom) from adminbndy1;
Zeit: 34402,441 ms
lwgeom=# select asEWKB(geom)::bytea from adminbndy1;
Zeit: 7444,995 ms

So the canonical text rep now is about 40 times faster, and even WKT
output is nearly 8 times faster.

Good work!

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