[postgis-tickets] r15226 - Bugs in BRIN support
Regina Obe
lr at pcorp.us
Fri Nov 11 11:35:58 PST 2016
Author: robe
Date: 2016-11-11 11:35:58 -0800 (Fri, 11 Nov 2016)
New Revision: 15226
Modified:
trunk/doc/using_postgis_dataman.xml
trunk/libpgcommon/gserialized_gist.c
trunk/libpgcommon/gserialized_gist.h
trunk/postgis/brin_2d.c
trunk/postgis/brin_nd.c
trunk/postgis/gserialized_gist_2d.c
trunk/postgis/gserialized_gist_nd.c
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
Log:
Bugs in BRIN support
Patch from Julien Rouhaud (Dalibo)
references #3665 for PostGIS 2.4 (trunk)
Modified: trunk/doc/using_postgis_dataman.xml
===================================================================
--- trunk/doc/using_postgis_dataman.xml 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/doc/using_postgis_dataman.xml 2016-11-11 19:35:58 UTC (rev 15226)
@@ -2184,7 +2184,7 @@
<para>BRIN stands for "Block Range Index" and is a generic form of
indexing that has been introduced in PostgreSQL 9.5. BRIN is a lossy kind
of index, and its main usage is to provide a compromise for both read and
- write performance. It's primary goal is to handle very large tables for
+ write performance. Its primary goal is to handle very large tables for
which some of the columns have some natural correlation with their
physical location within the table. In addition to GIS indexing, BRIN is
used to speed up searches on various kinds of regular or irregular data
@@ -2221,13 +2221,22 @@
follows:</para>
<para><programlisting>CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ); </programlisting></para>
- <para>The above syntax will always build a 2D-index. To get the 3d-dimensional index supported in PostGIS 2.0+ for the geometry type, you can create one using this syntax</para>
+ <para>The above syntax will always build a 2D-index. To get a 3d-dimensional index, you can create one using this syntax</para>
<programlisting>CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_3d);</programlisting>
- <para>These above syntaxes will use the default number or block in a range, which is 128. To specify the number of blocks you want to summarise in a range, you can create one using this syntax</para>
- <para><programlisting>CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]); </programlisting></para>
+ <para>You can also get a 4d-dimensional index using the 4d operator class</para>
+ <programlisting>CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] brin_geometry_inclusion_ops_4d);</programlisting>
+ <para>These above syntaxes will use the default number or block in a range, which is 128. To specify the number of blocks you want to summarise in a range, you can create one using this syntax</para>
+ <para><programlisting>CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH (pages_per_range = [number]); </programlisting></para>
+ <para>Also, keep in mind that a BRIN index will only store one index
+ value for a large number of rows. If your table stores geometries with
+ a mixed number of dimensions, it's likely that the resulting index will
+ have poor performance. You can avoid this drop of performance by
+ choosing the operator class whith the least number of dimensions of the
+ stored geometries
+ </para>
<para>Also the "geography" datatype is supported for BRIN indexing. The
- syntax for building a BRIN index on a "geometry" column is as follows:</para>
+ syntax for building a BRIN index on a "geography" column is as follows:</para>
<para><programlisting>CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geographyfield] ); </programlisting></para>
<para>The above syntax will always build a 2D-index for geospatial objetcs on the spheroid. </para>
Modified: trunk/libpgcommon/gserialized_gist.c
===================================================================
--- trunk/libpgcommon/gserialized_gist.c 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/libpgcommon/gserialized_gist.c 2016-11-11 19:35:58 UTC (rev 15226)
@@ -290,12 +290,17 @@
{
POSTGIS_DEBUG(4, "could not calculate bbox, returning failure");
lwgeom_free(lwgeom);
+ POSTGIS_FREE_IF_COPY_P(gpart, gsdatum);
+ POSTGIS_FREE_IF_COPY_P(g, gsdatum);
return LW_FAILURE;
}
lwgeom_free(lwgeom);
+ POSTGIS_FREE_IF_COPY_P(g, gsdatum);
result = gidx_from_gbox_p(gbox, gidx);
}
-
+
+ POSTGIS_FREE_IF_COPY_P(gpart, gsdatum);
+
if ( result == LW_SUCCESS )
{
POSTGIS_DEBUGF(4, "got gidx %s", gidx_to_string(gidx));
Modified: trunk/libpgcommon/gserialized_gist.h
===================================================================
--- trunk/libpgcommon/gserialized_gist.h 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/libpgcommon/gserialized_gist.h 2016-11-11 19:35:58 UTC (rev 15226)
@@ -27,6 +27,17 @@
#define GIDX_MAX_DIM 4
+/*
+ * This macro is based on PG_FREE_IF_COPY, except that it accepts two pointers.
+ * See PG_FREE_IF_COPY comment in src/include/fmgr.h in postgres source code
+ * for more details.
+ */
+#define POSTGIS_FREE_IF_COPY_P(ptrsrc, ptrori) \
+ do { \
+ if ((Pointer) (ptrsrc) != (Pointer) (ptrori)) \
+ pfree(ptrsrc); \
+ } while (0)
+
/**********************************************************************
** BOX2DF structure.
**
@@ -95,6 +106,8 @@
/* Remove the box from a disk serialization */
GSERIALIZED* gserialized_drop_gidx(GSERIALIZED *g);
+bool box2df_contains(const BOX2DF *a, const BOX2DF *b);
+bool gidx_contains(GIDX *a, GIDX *b);
int gserialized_datum_get_box2df_p(Datum gsdatum, BOX2DF *box2df);
Modified: trunk/postgis/brin_2d.c
===================================================================
--- trunk/postgis/brin_2d.c 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/postgis/brin_2d.c 2016-11-11 19:35:58 UTC (rev 15226)
@@ -72,17 +72,20 @@
PG_RETURN_BOOL(true);
}
+ box_key = (BOX2DF *) column->bv_values[INCLUSION_UNION];
+
+ /* Check if the stored bouding box already contains the geometry's one */
+ if (box2df_contains(box_key, &box_geom))
+ PG_RETURN_BOOL(false);
+
/*
* 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);
+ PG_RETURN_BOOL(true);
}
Modified: trunk/postgis/brin_nd.c
===================================================================
--- trunk/postgis/brin_nd.c 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/postgis/brin_nd.c 2016-11-11 19:35:58 UTC (rev 15226)
@@ -12,7 +12,7 @@
*/
Datum gidx_brin_inclusion_add_value(BrinDesc *bdesc, BrinValues *column, Datum
- newval, bool isnull, int dims_wanted);
+ newval, bool isnull, int max_dims);
/*
* As for the GiST case, geographies are converted into GIDX before
@@ -60,13 +60,13 @@
Datum
gidx_brin_inclusion_add_value(BrinDesc *bdesc, BrinValues *column, Datum newval,
- bool isnull, int dims_wanted)
+ bool isnull, int max_dims)
{
char gboxmem[GIDX_MAX_SIZE];
GIDX *gidx_geom, *gidx_key;
- int dims_geom, i;
+ int dims_geom, dims_key, i;
- Assert(dims_wanted <= GIDX_MAX_DIM);
+ Assert(max_dims <= GIDX_MAX_DIM);
/*
* If the new value is null, we record that we saw it if it's the first
@@ -81,6 +81,14 @@
PG_RETURN_BOOL(true);
}
+ /*
+ * No need for further processing if the block range is already initialized
+ * and is marked as containing unmergeable values.
+ */
+ if (!column->bv_allnulls &&
+ DatumGetBool(column->bv_values[INCLUSION_UNMERGEABLE]))
+ PG_RETURN_BOOL(false);
+
/* create a new GIDX in stack memory, maximum dimensions */
gidx_geom = (GIDX *) gboxmem;
@@ -120,28 +128,24 @@
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.
+ * It's not safe to summarize geometries of different number of
+ * dimensions in the same range. We therefore fix the number of
+ * dimension for this range by storing the bounding box of the first
+ * geometry found as is, being careful not to store more dimension than
+ * defined in the opclass.
*/
- if (dims_geom != dims_wanted)
+ if (dims_geom > max_dims)
{
/*
- * 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
+ * Diminush the varsize to only store the maximum number of
+ * dimensions allowed by the opclass
*/
- SET_VARSIZE(gidx_geom, VARHDRSZ + dims_wanted * 2 * sizeof(float));
+ SET_VARSIZE(gidx_geom, VARHDRSZ + max_dims * 2 * sizeof(float));
+ dims_geom = max_dims;
}
- /* 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));
+ GIDX_SIZE(dims_geom));
column->bv_values[INCLUSION_UNMERGEABLE] = BoolGetDatum(false);
column->bv_values[INCLUSION_CONTAINS_EMPTY] = BoolGetDatum(false);
column->bv_allnulls = false;
@@ -149,13 +153,29 @@
}
gidx_key = (GIDX *) column->bv_values[INCLUSION_UNION];
+ dims_key = GIDX_NDIMS(gidx_key);
/*
- * As we always store a GIDX of the wanted number of dimensions, we just
- * need adjust min and max
+ * Mark the datum as unmergeable if its number of dimension is not the same
+ * as the one stored in the key of the current range
*/
- for ( i = 0; i < dims_wanted; i++ )
+ if (dims_key != dims_geom)
{
+ column->bv_values[INCLUSION_UNMERGEABLE] = BoolGetDatum(true);
+ PG_RETURN_BOOL(true);
+ }
+
+ /* Check if the stored bounding box already contains the geometry's one */
+ if (gidx_contains(gidx_key, gidx_geom))
+ PG_RETURN_BOOL(false);
+
+ /*
+ * Otherwise, we need to enlarge the stored GIDX to make it contains the
+ * current geometry. As we store a GIDX with a fixed number of dimensions,
+ * we just need adjust min and max
+ */
+ for ( i = 0; i < dims_key; i++ )
+ {
/* Adjust minimums */
GIDX_SET_MIN(gidx_key, i,
Min(GIDX_GET_MIN(gidx_key,i),GIDX_GET_MIN(gidx_geom,i)));
@@ -164,5 +184,5 @@
Max(GIDX_GET_MAX(gidx_key,i),GIDX_GET_MAX(gidx_geom,i)));
}
- PG_RETURN_BOOL(false);
+ PG_RETURN_BOOL(true);
}
Modified: trunk/postgis/gserialized_gist_2d.c
===================================================================
--- trunk/postgis/gserialized_gist_2d.c 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/postgis/gserialized_gist_2d.c 2016-11-11 19:35:58 UTC (rev 15226)
@@ -291,7 +291,7 @@
return TRUE;
}
-static bool box2df_contains(const BOX2DF *a, const BOX2DF *b)
+bool box2df_contains(const BOX2DF *a, const BOX2DF *b)
{
if ( ! a || ! b ) return FALSE; /* TODO: might be smarter for EMPTY */
@@ -595,11 +595,16 @@
if (gserialized_get_gbox_p(g, &gbox) == LW_FAILURE)
{
POSTGIS_DEBUG(4, "could not calculate bbox, returning failure");
+ POSTGIS_FREE_IF_COPY_P(gpart, gsdatum);
+ POSTGIS_FREE_IF_COPY_P(g, gsdatum);
return LW_FAILURE;
}
+ POSTGIS_FREE_IF_COPY_P(g, gsdatum);
result = box2df_from_gbox_p(&gbox, box2df);
}
+ POSTGIS_FREE_IF_COPY_P(gpart, gsdatum);
+
if ( result == LW_SUCCESS )
{
POSTGIS_DEBUGF(4, "got box2df %s", box2df_to_string(box2df));
Modified: trunk/postgis/gserialized_gist_nd.c
===================================================================
--- trunk/postgis/gserialized_gist_nd.c 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/postgis/gserialized_gist_nd.c 2016-11-11 19:35:58 UTC (rev 15226)
@@ -378,7 +378,7 @@
**
** Box(A) CONTAINS Box(B) IFF (pt(A)LL < pt(B)LL) && (pt(A)UR > pt(B)UR)
*/
-static bool gidx_contains(GIDX *a, GIDX *b)
+bool gidx_contains(GIDX *a, GIDX *b)
{
int i, dims_a, dims_b;
Modified: trunk/regress/regress_brin_index.sql
===================================================================
--- trunk/regress/regress_brin_index.sql 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/regress/regress_brin_index.sql 2016-11-11 19:35:58 UTC (rev 15226)
@@ -1,7 +1,7 @@
--- build a larger database
\i regress_lots_of_points.sql
---- test some of the searching capabilities
+--- Test the various BRIN opclass with dataset containing 2D geometries
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
LANGUAGE 'plpgsql' AS
@@ -96,6 +96,81 @@
DROP INDEX brin_4d;
+-- test adding rows and unsummarized ranges
+--
+
+-- 2D
+TRUNCATE TABLE test;
+INSERT INTO test select 1, st_makepoint(1, 1);
+CREATE INDEX brin_2d on test using brin (the_geom) WITH (pages_per_range = 1);
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(2, 3) i;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom && ''BOX(2.1 2.1, 3.1 3.1)''::box2d');
+ select '2d', count(*) from test where the_geom && 'BOX(2.1 2.1, 3.1 3.1)'::box2d;
+
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(4, 1000) i;
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom && ''BOX(900.1 900.1, 920.1 920.1)''::box2d');
+ select '2d', count(*) from test where the_geom && 'BOX(900.1 900.1, 920.1 920.1)'::box2d;
+
+SELECT 'summarize 2d', brin_summarize_new_values('brin_2d');
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom && ''BOX(900.1 900.1, 920.1 920.1)''::box2d');
+ select '2d', count(*) from test where the_geom && 'BOX(900.1 900.1, 920.1 920.1)'::box2d;
+
+DROP INDEX brin_2d;
+
+-- 3D
+TRUNCATE TABLE test;
+INSERT INTO test select 1, st_makepoint(1, 1);
+CREATE INDEX brin_3d on test using brin (the_geom brin_geometry_inclusion_ops_3d) WITH (pages_per_range = 1);
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(2, 3) i;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)''::box3d');
+ select '3d', count(*) from test where the_geom &&& 'BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)'::box3d;
+
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(4, 1000) i;
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '3d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+SELECT 'summarize 3d', brin_summarize_new_values('brin_3d');
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '3d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+DROP INDEX brin_3d;
+
+-- 4D
+TRUNCATE TABLE test;
+INSERT INTO test select 1, st_makepoint(1, 1);
+CREATE INDEX brin_4d on test using brin (the_geom brin_geometry_inclusion_ops_4d) WITH (pages_per_range = 1);
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(2, 3) i;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)''::box3d');
+ select '4d', count(*) from test where the_geom &&& 'BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)'::box3d;
+
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(4, 1000) i;
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '4d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+SELECT 'summarize 4d', brin_summarize_new_values('brin_4d');
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '4d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+DROP INDEX brin_4d;
+
-- cleanup
DROP TABLE test;
DROP FUNCTION qnodes(text);
Modified: trunk/regress/regress_brin_index_3d.sql
===================================================================
--- trunk/regress/regress_brin_index_3d.sql 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/regress/regress_brin_index_3d.sql 2016-11-11 19:35:58 UTC (rev 15226)
@@ -1,7 +1,8 @@
--- build a larger database
\i regress_lots_of_3dpoints.sql
---- test some of the searching capabilities
+--- Test the various BRIN opclass with dataset containing 3D geometries, or
+-- geometries of different number of dimensions
CREATE OR REPLACE FUNCTION qnodes(q text) RETURNS text
LANGUAGE 'plpgsql' AS
@@ -114,6 +115,137 @@
DROP INDEX brin_4d;
+-- test adding rows and unsummarized ranges
+--
+
+-- 2D
+TRUNCATE TABLE test;
+INSERT INTO test select 1, st_makepoint(1, 1, 1);
+CREATE INDEX brin_2d on test using brin (the_geom) WITH (pages_per_range = 1);
+INSERT INTO test select i, st_makepoint(i, i, i) FROM generate_series(2, 3) i;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom && ''BOX(2.1 2.1, 3.1 3.1)''::box2d');
+ select '2d', count(*) from test where the_geom && 'BOX(2.1 2.1, 3.1 3.1)'::box2d;
+
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(4, 1000) i;
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom && ''BOX(900.1 900.1, 920.1 920.1)''::box2d');
+ select '2d', count(*) from test where the_geom && 'BOX(900.1 900.1, 920.1 920.1)'::box2d;
+
+SELECT 'summarize 2d', brin_summarize_new_values('brin_2d');
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom && ''BOX(900.1 900.1, 920.1 920.1)''::box2d');
+ select '2d', count(*) from test where the_geom && 'BOX(900.1 900.1, 920.1 920.1)'::box2d;
+
+DROP INDEX brin_2d;
+
+-- 3D
+TRUNCATE TABLE test;
+INSERT INTO test select 1, st_makepoint(1, 1, 1);
+CREATE INDEX brin_3d on test using brin (the_geom brin_geometry_inclusion_ops_3d) WITH (pages_per_range = 1);
+INSERT INTO test select i, st_makepoint(i, i, i) FROM generate_series(2, 3) i;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)''::box3d');
+ select '3d', count(*) from test where the_geom &&& 'BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)'::box3d;
+
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(4, 1000) i;
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '3d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+SELECT 'summarize 3d', brin_summarize_new_values('brin_3d');
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '3d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+DROP INDEX brin_3d;
+
+-- 4D
+TRUNCATE TABLE test;
+INSERT INTO test select 1, st_makepoint(1, 1, 1);
+CREATE INDEX brin_4d on test using brin (the_geom brin_geometry_inclusion_ops_4d) WITH (pages_per_range = 1);
+INSERT INTO test select i, st_makepoint(i, i, i) FROM generate_series(2, 3) i;
+
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)''::box3d');
+ select '4d', count(*) from test where the_geom &&& 'BOX3D(2.1 2.1 2.1, 3.1 3.1 3.1)'::box3d;
+
+INSERT INTO test select i, st_makepoint(i, i) FROM generate_series(4, 1000) i;
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '4d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+SELECT 'summarize 4d', brin_summarize_new_values('brin_4d');
+
+SELECT 'scan_idx', qnodes('select count(*) from test where the_geom &&& ''BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)''::box3d');
+ select '4d', count(*) from test where the_geom &&& 'BOX3D(900.1 900.1 900.1, 920.1 920.1 920.1)'::box3d;
+
+DROP INDEX brin_4d;
+
+-- test mix of dimensions, NULL and empty geomertries
+TRUNCATE TABLE test;
+INSERT INTO test SELECT i,
+ CASE i%5
+ WHEN 0 THEN ST_MakePoint(i, i)
+ WHEN 1 THEN ST_MakePoint(i, i, 2)
+ WHEN 2 THEN ST_MakePoint(i, i, 2, 3)
+ WHEN 3 THEN NULL
+ ELSE 'POINTZ EMPTY'::geometry
+ END
+ FROM generate_series(1, 5) i;
+
+-- seq scan
+set enable_indexscan = off;
+set enable_bitmapscan = off;
+set enable_seqscan = on;
+SELECT 'scan_seq', qnodes('select * from test where the_geom && ''BOX(1 1, 5 5)''::box2d');
+ select 'mix_seq_box2d', num,ST_astext(the_geom) from test where the_geom && 'BOX(1 1, 5 5)'::box2d order by num;
+ select 'mix_seq_box3d', num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(1 1 0, 5 5 0)'::box3d order by num;
+
+-- 2D
+CREATE INDEX brin_2d on test using brin (the_geom) WITH (pages_per_range = 1);
+set enable_indexscan = off;
+set enable_bitmapscan = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom && ''BOX(1 1, 5 5)''::box2d');
+ select 'mix_2d_box2d', num,ST_astext(the_geom) from test where the_geom && 'BOX(1 1, 5 5)'::box2d order by num;
+ select 'mix_2d_box3d', num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(1 1 0, 5 5 0)'::box3d 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 = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom && ''BOX(1 1, 5 5)''::box2d');
+ select 'mix_3d_box2d', num,ST_astext(the_geom) from test where the_geom && 'BOX(1 1, 5 5)'::box2d order by num;
+ select 'mix_3d_box3d', num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(1 1 0, 5 5 0)'::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 = on;
+set enable_seqscan = off;
+
+SELECT 'scan_idx', qnodes('select * from test where the_geom && ''BOX(1 1, 5 5)''::box2d');
+ select 'mix_4d_box2d', num,ST_astext(the_geom) from test where the_geom && 'BOX(1 1, 5 5)'::box2d order by num;
+ select 'mix_4d_box3d', num,ST_astext(the_geom) from test where the_geom &&& 'BOX3D(1 1 0, 5 5 0)'::box3d order by num;
+
+DROP INDEX brin_4d;
+
-- cleanup
DROP TABLE test;
DROP FUNCTION qnodes(text);
Modified: trunk/regress/regress_brin_index_3d_expected
===================================================================
--- trunk/regress/regress_brin_index_3d_expected 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/regress/regress_brin_index_3d_expected 2016-11-11 19:35:58 UTC (rev 15226)
@@ -130,3 +130,44 @@
1260|POINT Z (126 126 126)
scan_idx|Bitmap Heap Scan,Bitmap Index Scan
20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2d|1
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2d|20
+summarize 2d|8
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2d|20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+3d|1
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+3d|20
+summarize 3d|8
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+3d|20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+4d|1
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+4d|20
+summarize 4d|8
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+4d|20
+scan_seq|Seq Scan
+mix_seq_box2d|1|POINT Z (1 1 2)
+mix_seq_box2d|2|POINT ZM (2 2 2 3)
+mix_seq_box2d|5|POINT(5 5)
+mix_seq_box3d|5|POINT(5 5)
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+mix_2d_box2d|1|POINT Z (1 1 2)
+mix_2d_box2d|2|POINT ZM (2 2 2 3)
+mix_2d_box2d|5|POINT(5 5)
+mix_2d_box3d|5|POINT(5 5)
+scan_idx|Seq Scan
+mix_3d_box2d|1|POINT Z (1 1 2)
+mix_3d_box2d|2|POINT ZM (2 2 2 3)
+mix_3d_box2d|5|POINT(5 5)
+mix_3d_box3d|5|POINT(5 5)
+scan_idx|Seq Scan
+mix_4d_box2d|1|POINT Z (1 1 2)
+mix_4d_box2d|2|POINT ZM (2 2 2 3)
+mix_4d_box2d|5|POINT(5 5)
+mix_4d_box3d|5|POINT(5 5)
Modified: trunk/regress/regress_brin_index_expected
===================================================================
--- trunk/regress/regress_brin_index_expected 2016-11-10 19:07:53 UTC (rev 15225)
+++ trunk/regress/regress_brin_index_expected 2016-11-11 19:35:58 UTC (rev 15226)
@@ -38,3 +38,24 @@
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
+2d|1
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2d|20
+summarize 2d|8
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+2d|20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+3d|1
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+3d|20
+summarize 3d|8
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+3d|20
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+4d|1
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+4d|20
+summarize 4d|8
+scan_idx|Bitmap Heap Scan,Bitmap Index Scan
+4d|20
More information about the postgis-tickets
mailing list