[postgis-users] postgis-3.1.0beta2 released

Marco Boeringa marco at boeringa.demon.nl
Fri Dec 11 11:36:45 PST 2020

Hi Paul,

Could you potentially eleborate a bit about #4805, '_ST_SortableHash' 

I looked up the bug tracker issue, and have followed the links, but the 
information provided there is more of a stub, and at least one reference 
suggests mainly a link with BRIN type spatial indexes. On the other 
hand, it suggests a generic performance issue with Postgres and sorting 
very large spatial tables on the geometry column ('ORDER BY geom').

I must admit I actually ran into to something that sounds like the 
latter, where attempting to 'ORDER BY geom' on a non-indexed spatial 
column of a >370M table, resulted in a relatively short (few hours) 
parallel execution of the query with multiple 'parallel workers' visible 
in pgAdmin, which was subsequently followed by > 30 hours of the 'client 
backend' doing single threaded work, with virtually no CPU, RAM or disk 
write activity. Since the database runs on SSD, this was unexpected. 
Even a full sequential scan of the entire table should only have cost a 
fraction of that time. I finally killed the process, not wanting to wait 
any longer.

Marco Boeringa

Op 11-12-2020 om 19:09 schreef Paul Ramsey:
> Since there were a few quick additions to beta1, I have dropped a beta2. The next release will be rc1 and then final, barring any emergency.
>   * Enhancements *
>    - #4814, Do not drop empty geometry components when converting
>             to GEOS (Sandro Santilli)
>    - #4815, Fix GEOS conversion of POINT EMPTY to retain type
>             (Sandro Santilli)
>    - #4813, ST_MakeValid removing NaN coordinates (Sandro Santilli)
>> On Dec 9, 2020, at 4:25 PM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>> This is a beta1 release, for testing and quality assurance, to be followed shortly by a final release. If you're interested in the stability and usability of PostGIS, please take a little time to ensure that you can build and use this release.
>> https://download.osgeo.org/postgis/source/postgis-3.1.0beta1.tar.gz
>> https://postgis.net/2020/12/09/postgis-3.1.0beta1/
>> There have been a few changes since the alpha3 release.
>> * Breaking changes *
>>   - #4214, Deprecated ST_Count(tablename,...), ST_ApproxCount(tablename, ...)
>>            ST_SummaryStats(tablename, ..),
>>            ST_Histogram(tablename, ...), ST_ApproxHistogram(tablename, ...),
>>            ST_Quantile(tablename, ...), ST_ApproxQuantile(tablename, ...) removed.
>>            (Darafei Praliaskouski)
>> * Enhancements *
>>   - #4801, ST_ClusterKMeans supports weights in POINT[Z]M geometries (Darafei Praliaskouski)
>>   - #4804, ST_ReducePrecision (GEOS 3.9+) allows valid precision reduction (Paul Ramsey)
>>   - #4805, _ST_SortableHash exposed to work around parallel soring performance issue
>>            in Postgres. If your table is huge, use ORDER BY _ST_SortableHash(geom)
>>            instead of ORDER BY geom to make parallel sort faster (Darafei Praliaskouski)
>>   - #4625, Correlation statistics now calculated.
>>            Run ANALYZE for BRIN indexes to start kicking in.
>>            (Darafei Praliaskouski)
>>   - Fix axis order issue with urn:ogc:def:crs:EPSG in ST_GeomFromGML()
>>            (Even Roualt)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

More information about the postgis-users mailing list