[postgis-tickets] r15281 - Relationship functions not using an index in some cases
Regina Obe
lr at pcorp.us
Thu Jan 5 11:51:01 PST 2017
Author: robe
Date: 2017-01-05 11:51:00 -0800 (Thu, 05 Jan 2017)
New Revision: 15281
Modified:
trunk/postgis/postgis.sql.in
Log:
Relationship functions not using an index in some cases
Set cost back to 1 for functions that return a geometry
A higher cost has the side-effect of making the planner
think when used in conjunction with relate constructs like ST_Intersects
it's cheaper to compute the geometry once rather
than compute twice and break into an index and non-index call.
References #3675 for PostGIS 2.4
Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in 2017-01-05 19:15:21 UTC (rev 15280)
+++ trunk/postgis/postgis.sql.in 2017-01-05 19:51:00 UTC (rev 15281)
@@ -1291,7 +1291,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_2d'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 5;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
-- Deprecation in 2.1.0
@@ -1307,7 +1307,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_3dz'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
-- Deprecation in 2.1.0
@@ -1323,7 +1323,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_3dz'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
-- Deprecation in 2.1.0
@@ -1339,7 +1339,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_3dm'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
-- Deprecation in 2.1.0
@@ -1355,7 +1355,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_4d'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
-- Deprecation in 2.1.0
@@ -1371,7 +1371,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_collection'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 10;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
-- Deprecation in 2.1.0
@@ -1399,7 +1399,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_multi'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 10;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.2.0
CREATE OR REPLACE FUNCTION ST_ForceCurve(geometry)
@@ -1418,42 +1418,42 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_sfs'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 10;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
CREATE OR REPLACE FUNCTION ST_Expand(box3d,float8)
RETURNS box3d
AS 'MODULE_PATHNAME', 'BOX3D_expand'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.3.0
CREATE OR REPLACE FUNCTION ST_Expand(box box3d, dx float8, dy float8, dz float8 DEFAULT 0)
RETURNS box3d
AS 'MODULE_PATHNAME', 'BOX3D_expand'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
CREATE OR REPLACE FUNCTION ST_Expand(geometry,float8)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_expand'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.3.0
CREATE OR REPLACE FUNCTION ST_Expand(geom geometry, dx float8, dy float8, dz float8 DEFAULT 0, dm float8 DEFAULT 0)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_expand'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- PostGIS equivalent function: envelope(geometry)
CREATE OR REPLACE FUNCTION ST_Envelope(geometry)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_envelope'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.2.0
CREATE OR REPLACE FUNCTION ST_BoundingDiagonal(geom geometry, fits boolean DEFAULT false)
@@ -1466,14 +1466,14 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_reverse'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 10;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
CREATE OR REPLACE FUNCTION ST_ForceRHR(geometry)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_force_clockwise_poly'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 15;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.5.0
CREATE OR REPLACE FUNCTION postgis_noop(geometry)
@@ -1487,7 +1487,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_Normalize'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 20; -- 20 as it delegates to GEOS
+ COST 1; -- reset cost, see #3675
-- Deprecation in 1.5.0
CREATE OR REPLACE FUNCTION ST_zmflag(geometry)
@@ -1692,14 +1692,14 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_BuildArea'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.4.0
CREATE OR REPLACE FUNCTION ST_Polygonize (geometry[])
RETURNS geometry
AS 'MODULE_PATHNAME', 'polygonize_garray'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.2
CREATE OR REPLACE FUNCTION ST_ClusterIntersecting(geometry[])
@@ -1724,7 +1724,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'linemerge'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100;
+ COST 1; -- reset cost, see #3675
-----------------------------------------------------------------------------
-- Affine transforms
@@ -1735,7 +1735,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_affine'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
CREATE OR REPLACE FUNCTION ST_Affine(geometry,float8,float8,float8,float8,float8,float8)
@@ -2764,7 +2764,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME','transform'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.3.0
CREATE OR REPLACE FUNCTION ST_Transform(geom geometry, to_proj text)
@@ -3095,28 +3095,28 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_simplify2d'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 50;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.2.0
CREATE OR REPLACE FUNCTION ST_Simplify(geometry, float8, boolean)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_simplify2d'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 50; -- COST guessed from ST_Simplify(geometry, float8)
+ COST 1; -- reset cost, see #3675
-- Availability: 2.2.0
CREATE OR REPLACE FUNCTION ST_SimplifyVW(geometry, float8)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_SetEffectiveArea'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 50; -- COST guessed from ST_Simplify(geometry, float8)
+ COST 1; -- reset cost, see #3675
-- Availability: 2.2.0
CREATE OR REPLACE FUNCTION ST_SetEffectiveArea(geometry, float8 default -1, integer default 1)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_SetEffectiveArea'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 50; -- COST guessed from ST_Simplify(geometry, float8)
+ COST 1; -- reset cost, see #3675
-- ST_SnapToGrid(input, xoff, yoff, xsize, ysize)
-- Availability: 1.2.2
@@ -3124,7 +3124,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_snaptogrid'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- ST_SnapToGrid(input, xsize, ysize) # offsets=0
-- Availability: 1.2.2
@@ -3132,7 +3132,7 @@
RETURNS geometry
AS 'SELECT @extschema at .ST_SnapToGrid($1, 0, 0, $2, $3)'
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- ST_SnapToGrid(input, size) # xsize=ysize=size, offsets=0
-- Availability: 1.2.2
@@ -3147,14 +3147,14 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_snaptogrid_pointoff'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25; -- COST from ST_SnapToGrid(input, xoff, yoff, xsize, ysize)
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
CREATE OR REPLACE FUNCTION ST_Segmentize(geometry, float8)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_segmentize2d'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
---------------------------------------------------------------
-- LRS
@@ -3264,21 +3264,21 @@
RETURNS geometry
AS 'MODULE_PATHNAME','intersection'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100; -- Guessed cost
+ COST 1; -- reset cost, see #3675
-- PostGIS equivalent function: buffer(geometry,float8)
CREATE OR REPLACE FUNCTION ST_Buffer(geometry,float8)
RETURNS geometry
AS 'MODULE_PATHNAME','buffer'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 5000;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.5.0 - requires GEOS-3.2 or higher
CREATE OR REPLACE FUNCTION _ST_Buffer(geometry,float8,cstring)
RETURNS geometry
AS 'MODULE_PATHNAME','buffer'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 5000;
+ COST 1; -- reset cost, see #3675
-- Availability: 1.2.2
CREATE OR REPLACE FUNCTION ST_Buffer(geometry,float8,integer)
@@ -3314,21 +3314,21 @@
RETURNS geometry
AS 'MODULE_PATHNAME','ST_OffsetCurve'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100; -- Guessed cost
+ COST 1; -- reset cost, see #3675
-- Availability: 2.3.0
CREATE OR REPLACE FUNCTION ST_GeneratePoints(area geometry, npoints numeric)
RETURNS geometry
AS 'MODULE_PATHNAME','ST_GeneratePoints'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 400; -- Guessed cost
+ COST 1; -- reset cost, see #3675
-- PostGIS equivalent function: convexhull(geometry)
CREATE OR REPLACE FUNCTION ST_ConvexHull(geometry)
RETURNS geometry
AS 'MODULE_PATHNAME','convexhull'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 250;
+ COST 1; -- reset cost, see #3675
-- Only accepts LINESTRING as parameters.
-- Availability: 1.4.0
@@ -3350,7 +3350,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME','topologypreservesimplify'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 2000;
+ COST 1; -- reset cost, see #3675
-- Requires GEOS >= 3.1.0
-- Availability: 1.4.0
@@ -3474,7 +3474,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_RemoveRepeatedPoints'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
-- Requires GEOS >= 3.5.0
-- Availability: 2.2.0
@@ -3510,7 +3510,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_MakeValid'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 1000;
+ COST 1; -- reset cost, see #3675
-- ST_CleanGeometry(in geometry)
--
@@ -3531,7 +3531,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_CleanGeometry'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 1000;
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
-- ST_Split
@@ -3551,7 +3551,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_Split'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100; -- Guessed cost
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
-- ST_SharedPaths
@@ -3574,7 +3574,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_SharedPaths'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100; -- Guessed cost
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
-- ST_Snap
@@ -3591,7 +3591,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_Snap'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100; -- Guessed cost
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
-- ST_RelateMatch
@@ -3627,7 +3627,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_Node'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100; -- Guessed cost
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
-- ST_DelaunayTriangles
@@ -3651,7 +3651,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_DelaunayTriangles'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25000;
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
@@ -3683,20 +3683,20 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_Voronoi'
LANGUAGE 'c' IMMUTABLE _PARALLEL
- COST 25000; -- Guessed cost
+ COST 1; -- reset cost, see #3675
CREATE OR REPLACE FUNCTION ST_VoronoiPolygons(g1 geometry, tolerance float8 DEFAULT 0.0, extend_to geometry DEFAULT NULL)
RETURNS geometry
AS $$ SELECT @extschema at ._ST_Voronoi(g1, extend_to, tolerance, true) $$
LANGUAGE SQL IMMUTABLE _PARALLEL
- COST 25000; -- Guessed cost
+ COST 1; -- reset cost, see #3675
CREATE OR REPLACE FUNCTION ST_VoronoiLines(g1 geometry, tolerance float8 DEFAULT 0.0, extend_to geometry DEFAULT NULL)
RETURNS geometry
AS $$ SELECT @extschema at ._ST_Voronoi(g1, extend_to, tolerance, false) $$
LANGUAGE SQL IMMUTABLE _PARALLEL
- COST 25000; -- Guessed cost
+ COST 1; -- reset cost, see #3675
--------------------------------------------------------------------------------
-- Aggregates and their supporting functions
@@ -4144,7 +4144,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'centroid'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 100;
+ COST 1; -- reset cost, see #3675
-- Availability: 2.3.0
@@ -4164,7 +4164,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'pointonsurface'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 2500; --this looks suspicious, should not be this high
+ COST 1; -- reset cost, see #3675
-- PostGIS equivalent function: IsSimple(geometry)
CREATE OR REPLACE FUNCTION ST_IsSimple(geometry)
@@ -5065,7 +5065,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_SwapOrdinates'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25; -- COST guessed from ST_FlipCoordinates(geometry)
+ COST 1; -- reset cost, see #3675
-- NOTE: same as ST_SwapOrdinates(geometry, 'xy')
-- but slightly faster in that it doesn't need to parse ordinate
@@ -5074,7 +5074,7 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'ST_FlipCoordinates'
LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
- COST 25;
+ COST 1; -- reset cost, see #3675
--
-- SFSQL 1.1
@@ -5407,19 +5407,19 @@
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_closestpoint3d'
LANGUAGE 'c' IMMUTABLE STRICT
- COST 100;
+ COST 1; -- reset cost, see #3675
CREATE OR REPLACE FUNCTION ST_3DShortestLine(geom1 geometry, geom2 geometry)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_shortestline3d'
LANGUAGE 'c' IMMUTABLE STRICT
- COST 100;
+ COST 1; -- reset cost, see #3675
CREATE OR REPLACE FUNCTION ST_3DLongestLine(geom1 geometry, geom2 geometry)
RETURNS geometry
AS 'MODULE_PATHNAME', 'LWGEOM_longestline3d'
LANGUAGE 'c' IMMUTABLE STRICT
- COST 100;
+ COST 1; -- reset cost, see #3675
CREATE OR REPLACE FUNCTION _ST_3DDWithin(geom1 geometry, geom2 geometry,float8)
RETURNS boolean
More information about the postgis-tickets
mailing list