[postgis-devel] Another Data Point

[Paul Ramsey]

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
>
>