<HTML dir=ltr><HEAD><TITLE>RE: [postgis-devel] What's the difference betweengeom_accum andbuiltin postgresql array_append</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.5626" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText70205 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Slightly embarassing flaw in my testing. I just realized my windows test was on a test database where I had already altered the behavior of st_geom_accum to use array_append. So it was testing something I ignorantly falsely took for truth and should have tested before.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>The unadulterated comparison is - the below - so array_append still appears to perform better but by much less.</FONT></DIV>
<DIV dir=ltr>--42,781 ms </DIV>
<DIV dir=ltr>SELECT ST_Numgeometries(ST_Collect(the_geom))<BR>FROM usstatebounds;</DIV>
<DIV dir=ltr><BR>-- 36,359 ms<BR>SELECT ST_Numgeometries(ST_Collect2(the_geom))<BR>FROM usstatebounds;</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>Getting back to the embarrasment. I guess this means my assumption that simple SQL functions are inlined in aggregate functions just as it is in any other use is wrong so can't implement st_geom_accum with an sql wrapper without penalty. I'll try to find some other sets of data to test with and cross compare between 8.2 and 8.3 installs.</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><BR>Thanks,</DIV>
<DIV dir=ltr>Regina</DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-devel-bounces@postgis.refractions.net on behalf of Obe, Regina<BR><B>Sent:</B> Wed 9/3/2008 12:12 AM<BR><B>To:</B> PostGIS Development Discussion<BR><B>Subject:</B> RE: [postgis-devel] What's the differencebetweengeom_accum andbuiltin postgresql array_append<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Mark,<BR><BR>This is interesting. I've been doing all my tests on Union and since the bottleneck has always been ST_Union I wasn't seeing much of a difference in timings, but to just test the raw speed of array_append vs. st_geom_accum, I decided doing a collect compare would be better since collect pretty much just appends and does a simple collect. <BR><BR>st_geom_accum actually performs worse most of the time for largish geometries. Note I also put in the ST_NumGeometries test to limit the latency of network transfer.<BR><BR>My ST_Collect2 looks like this<BR>CREATE AGGREGATE st_collect2(geometry) (<BR> SFUNC=array_append,<BR> STYPE=geometry[],<BR> FINALFUNC=st_collect_garray<BR>);<BR><BR>On my standard usstatebounds sample set -<BR>POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" (Windows 2003)<BR>"PostgreSQL 8.2.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"<BR><BR>--55,781 ms (result: 2895 )<BR>SELECT ST_Numgeometries(ST_Collect(the_geom))<BR>FROM usstatebounds<BR><BR>-- 36,359 ms (result: 2895)<BR>SELECT ST_Numgeometries(ST_Collect2(the_geom))<BR>FROM usstatebounds<BR><BR>-- Max number of points of any single geometry is 70,238 points, avg - 140.5<BR>SELECT Max(ST_NPoints(the_geom)), Avg(ST_NPoints(the_geom))<BR>FROM<BR>usstatebounds<BR><BR>(unfortunately I don't have a similar data set on my Linux box) - testing with a smaller number of records with largish geoms which I can do a comparable test). <BR>Tests on linux ("PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)")<BR>This test on linux with a 8.3 gives same timings POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS"<BR>--812 ms (result - 56)<BR>SELECT ST_Numgeometries(ST_Collect(the_geom))<BR>FROM fe_2007_us_state<BR><BR>-- 812 ms (result - 56)<BR>SELECT ST_Numgeometries(ST_Collect2(the_geom))<BR>FROM fe_2007_us_state<BR><BR>-- max 45,056, avg 12,130<BR>SELECT Max(ST_NPoints(the_geom)), Avg(ST_NPoints(the_geom))<BR>FROM<BR>fe_2007_us_state<BR><BR><BR>--437 ms (result - 209)<BR>SELECT ST_Numgeometries(ST_Collect(the_geom))<BR>FROM country_boundaries<BR><BR>-- 312 ms (result - 209)<BR>SELECT ST_Numgeometries(ST_Collect2(the_geom))<BR>FROM country_boundaries<BR><BR>-- max 12,125, avg 455<BR>SELECT Max(ST_NPoints(the_geom)), Avg(ST_NPoints(the_geom))<BR>FROM<BR>country_boundaries<BR><BR>Thanks,<BR>Regina<BR>-----Original Message-----<BR>From: postgis-devel-bounces@postgis.refractions.net on behalf of Mark Cave-Ayland<BR>Sent: Tue 9/2/2008 6:23 PM<BR>To: PostGIS Development Discussion<BR>Subject: Re: [postgis-devel] What's the difference betweengeom_accum andbuiltin postgresql array_append<BR><BR>Obe, Regina wrote:<BR>><BR>> Getting back to this topic. Recall I said there was a situation where<BR>> LWGEOM_accum worked where array_append didn't. I can't replicate it so<BR>> I suspect it was an earlier error in my indexing efforts that caused<BR>> this issue. I am also not seeing this LWGEOM_accum function used<BR>> directly in any of the postgis code base.<BR>><BR>> So here is my half-assed suggestion since no one seems to know why we<BR>> should maintain the LWGEOM_accum function instead of just using built-in<BR>> PostgreSQL array_append function.<BR>><BR>> Simply get rid of LWGEOM_accum from our code base (or deprecate it) --<BR>> change<BR>><BR>> /**CREATE OR REPLACE FUNCTION st_geom_accum(geometry[], geometry)<BR>> RETURNS geometry[] AS<BR>> '$libdir/liblwgeom.dll', 'LWGEOM_accum'<BR>> LANGUAGE 'c' IMMUTABLE;<BR>> ***/<BR>><BR>> To:<BR>> CREATE OR REPLACE FUNCTION st_geom_accum(geometry[], geometry)<BR>> RETURNS geometry[] AS<BR>> $$ SELECT array_append($1,$2); $$<BR>> LANGUAGE 'sql' IMMUTABLE;<BR>><BR>><BR>> And that's it. In tests I've done doing above, I don't see a definitive<BR>> speed difference and all aggs we have seem to have this function for<BR>> collecting geometries.<BR>><BR>> I would have suggested replacing our use in aggregates directly with<BR>> array_append, but thought it would be better to keep this buffer for 3<BR>> reasons<BR>> 1) We don't have a CREATE OR REPLACE AGGREGATE which makes changing aggs<BR>> without a drop reload tricky. Changing this function will auto-magically<BR>> correct the aggs. Granted 1.4 people should be doing a full reload anyway.<BR>><BR>> 2) Don't know if anyone is relying on the st_geom_accum function so we<BR>> probably should keep it around anyway.<BR>><BR>> 3) In case we are wrong. It won't be a major undertaking to correct the<BR>> mistake.<BR>><BR>> Any thoughts, concerns?<BR>><BR>> Thanks,<BR>> Regina<BR><BR><BR>Hi Regina,<BR><BR>At a quick glance, I'd say that the LWGEOM_accum function forcibly<BR>de-TOASTs the input geometries when building the array so it may be that<BR>you will only see a time difference working very large geometries<BR>(rather than large numbers of smaller geometries). Does this make any<BR>difference in your tests?<BR><BR><BR>ATB,<BR><BR>Mark.<BR><BR>--<BR>Mark Cave-Ayland<BR>Sirius Corporation - The Open Source Experts<BR><A href="http://www.siriusit.co.uk/">http://www.siriusit.co.uk</A><BR>T: +44 870 608 0063<BR>_______________________________________________<BR>postgis-devel mailing list<BR>postgis-devel@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR><BR><BR><BR><BR></FONT></P>
<P>
<HR SIZE=1>
<P></P>
<P><STRONG>The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. </STRONG></P>
<P>
<HR SIZE=1>
<P></P>
<P><STRONG><FONT color=#339900 size=2>Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </P>
<P></FONT></STRONG></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>