[postgis-tickets] r15029 - Add support for BRIN indexes - missed new files on last commit
Regina Obe
lr at pcorp.us
Sat Jul 30 21:34:16 PDT 2016
Author: robe
Date: 2016-07-30 21:34:16 -0700 (Sat, 30 Jul 2016)
New Revision: 15029
Added:
trunk/postgis/brin_2d.c
trunk/postgis/brin_common.c
trunk/postgis/brin_nd.c
trunk/postgis/postgis_brin.h
trunk/regress/regress_brin_index.sql
trunk/regress/regress_brin_index_3d.sql
trunk/regress/regress_brin_index_3d_expected
trunk/regress/regress_brin_index_expected
trunk/regress/regress_brin_index_geography.sql
trunk/regress/regress_brin_index_geography_expected
trunk/regress/regress_lots_of_3dpoints.sql
trunk/regress/regress_lots_of_geographies.sql
Log:
Add support for BRIN indexes - missed new files on last commit
Closes #3591
Closes https://github.com/postgis/postgis/pull/106
Added: trunk/postgis/brin_2d.c
===================================================================
--- trunk/postgis/brin_2d.c (rev 0)
+++ trunk/postgis/brin_2d.c 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,88 @@
+#include "postgis_brin.h"
+
+/*
+ * As we index geometries but store either a BOX2DF or GIDX according to the
+ * operator class, we need to overload the original brin_inclusion_add_value()
+ * function to be able to do this. Other original mandatory support functions
+ * doesn't need to be overloaded.
+ *
+ * The previous limitation might be lifted, but we also eliminate some overhead
+ * by doing it this way, namely calling different functions through the
+ * FunctionCallInvoke machinery for each heap tuple.
+ */
+
+PG_FUNCTION_INFO_V1(geom2d_brin_inclusion_add_value);
+Datum
+geom2d_brin_inclusion_add_value(PG_FUNCTION_ARGS)
+{
+ BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
+ Datum newval = PG_GETARG_DATUM(2);
+ bool isnull = PG_GETARG_BOOL(3);
+ BOX2DF box_geom, *box_key;
+
+ /*
+ * If the new value is null, we record that we saw it if it's the first
+ * one; otherwise, there's nothing to do.
+ */
+ if (isnull)
+ {
+ if (column->bv_hasnulls)
+ PG_RETURN_BOOL(false);
+
+ column->bv_hasnulls = true;
+ PG_RETURN_BOOL(true);
+ }
+
+ /*
+ * check other cases where it is not possible to retrieve a box
+ */
+ if (gserialized_datum_get_box2df_p(newval, &box_geom) == LW_FAILURE)
+ {
+ /*
+ * Empty entries have to be supported in the opclass: test the passed
+ * new value for emptiness; if it returns true, we need to set the
+ * "contains empty" flag in the element (unless already set).
+ */
+ if (is_gserialized_from_datum_empty(newval)) {
+ if (!DatumGetBool(column->bv_values[INCLUSION_CONTAINS_EMPTY]))
+ {
+ column->bv_values[INCLUSION_CONTAINS_EMPTY] = BoolGetDatum(true);
+ PG_RETURN_BOOL(true);
+ }
+
+ PG_RETURN_BOOL(false);
+ } else
+ {
+ /*
+ * in case the entry is not empty and it is not possible to
+ * retrieve a box, raise an error
+ */
+ elog(ERROR, "Error while extracting the box2df from the geom");
+ }
+ }
+
+ /* if the recorded value is null, we just need to store the box2df */
+ if (column->bv_allnulls)
+ {
+ column->bv_values[INCLUSION_UNION] = datumCopy((Datum) &box_geom, false,
+ sizeof(BOX2DF));
+ column->bv_values[INCLUSION_UNMERGEABLE] = BoolGetDatum(false);
+ column->bv_values[INCLUSION_CONTAINS_EMPTY] = BoolGetDatum(false);
+ column->bv_allnulls = false;
+ PG_RETURN_BOOL(true);
+ }
+
+ /*
+ * Otherwise, we need to enlarge the stored box2df to make it contains the
+ * current geometry
+ */
+ box_key = (BOX2DF *) column->bv_values[INCLUSION_UNION];
+
+ /* enlarge box2df */
+ box_key->xmin = Min(box_key->xmin, box_geom.xmin);
+ box_key->xmax = Max(box_key->xmax, box_geom.xmax);
+ box_key->ymin = Min(box_key->ymin, box_geom.ymin);
+ box_key->ymax = Max(box_key->ymax, box_geom.ymax);
+
+ PG_RETURN_BOOL(false);
+}
Added: trunk/postgis/brin_common.c
===================================================================
--- trunk/postgis/brin_common.c (rev 0)
+++ trunk/postgis/brin_common.c 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,12 @@
+#include "postgis_brin.h"
+
+bool
+is_gserialized_from_datum_empty(Datum the_datum)
+{
+ GSERIALIZED *geom = (GSERIALIZED*)PG_DETOAST_DATUM(the_datum);
+
+ if (gserialized_is_empty(geom) == LW_TRUE)
+ return true;
+ else
+ return false;
+}
Added: trunk/postgis/brin_nd.c
===================================================================
--- trunk/postgis/brin_nd.c (rev 0)
+++ trunk/postgis/brin_nd.c 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,168 @@
+#include "postgis_brin.h"
+
+/*
+ * As we index geometries but store either a BOX2DF or GIDX according to the
+ * operator class, we need to overload the original brin_inclusion_add_value()
+ * function to be able to do this. Other original mandatory support functions
+ * doesn't need to be overloaded.
+ *
+ * The previous limitation might be lifted, but we also eliminate some overhead
+ * by doing it this way, namely calling different functions through the
+ * FunctionCallInvoke machinery for each heap tuple.
+ */
+
+Datum gidx_brin_inclusion_add_value(BrinDesc *bdesc, BrinValues *column, Datum
+ newval, bool isnull, int dims_wanted);
+
+/*
+ * As for the GiST case, geographies are converted into GIDX before
+ * they are added to the other index keys
+ */
+PG_FUNCTION_INFO_V1(geog_brin_inclusion_add_value);
+Datum
+geog_brin_inclusion_add_value(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
+ Datum newval = PG_GETARG_DATUM(2);
+ bool isnull = PG_GETARG_BOOL(3);
+
+ PG_RETURN_DATUM(gidx_brin_inclusion_add_value(bdesc, column, newval, isnull,
+ 2));
+}
+
+
+PG_FUNCTION_INFO_V1(geom3d_brin_inclusion_add_value);
+Datum
+geom3d_brin_inclusion_add_value(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
+ Datum newval = PG_GETARG_DATUM(2);
+ bool isnull = PG_GETARG_BOOL(3);
+
+ PG_RETURN_DATUM(gidx_brin_inclusion_add_value(bdesc, column, newval, isnull,
+ 3));
+}
+
+PG_FUNCTION_INFO_V1(geom4d_brin_inclusion_add_value);
+Datum
+geom4d_brin_inclusion_add_value(PG_FUNCTION_ARGS)
+{
+ BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0);
+ BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1);
+ Datum newval = PG_GETARG_DATUM(2);
+ bool isnull = PG_GETARG_BOOL(3);
+
+ PG_RETURN_DATUM(gidx_brin_inclusion_add_value(bdesc, column, newval, isnull,
+ 4));
+}
+
+Datum
+gidx_brin_inclusion_add_value(BrinDesc *bdesc, BrinValues *column, Datum newval,
+ bool isnull, int dims_wanted)
+{
+ char gboxmem[GIDX_MAX_SIZE];
+ GIDX *gidx_geom, *gidx_key;
+ int dims_geom, i;
+
+ Assert(dims_wanted <= GIDX_MAX_DIM);
+
+ /*
+ * If the new value is null, we record that we saw it if it's the first
+ * one; otherwise, there's nothing to do.
+ */
+ if (isnull)
+ {
+ if (column->bv_hasnulls)
+ PG_RETURN_BOOL(false);
+
+ column->bv_hasnulls = true;
+ PG_RETURN_BOOL(true);
+ }
+
+ /* create a new GIDX in stack memory, maximum dimensions */
+ gidx_geom = (GIDX *) gboxmem;
+
+ /*
+ * check other cases where it is not possible to retrieve a box
+ */
+ if (gserialized_datum_get_gidx_p(newval, gidx_geom) == LW_FAILURE)
+ {
+ /*
+ * Empty entries have to be supported in the opclass: test the passed
+ * new value for emptiness; if it returns true, we need to set the
+ * "contains empty" flag in the element (unless already set).
+ */
+ if (is_gserialized_from_datum_empty(newval))
+ {
+ if (!DatumGetBool(column->bv_values[INCLUSION_CONTAINS_EMPTY]))
+ {
+ column->bv_values[INCLUSION_CONTAINS_EMPTY] = BoolGetDatum(true);
+ PG_RETURN_BOOL(true);
+ }
+
+ PG_RETURN_BOOL(false);
+ } else
+ {
+ /*
+ * in case the entry is not empty and it is not possible to
+ * retrieve a box, raise an error
+ */
+ elog(ERROR, "Error while extracting the gidx from the geom");
+ }
+ }
+
+ /* Get the actual dimension of the geometry */
+ dims_geom = GIDX_NDIMS(gidx_geom);
+
+ /* if the recorded value is null, we just need to store the GIDX */
+ if (column->bv_allnulls)
+ {
+ /*
+ * We have to make sure we store a GIDX of wanted dimension. If the
+ * original geometry has less dimensions, we zero them in the GIDX. If
+ * the original geometry has more, we ignore them.
+ */
+ if (dims_geom != dims_wanted)
+ {
+ /*
+ * This is safe to either enlarge or diminush the varsize because
+ * the GIDX was created with the maximum number of dimension a GIDX
+ * can contain
+ */
+ SET_VARSIZE(gidx_geom, VARHDRSZ + dims_wanted * 2 * sizeof(float));
+ }
+ /* zero the extra dimensions if we enlarged the GIDX */
+ for (i = dims_geom; i < dims_wanted; i++)
+ {
+ GIDX_SET_MIN(gidx_geom, i, 0);
+ GIDX_SET_MAX(gidx_geom, i, 0);
+ }
+
+ column->bv_values[INCLUSION_UNION] = datumCopy((Datum) gidx_geom, false,
+ GIDX_SIZE(dims_wanted));
+ column->bv_values[INCLUSION_UNMERGEABLE] = BoolGetDatum(false);
+ column->bv_values[INCLUSION_CONTAINS_EMPTY] = BoolGetDatum(false);
+ column->bv_allnulls = false;
+ PG_RETURN_BOOL(true);
+ }
+
+ gidx_key = (GIDX *) column->bv_values[INCLUSION_UNION];
+
+ /*
+ * As we always store a GIDX of the wanted number of dimensions, we just
+ * need adjust min and max
+ */
+ for ( i = 0; i < dims_wanted; i++ )
+ {
+ /* Adjust minimums */
+ GIDX_SET_MIN(gidx_key, i,
+ Min(GIDX_GET_MIN(gidx_key,i),GIDX_GET_MIN(gidx_geom,i)));
+ /* Adjust maximums */
+ GIDX_SET_MAX(gidx_key, i,
+ Max(GIDX_GET_MAX(gidx_key,i),GIDX_GET_MAX(gidx_geom,i)));
+ }
+
+ PG_RETURN_BOOL(false);
+}
Added: trunk/postgis/postgis_brin.h
===================================================================
--- trunk/postgis/postgis_brin.h (rev 0)
+++ trunk/postgis/postgis_brin.h 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,23 @@
+#include "postgres.h"
+#include "fmgr.h"
+
+#include "../postgis_config.h"
+
+/*#define POSTGIS_DEBUG_LEVEL 4*/
+
+#include "liblwgeom.h" /* For standard geometry types. */
+#include "lwgeom_pg.h" /* For debugging macros. */
+
+#include <assert.h>
+#include <math.h>
+#include <float.h>
+#include <string.h>
+#include "access/brin_tuple.h"
+#include "utils/datum.h"
+#include "gserialized_gist.h"
+
+#define INCLUSION_UNION 0
+#define INCLUSION_UNMERGEABLE 1
+#define INCLUSION_CONTAINS_EMPTY 2
+
+bool is_gserialized_from_datum_empty(Datum the_datum);
Added: trunk/regress/regress_brin_index.sql
===================================================================
--- trunk/regress/regress_brin_index.sql (rev 0)
+++ trunk/regress/regress_brin_index.sql 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,105 @@
+--- build a larger database
+\i regress_lots_of_points.sql
+
+--- test some of the searching capabilities
+
+CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
+LANGUAGE 'plpgsql' AS
+$$
+DECLARE
+ exp TEXT;
+ mat TEXT[];
+ ret TEXT[];
+BEGIN
+ FOR exp IN EXECUTE 'EXPLAIN ' || q
+ LOOP
+ --RAISE NOTICE 'EXP: %', exp;
+ mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan)');
+ --RAISE NOTICE 'MAT: %', mat;
+ IF mat IS NOT NULL THEN
+ ret := array_append(ret, mat[1]);
+ END IF;
+ --RAISE NOTICE 'RET: %', ret;
+ END LOOP;
+ RETURN array_to_string(ret,',');
+END;
+$$;
+
+-- BRIN indexes
+
+-- 2D
+CREATE INDEX brin_2d on test using brin (the_geom);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('select * from test where the_geom && ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom && 'BOX(125 125,135 135)'::box2d order by num;
+
+SELECT 'scan_seq', qnodes('select * from test where ST_MakePoint(0,0) ~ the_geom');
+ select num,ST_astext(the_geom) from test where 'BOX(125 125,135 135)'::box2d ~ the_geom order by num;
+
+SELECT 'scan_seq', qnodes('select * from test where the_geom @ ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom @ 'BOX(125 125,135 135)'::box2d order by num;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom && ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom && 'BOX(125 125,135 135)'::box2d order by num;
+
+SELECT 'scan_idx', qnodes('select * from test where ST_MakePoint(0,0) ~ the_geom');
+ select num,ST_astext(the_geom) from test where 'BOX(125 125,135 135)'::box2d ~ the_geom order by num;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom @ ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom @ 'BOX(125 125,135 135)'::box2d order by num;
+
+DROP INDEX brin_2d;
+
+-- 3D
+CREATE INDEX brin_3d on test using brin (the_geom brin_geometry_inclusion_ops_3d);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('select * from test where the_geom &&& ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(125 125,135 135)'::box3d order by num;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom &&& ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(125 125,135 135)'::box3d order by num;
+
+DROP INDEX brin_3d;
+
+-- 4D
+CREATE INDEX brin_4d on test using brin (the_geom brin_geometry_inclusion_ops_4d);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('select * from test where the_geom &&& ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(125 125,135 135)'::box3d order by num;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom &&& ST_MakePoint(0,0)');
+ select num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(125 125,135 135)'::box3d order by num;
+
+DROP INDEX brin_4d;
+
+-- cleanup
+DROP TABLE test;
+DROP FUNCTION qnodes(text);
+
+set enable_indexscan = on;
+set enable_bitmapscan = on;
+set enable_seqscan = on;
Added: trunk/regress/regress_brin_index_3d.sql
===================================================================
--- trunk/regress/regress_brin_index_3d.sql (rev 0)
+++ trunk/regress/regress_brin_index_3d.sql 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,123 @@
+--- build a larger database
+\i regress_lots_of_3dpoints.sql
+
+--- test some of the searching capabilities
+
+CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
+LANGUAGE 'plpgsql' AS
+$$
+DECLARE
+ exp TEXT;
+ mat TEXT[];
+ ret TEXT[];
+BEGIN
+ FOR exp IN EXECUTE 'EXPLAIN ' || q
+ LOOP
+ --RAISE NOTICE 'EXP: %', exp;
+ mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan)');
+ --RAISE NOTICE 'MAT: %', mat;
+ IF mat IS NOT NULL THEN
+ ret := array_append(ret, mat[1]);
+ END IF;
+ --RAISE NOTICE 'RET: %', ret;
+ END LOOP;
+ RETURN array_to_string(ret,',');
+END;
+$$;
+
+-- BRIN indexes
+
+-- 2D
+CREATE INDEX brin_2d on test using brin (the_geom) WITH (pages_per_range = 10);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom && ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom && 'BOX(125 125,126 126)'::box2d order by num;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE ST_MakePoint(0,0) ~ the_geom');
+ SELECT num, ST_astext(the_geom) FROM test WHERE 'BOX(125 125,126 126)'::box2d ~ the_geom order by num;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom @ ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom @ 'BOX(125 125,126 126)'::box2d order by num;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geom IS NULL;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geom && ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom && 'BOX(125 125,126 126)'::box2d order by num;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE ST_MakePoint(0,0) ~ the_geom');
+ SELECT num, ST_astext(the_geom) FROM test WHERE 'BOX(125 125,126 126)'::box2d ~ the_geom order by num;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geom @ ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom @ 'BOX(125 125,126 126)'::box2d order by num;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geom IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geom IS NULL;
+
+DROP INDEX brin_2d;
+
+-- 3D
+CREATE INDEX brin_3d on test using brin (the_geom brin_geometry_inclusion_ops_3d) WITH (pages_per_range = 10);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom &&& ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom &&& 'BOX3D(125 125 125,126 126 126)'::box3d order by num;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geom IS NULL;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geom &&& ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom &&& 'BOX3D(125 125 125,126 126 126)'::box3d order by num;
+
+SELECT 'scan_idx', qnodes('select COUNT(num) FROM test WHERE the_geom IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geom IS NULL;
+
+DROP INDEX brin_3d;
+
+-- 4D
+CREATE INDEX brin_4d on test using brin (the_geom brin_geometry_inclusion_ops_4d) WITH (pages_per_range = 10);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom &&& ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom &&& 'BOX3D(125 125 125,126 126 126)'::box3d order by num;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geom IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geom IS NULL;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geom &&& ST_MakePoint(0,0)');
+ SELECT num, ST_astext(the_geom) FROM test WHERE the_geom &&& 'BOX3D(125 125 125,126 126 126)'::box3d order by num;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geom IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geom IS NULL;
+
+DROP INDEX brin_4d;
+
+-- cleanup
+DROP TABLE test;
+DROP FUNCTION qnodes(text);
+
+set enable_indexscan = on;
+set enable_bitmapscan = on;
+set enable_seqscan = on;
Added: trunk/regress/regress_brin_index_3d_expected
===================================================================
--- trunk/regress/regress_brin_index_3d_expected (rev 0)
+++ trunk/regress/regress_brin_index_3d_expected 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,132 @@
+scan_seq|Seq Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_seq|Seq Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_seq|Seq Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_seq|Seq Scan
+20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+20
+scan_seq|Seq Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_seq|Seq Scan
+20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+20
+scan_seq|Seq Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_seq|Seq Scan
+20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+1250|POINT Z (125 125 125)
+1251|POINT Z (125.1 125.1 125.1)
+1252|POINT Z (125.2 125.2 125.2)
+1253|POINT Z (125.3 125.3 125.3)
+1254|POINT Z (125.4 125.4 125.4)
+1255|POINT Z (125.5 125.5 125.5)
+1256|POINT Z (125.6 125.6 125.6)
+1257|POINT Z (125.7 125.7 125.7)
+1258|POINT Z (125.8 125.8 125.8)
+1259|POINT Z (125.9 125.9 125.9)
+1260|POINT Z (126 126 126)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+20
Added: trunk/regress/regress_brin_index_expected
===================================================================
--- trunk/regress/regress_brin_index_expected (rev 0)
+++ trunk/regress/regress_brin_index_expected 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,40 @@
+scan_seq|Seq Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_seq|Seq Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_seq|Seq Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_seq|Seq Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_seq|Seq Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2594|POINT(130.504303 126.53112)
+3618|POINT(130.447205 131.655289)
+7245|POINT(128.10466 130.94133)
Added: trunk/regress/regress_brin_index_geography.sql
===================================================================
--- trunk/regress/regress_brin_index_geography.sql (rev 0)
+++ trunk/regress/regress_brin_index_geography.sql 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,61 @@
+--- build a larger database
+\i regress_lots_of_geographies.sql
+
+--- test some of the searching capabilities
+
+CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
+LANGUAGE 'plpgsql' AS
+$$
+DECLARE
+ exp TEXT;
+ mat TEXT[];
+ ret TEXT[];
+BEGIN
+ FOR exp IN EXECUTE 'EXPLAIN ' || q
+ LOOP
+ --RAISE NOTICE 'EXP: %', exp;
+ mat := regexp_matches(exp, ' *(?:-> *)?(.*Scan)');
+ --RAISE NOTICE 'MAT: %', mat;
+ IF mat IS NOT NULL THEN
+ ret := array_append(ret, mat[1]);
+ END IF;
+ --RAISE NOTICE 'RET: %', ret;
+ END LOOP;
+ RETURN array_to_string(ret,',');
+END;
+$$;
+
+-- BRIN indexes
+
+-- 2D
+CREATE INDEX brin_geog on test using brin (the_geog) WITH (pages_per_range = 10);
+
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+
+SELECT 'scan_seq', qnodes('select * from test where the_geog && ST_GeographyFromText(''SRID=4326;POLYGON((43. 42.,43. 43.,42. 43.,42. 42.,43. 42.))'')');
+ select num,ST_astext(the_geog) from test where the_geog && ST_GeographyFromText('SRID=4326;POLYGON((43. 42.,43. 43.,42. 43.,42. 42.,43. 42.))') order by num;
+
+SELECT 'scan_seq', qnodes('SELECT * FROM test WHERE the_geog IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geog IS NULL;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geog && ST_GeographyFromText(''SRID=4326;POLYGON((43. 42.,43. 43.,42. 43.,42. 42.,43. 42.))'')');
+ select num,ST_astext(the_geog) from test where the_geog && ST_GeographyFromText('SRID=4326;POLYGON((43. 42.,43. 43.,42. 43.,42. 42.,43. 42.))') order by num;
+
+SELECT 'scan_idx', qnodes('SELECT * FROM test WHERE the_geog IS NULL');
+ SELECT COUNT(num) FROM test WHERE the_geog IS NULL;
+
+DROP INDEX brin_geog;
+
+-- cleanup
+DROP TABLE test;
+DROP FUNCTION qnodes(text);
+
+set enable_indexscan = on;
+set enable_bitmapscan = on;
+set enable_seqscan = on;
Added: trunk/regress/regress_brin_index_geography_expected
===================================================================
--- trunk/regress/regress_brin_index_geography_expected (rev 0)
+++ trunk/regress/regress_brin_index_geography_expected 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,170 @@
+scan_seq|Seq Scan
+42.01|POINT Z (42.01 42.01 42.01)
+42.03|POINT Z (42.03 42.03 42.03)
+42.04|POINT Z (42.04 42.04 42.04)
+42.05|POINT Z (42.05 42.05 42.05)
+42.06|POINT Z (42.06 42.06 42.06)
+42.07|POINT Z (42.07 42.07 42.07)
+42.08|POINT Z (42.08 42.08 42.08)
+42.09|POINT Z (42.09 42.09 42.09)
+42.11|POINT Z (42.11 42.11 42.11)
+42.12|POINT Z (42.12 42.12 42.12)
+42.14|POINT Z (42.14 42.14 42.14)
+42.15|POINT Z (42.15 42.15 42.15)
+42.16|POINT Z (42.16 42.16 42.16)
+42.17|POINT Z (42.17 42.17 42.17)
+42.18|POINT Z (42.18 42.18 42.18)
+42.19|POINT Z (42.19 42.19 42.19)
+42.21|POINT Z (42.21 42.21 42.21)
+42.22|POINT Z (42.22 42.22 42.22)
+42.23|POINT Z (42.23 42.23 42.23)
+42.25|POINT Z (42.25 42.25 42.25)
+42.26|POINT Z (42.26 42.26 42.26)
+42.27|POINT Z (42.27 42.27 42.27)
+42.28|POINT Z (42.28 42.28 42.28)
+42.29|POINT Z (42.29 42.29 42.29)
+42.31|POINT Z (42.31 42.31 42.31)
+42.32|POINT Z (42.32 42.32 42.32)
+42.33|POINT Z (42.33 42.33 42.33)
+42.34|POINT Z (42.34 42.34 42.34)
+42.36|POINT Z (42.36 42.36 42.36)
+42.37|POINT Z (42.37 42.37 42.37)
+42.38|POINT Z (42.38 42.38 42.38)
+42.39|POINT Z (42.39 42.39 42.39)
+42.41|POINT Z (42.41 42.41 42.41)
+42.42|POINT Z (42.42 42.42 42.42)
+42.43|POINT Z (42.43 42.43 42.43)
+42.44|POINT Z (42.44 42.44 42.44)
+42.45|POINT Z (42.45 42.45 42.45)
+42.47|POINT Z (42.47 42.47 42.47)
+42.48|POINT Z (42.48 42.48 42.48)
+42.49|POINT Z (42.49 42.49 42.49)
+42.51|POINT Z (42.51 42.51 42.51)
+42.52|POINT Z (42.52 42.52 42.52)
+42.53|POINT Z (42.53 42.53 42.53)
+42.54|POINT Z (42.54 42.54 42.54)
+42.55|POINT Z (42.55 42.55 42.55)
+42.56|POINT Z (42.56 42.56 42.56)
+42.58|POINT Z (42.58 42.58 42.58)
+42.59|POINT Z (42.59 42.59 42.59)
+42.61|POINT Z (42.61 42.61 42.61)
+42.62|POINT Z (42.62 42.62 42.62)
+42.63|POINT Z (42.63 42.63 42.63)
+42.64|POINT Z (42.64 42.64 42.64)
+42.65|POINT Z (42.65 42.65 42.65)
+42.66|POINT Z (42.66 42.66 42.66)
+42.67|POINT Z (42.67 42.67 42.67)
+42.69|POINT Z (42.69 42.69 42.69)
+42.71|POINT Z (42.71 42.71 42.71)
+42.72|POINT Z (42.72 42.72 42.72)
+42.73|POINT Z (42.73 42.73 42.73)
+42.74|POINT Z (42.74 42.74 42.74)
+42.75|POINT Z (42.75 42.75 42.75)
+42.76|POINT Z (42.76 42.76 42.76)
+42.77|POINT Z (42.77 42.77 42.77)
+42.78|POINT Z (42.78 42.78 42.78)
+42.81|POINT Z (42.81 42.81 42.81)
+42.82|POINT Z (42.82 42.82 42.82)
+42.83|POINT Z (42.83 42.83 42.83)
+42.84|POINT Z (42.84 42.84 42.84)
+42.85|POINT Z (42.85 42.85 42.85)
+42.86|POINT Z (42.86 42.86 42.86)
+42.87|POINT Z (42.87 42.87 42.87)
+42.88|POINT Z (42.88 42.88 42.88)
+42.89|POINT Z (42.89 42.89 42.89)
+42.91|POINT Z (42.91 42.91 42.91)
+42.92|POINT Z (42.92 42.92 42.92)
+42.93|POINT Z (42.93 42.93 42.93)
+42.94|POINT Z (42.94 42.94 42.94)
+42.95|POINT Z (42.95 42.95 42.95)
+42.96|POINT Z (42.96 42.96 42.96)
+42.97|POINT Z (42.97 42.97 42.97)
+42.98|POINT Z (42.98 42.98 42.98)
+42.99|POINT Z (42.99 42.99 42.99)
+scan_seq|Seq Scan
+1001
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+42.01|POINT Z (42.01 42.01 42.01)
+42.03|POINT Z (42.03 42.03 42.03)
+42.04|POINT Z (42.04 42.04 42.04)
+42.05|POINT Z (42.05 42.05 42.05)
+42.06|POINT Z (42.06 42.06 42.06)
+42.07|POINT Z (42.07 42.07 42.07)
+42.08|POINT Z (42.08 42.08 42.08)
+42.09|POINT Z (42.09 42.09 42.09)
+42.11|POINT Z (42.11 42.11 42.11)
+42.12|POINT Z (42.12 42.12 42.12)
+42.14|POINT Z (42.14 42.14 42.14)
+42.15|POINT Z (42.15 42.15 42.15)
+42.16|POINT Z (42.16 42.16 42.16)
+42.17|POINT Z (42.17 42.17 42.17)
+42.18|POINT Z (42.18 42.18 42.18)
+42.19|POINT Z (42.19 42.19 42.19)
+42.21|POINT Z (42.21 42.21 42.21)
+42.22|POINT Z (42.22 42.22 42.22)
+42.23|POINT Z (42.23 42.23 42.23)
+42.25|POINT Z (42.25 42.25 42.25)
+42.26|POINT Z (42.26 42.26 42.26)
+42.27|POINT Z (42.27 42.27 42.27)
+42.28|POINT Z (42.28 42.28 42.28)
+42.29|POINT Z (42.29 42.29 42.29)
+42.31|POINT Z (42.31 42.31 42.31)
+42.32|POINT Z (42.32 42.32 42.32)
+42.33|POINT Z (42.33 42.33 42.33)
+42.34|POINT Z (42.34 42.34 42.34)
+42.36|POINT Z (42.36 42.36 42.36)
+42.37|POINT Z (42.37 42.37 42.37)
+42.38|POINT Z (42.38 42.38 42.38)
+42.39|POINT Z (42.39 42.39 42.39)
+42.41|POINT Z (42.41 42.41 42.41)
+42.42|POINT Z (42.42 42.42 42.42)
+42.43|POINT Z (42.43 42.43 42.43)
+42.44|POINT Z (42.44 42.44 42.44)
+42.45|POINT Z (42.45 42.45 42.45)
+42.47|POINT Z (42.47 42.47 42.47)
+42.48|POINT Z (42.48 42.48 42.48)
+42.49|POINT Z (42.49 42.49 42.49)
+42.51|POINT Z (42.51 42.51 42.51)
+42.52|POINT Z (42.52 42.52 42.52)
+42.53|POINT Z (42.53 42.53 42.53)
+42.54|POINT Z (42.54 42.54 42.54)
+42.55|POINT Z (42.55 42.55 42.55)
+42.56|POINT Z (42.56 42.56 42.56)
+42.58|POINT Z (42.58 42.58 42.58)
+42.59|POINT Z (42.59 42.59 42.59)
+42.61|POINT Z (42.61 42.61 42.61)
+42.62|POINT Z (42.62 42.62 42.62)
+42.63|POINT Z (42.63 42.63 42.63)
+42.64|POINT Z (42.64 42.64 42.64)
+42.65|POINT Z (42.65 42.65 42.65)
+42.66|POINT Z (42.66 42.66 42.66)
+42.67|POINT Z (42.67 42.67 42.67)
+42.69|POINT Z (42.69 42.69 42.69)
+42.71|POINT Z (42.71 42.71 42.71)
+42.72|POINT Z (42.72 42.72 42.72)
+42.73|POINT Z (42.73 42.73 42.73)
+42.74|POINT Z (42.74 42.74 42.74)
+42.75|POINT Z (42.75 42.75 42.75)
+42.76|POINT Z (42.76 42.76 42.76)
+42.77|POINT Z (42.77 42.77 42.77)
+42.78|POINT Z (42.78 42.78 42.78)
+42.81|POINT Z (42.81 42.81 42.81)
+42.82|POINT Z (42.82 42.82 42.82)
+42.83|POINT Z (42.83 42.83 42.83)
+42.84|POINT Z (42.84 42.84 42.84)
+42.85|POINT Z (42.85 42.85 42.85)
+42.86|POINT Z (42.86 42.86 42.86)
+42.87|POINT Z (42.87 42.87 42.87)
+42.88|POINT Z (42.88 42.88 42.88)
+42.89|POINT Z (42.89 42.89 42.89)
+42.91|POINT Z (42.91 42.91 42.91)
+42.92|POINT Z (42.92 42.92 42.92)
+42.93|POINT Z (42.93 42.93 42.93)
+42.94|POINT Z (42.94 42.94 42.94)
+42.95|POINT Z (42.95 42.95 42.95)
+42.96|POINT Z (42.96 42.96 42.96)
+42.97|POINT Z (42.97 42.97 42.97)
+42.98|POINT Z (42.98 42.98 42.98)
+42.99|POINT Z (42.99 42.99 42.99)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+1001
Added: trunk/regress/regress_lots_of_3dpoints.sql
===================================================================
--- trunk/regress/regress_lots_of_3dpoints.sql (rev 0)
+++ trunk/regress/regress_lots_of_3dpoints.sql 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,14 @@
+CREATE TABLE "test" (
+ "num" integer,
+ "the_geom" geometry
+);
+
+INSERT INTO test (num, the_geom)
+ SELECT i,
+ CASE
+ WHEN i%1000 = 0 THEN NULL
+ WHEN i%1100 = 0 THEN 'POINTZ EMPTY'::geometry
+ ELSE
+ st_makepoint(i::numeric/10, i::numeric/10, i::numeric/10)
+ END
+ FROM generate_series(1, 20000) i;
Added: trunk/regress/regress_lots_of_geographies.sql
===================================================================
--- trunk/regress/regress_lots_of_geographies.sql (rev 0)
+++ trunk/regress/regress_lots_of_geographies.sql 2016-07-31 04:34:16 UTC (rev 15029)
@@ -0,0 +1,15 @@
+CREATE TABLE "test" (
+ "num" numeric
+,
+ "the_geog" geography
+);
+
+INSERT INTO test (num, the_geog)
+ SELECT i,
+ CASE
+ WHEN i%0.1 = 0.0 THEN NULL
+ WHEN i%0.11 = 0 THEN 'SRID=4326;POINTZ EMPTY'::geography
+ ELSE
+ ST_GeographyFromText('SRID=4326;POINTZ(' || i || ' ' || i || ' ' || i || ')')
+ END
+ FROM generate_series(-20.0, 80.0, 0.01) i;
More information about the postgis-tickets
mailing list