[postgis-devel] Another Data Point

Wed Jan 21 12:03:09 PST 2009

Oops, the ground shifted under our feet (to quote President Obama),
can't compile postgis-svn against postgresql-cvs right now...

P

On Wed, Jan 21, 2009 at 11:47 AM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> Is that only coming in 8.4? I see a patch from Stephen Frost from 2006
> that does that. I see the code in CVS head too... it's exactly what I
> was thinking: build the result off to the side in a memory context
> instead of passing it around all the time.
>
> I guess a quick'n'dirty test is to build 8.4, load PostGIS and see how
> much the new array_agg outperforms our existing collect(). If the
> answer is "a lot", then steal the new code into our tree, so we can
> support older pgsql versions.
>
> P
>
> On Wed, Jan 21, 2009 at 10:18 AM, David William Bitner
> <david.bitner at gmail.com> wrote:
>> Would any of the 8.4 implementation of the generic array_agg() function be
>> applicable to this?
>>
>> On Wed, Jan 21, 2009 at 11:10 AM, Obe, Regina <robe.dnd at cityofboston.gov>
>> wrote:
>>>
>>> That is not surprising.  I think much of the benefit of my code was
>>> spoon feeding to postgis so sort of acting as a memory manager within
>>> the PostgreSQL context and my arrays were much smaller -- never ended up
>>> with a single array of yeh big as I recall.  Though its been a while
>>> since I have looked.
>>>
>>> Anyrate I'll retest in the next day or so against 3.1.   I would be
>>> curious to see the results.
>>>
>>> Yes accumulation when you get in the 1000s is non-trivial because
>>> PostgreSQL has to resize the array.
>>>
>>> Which is why a construct such as
>>>
>>> ARRAY(SELECT the_geom FROM sometable)
>>>
>>> is faster than
>>>
>>> SELECT ST_Accum(the_geom)
>>> FROM sometable
>>>
>>> -----Original Message-----
>>> From: postgis-devel-bounces at postgis.refractions.net
>>> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paul
>>> Ramsey
>>> Sent: Wednesday, January 21, 2009 12:05 PM
>>> To: PostGIS Development Discussion
>>> Subject: Re: [postgis-devel] Another Data Point
>>>
>>> Interesting! The Shark only samples for about 20 seconds, so I only
>>> got the first 10% or so of the run, and the biggest user in that time
>>> period memcpy'ing in LWGEOM_accum (postgis) and
>>> advance_transition_function (pgsql). So we are spending a non-trivial
>>> amount of time just accumulating the input...
>>>
>>> P
>>>
>>> On Wed, Jan 21, 2009 at 9:00 AM, Paul Ramsey <pramsey at cleverelephant.ca>
>>> wrote:
>>> > The glove is dropped indeed... why speculate when I have the Shark...
>>> > time to see where our time goes...
>>> >
>>> > P
>>> >
>>> > On Wed, Jan 21, 2009 at 8:55 AM, Martin Davis
>>> <mbdavis at refractions.net> wrote:
>>> >> Go for it, Regina!  The glove is dropped...
>>> >>
>>> >> Although I suspect this test may simply be running into the slower
>>> memory
>>> >> performance due to any or all of Postgres, C, GEOS compared to good
>>> ol'
>>> >> Java...  Hard to beat that tuned JVM memory manager!
>>> >>
>>> >> Obe, Regina wrote:
>>> >>>
>>> >>> Ah I think JTS was able to do this in about 40-50 seconds.  And my
>>> >>> version was able to beat out Kevin's nested by a small margin.  I'll
>>> >>> retest on mine.
>>> >>>
>>> >>> So I think we still need work here.
>>> >>>
>>> >>> I'm curious what would happen if I combined the algorithm I wrote
>>> with
>>> >>> the new ST_Union how things would fair.  In theory it should fair
>>> the
>>> >>> same, but I'm not really using Martin's cascaded union algorithm to
>>> the
>>> >>> letter.
>>> >>>
>>> >>>
>>> >>> -----Original Message-----
>>> >>> From: postgis-devel-bounces at postgis.refractions.net
>>> >>> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of
>>> Paul
>>> >>> Ramsey
>>> >>> Sent: Wednesday, January 21, 2009 12:50 AM
>>> >>> To: PostGIS Development Discussion
>>> >>> Cc: lee.keel at uai.com
>>> >>> Subject: [postgis-devel] Another Data Point
>>> >>>
>>> >>> Using the original sample set from Lee Keel,
>>> >>>
>>> >>> With the old style ST_Union, 3 hours, 27 minutes:
>>> >>>
>>> >>> uniontest=# select st_area(st_union(the_geom)) from sample_poly;
>>> >>>      st_area
>>> >>> --------------------
>>> >>>  0.0324039850011104
>>> >>> (1 row)
>>> >>>
>>> >>> Time: 12419261.819 ms
>>> >>>
>>> >>>
>>> >>> With the new cascaded ST_Union, 4 minutes, 30 seconds, or 46 times
>>> >>> faster.
>>> >>>
>>> >>> uniontest=# select st_area(st_union_fast(the_geom)) from
>>> sample_poly;
>>> >>>      st_area
>>> >>> --------------------
>>> >>>  0.0324039850054305
>>> >>> (1 row)
>>> >>>
>>> >>> Time: 271618.181 ms
>>> >>>
>>> >>>
>>> >>> That's one nasty data set.
>>> >>>
>>> >>> P.
>>> >>> _______________________________________________
>>> >>> postgis-devel mailing list
>>> >>> postgis-devel at postgis.refractions.net
>>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>> >>> -----------------------------------------
>>> >>> 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.
>>> >>> _______________________________________________
>>> >>> postgis-devel mailing list
>>> >>> postgis-devel at postgis.refractions.net
>>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>> >>>
>>> >>>
>>> >>
>>> >> --
>>> >> Martin Davis
>>> >> Senior Technical Architect
>>> >> Refractions Research, Inc.
>>> >> (250) 383-3022
>>> >>
>>> >> _______________________________________________
>>> >> postgis-devel mailing list
>>> >> postgis-devel at postgis.refractions.net
>>> >> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>> >>
>>> >
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>
>>
>>
>> --
>> ************************************
>> David William Bitner
>>
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>
>>
>