[postgis-devel] Wobbles

Wed Apr 28 14:02:16 PDT 2021

Another option to stabilize the scans to try:

 set synchronize_seqscans to off;

will discourage Postgres to start from the middle of the table at expense
of more IO.

On Thu, Apr 29, 2021 at 12:00 AM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> That seems more stable.
>
> > On Apr 28, 2021, at 1:56 PM, Darafei Komяpa Praliaskouski <me at komzpa.net>
> wrote:
> >
> > since it's a sum of doubles and it's order-dependant, can you try
> >
> > select sum(st_area(geom::geography) order by st_area(geom::geography))
> from va;
> >
> > On Wed, Apr 28, 2021 at 11:50 PM Paul Ramsey <pramsey at cleverelephant.ca>
> wrote:
> > Yeah, looks like it wobbles. Two runs, no change in anything at all,
> just two SQL statements one after another
> >
> > postgis=# select sum(st_area(geom::geography)) from va;
> >         sum
> > --------------------
> >  1070124793748.3967
> > (1 row)
> >
> > Time: 1345.384 ms (00:01.345)
> > postgis=# select sum(st_area(geom::geography)) from va;
> >         sum
> > --------------------
> >  1070124793748.3954
> > (1 row)
> >
> > Time: 1352.277 ms (00:01.352)
> >
> >
> >
> > > On Apr 28, 2021, at 1:34 PM, Regina Obe <lr at pcorp.us> wrote:
> > >
> > > I know the difference is pretty small, but how did you manage to get
> two
> > > different answers for area with just swap of GEOS or is that a typo?
> > >
> > > In 3.8.3 you have  1070124793748.3967
> > >
> > > In 3.9.2dev you have 1070124793748.3959
> > >
> > > Is that 2 digit different just voodoo powder.
> > > I thought given  the same exact machine, same version of PostGIS  and
> same
> > > version of Proj we could count on no randomness of answers.
> > >
> > > Is the answer the same each and every time you run it for a given
> setup or
> > > does it wabble a bit?
> > >
> > >> -----Original Message-----
> > >> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> > >> Behalf Of Paul Ramsey
> > >> Sent: Tuesday, April 27, 2021 4:47 PM
> > >> To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> > >> Subject: Re: [postgis-devel] Much slower processing on GEOS 3.9.0
> versus
> > >> 3.8.0 for geodesic area calculation
> > >>
> > >> Just to stick a nail in the GEOS-version-affects-geography-area
> > > conversation,
> > >> I took the time to set up two builds (GEOS 3.8 and GEOS 3.9) and
> swapped
> > >> between them without changing anything else about the system.
> > >>
> > >> postgis=# select postgis_full_version();
> > >>
> > >> postgis_full_version
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------------------
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------------------
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------------------
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ---
> > >> POSTGIS="3.2.0dev 3.1.0rc1-162-g46efb9f2d" [EXTENSION] PGSQL="130"
> > >> GEOS="3.8.3-CAPI-1.13.4" PROJ="8.0.0" GDAL="GDAL 3.2.0, released
> > >> 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3"
> > >> WAGYU="0.5.0 (Internal)"
> > >>
> > >> postgis=# select sum(st_area(geom::geography)) from va;
> > >>        sum
> > >> --------------------
> > >> 1070124793748.3967
> > >> (1 row)
> > >>
> > >> Time: 1449.551 ms (00:01.450)
> > >>
> > >>
> > >> Then stop the server, change the LD_LIBRARY_PATH to the 3.9 install
> > > location
> > >> and restart.
> > >>
> > >> postgis=# select postgis_full_version();
> > >>
> > >> postgis_full_version
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------------------
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------------------
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------------------
> > >>
> > >
> ----------------------------------------------------------------------------
> > > ------
> > >> POSTGIS="3.2.0dev 3.1.0rc1-162-g46efb9f2d" [EXTENSION] PGSQL="130"
> > >> GEOS="3.9.2dev-CAPI-1.14.2" PROJ="8.0.0" GDAL="GDAL 3.2.0, released
> > >> 2020/10/26" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.3.3"
> > >> WAGYU="0.5.0 (Internal)"
> > >>
> > >> postgis=# select sum(st_area(geom::geography)) from va;
> > >>        sum
> > >> --------------------
> > >> 1070124793748.3959
> > >> (1 row)
> > >>
> > >> Time: 1439.969 ms (00:01.440)
> > >>
> > >>
> > >>
> > >> There's no effect to be seen when GEOS version is the only variable in
> > > play.
> > >>
> > >> P
> > >> _______________________________________________
> > >> postgis-devel mailing list
> > >> postgis-devel at lists.osgeo.org
> > >> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> > >
> > > _______________________________________________
> > > postgis-devel mailing list
> > > postgis-devel at lists.osgeo.org
> > > https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >
> >
> > --
> > Darafei "Komяpa" Praliaskouski
> > OSM BY Team - http://openstreetmap.by/
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>

-- 
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20210429/9c06a012/attachment.html>