[postgis-tickets] [SCM] PostGIS branch master updated. 3.1.0alpha3-9-g8b13c3e

[git at osgeo.org]

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".

The branch, master has been updated
       via  8b13c3e2f8366d902dbf516ec17de09ae84361f4 (commit)
      from  87812cbd6d1e1348de83b82ee3980f6ef19083a1 (commit)

Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.

- Log -----------------------------------------------------------------
commit 8b13c3e2f8366d902dbf516ec17de09ae84361f4
Author: Darafei Praliaskouski <me at komzpa.net>
Date:   Sun Nov 29 01:40:15 2020 +0300

    Expose _ST_SortableHash
    
    Intentionally undocumented. Use as a work around for slow
    ORDER BY geom in parallel plans until Postgres gets better.
    
    Closes #4805

diff --git a/NEWS b/NEWS
index 866b944..165f568 100644
--- a/NEWS
+++ b/NEWS
@@ -5,6 +5,9 @@ Only tickets not included in 3.1.0alpha3
  * New features*
   - #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)
 
 
 PostGIS 3.1.0alpha3
diff --git a/postgis/lwgeom_functions_basic.c b/postgis/lwgeom_functions_basic.c
index c5b9e30..20c9aed 100644
--- a/postgis/lwgeom_functions_basic.c
+++ b/postgis/lwgeom_functions_basic.c
@@ -2710,6 +2710,14 @@ Datum ST_GeoHash(PG_FUNCTION_ARGS)
 	PG_RETURN_NULL();
 }
 
+PG_FUNCTION_INFO_V1(_ST_SortableHash);
+Datum _ST_SortableHash(PG_FUNCTION_ARGS)
+{
+	if (PG_ARGISNULL(0))
+		PG_RETURN_NULL();
+	PG_RETURN_INT64(gserialized_get_sortable_hash(PG_GETARG_GSERIALIZED_P(0)));
+}
+
 PG_FUNCTION_INFO_V1(ST_CollectionExtract);
 Datum ST_CollectionExtract(PG_FUNCTION_ARGS)
 {
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 5310d48..9e49473 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -4973,6 +4973,14 @@ CREATE OR REPLACE FUNCTION ST_GeoHash(geom geometry, maxchars int4 DEFAULT 0)
 	LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE
 	_COST_LOW;
 
+--
+-- Availability 3.1.0
+CREATE OR REPLACE FUNCTION _ST_SortableHash(geom geometry)
+	RETURNS bigint
+	AS 'MODULE_PATHNAME', '_ST_SortableHash'
+	LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE
+	_COST_LOW;
+
 -----------------------------------------------------------------------
 -- GeoHash input
 -- Availability: 2.0.?
diff --git a/regress/core/regress_index.sql b/regress/core/regress_index.sql
index df7768a..d3dd0e5 100644
--- a/regress/core/regress_index.sql
+++ b/regress/core/regress_index.sql
@@ -111,3 +111,6 @@ DROP FUNCTION qnodes(text);
 set enable_indexscan = on;
 set enable_bitmapscan = on;
 set enable_seqscan = on;
+
+-- _ST_SortableHash is a work around Postgres parallel sort requiring recalculation of abbreviated keys.
+select '_st_sortablehash', _ST_SortableHash('POINT(0 0)'), _ST_SortableHash('SRID=4326;POINT(0 0)'), _ST_SortableHash('SRID=3857;POINT(0 0)');
diff --git a/regress/core/regress_index_expected b/regress/core/regress_index_expected
index ae75d05..657981f 100644
--- a/regress/core/regress_index_expected
+++ b/regress/core/regress_index_expected
@@ -14,3 +14,4 @@ expr|3+=5:true
 expr|924+=60:true
 expr|12621+=500:true
 expr|50000+=600:true
+_st_sortablehash|0|768602608280535040|768602608280535040

-----------------------------------------------------------------------

Summary of changes:
 NEWS                                | 3 +++
 postgis/lwgeom_functions_basic.c    | 8 ++++++++
 postgis/postgis.sql.in              | 8 ++++++++
 regress/core/regress_index.sql      | 3 +++
 regress/core/regress_index_expected | 1 +
 5 files changed, 23 insertions(+)


hooks/post-receive
-- 
PostGIS