From 2b3c01bcdfb25caf5f0259827e9bb8551ee033e2 Mon Sep 17 00:00:00 2001 From: Regina Obe Date: Sun, 31 Jul 2016 04:34:16 +0000 Subject: [PATCH] Add support for BRIN indexes - missed new files on last commit Closes #3591 Closes https://github.com/postgis/postgis/pull/106 git-svn-id: http://svn.osgeo.org/postgis/trunk@15029 b70326c6-7e19-0410-871a-916f4a2858ee --- postgis/brin_2d.c | 88 +++++++++ postgis/brin_common.c | 12 ++ postgis/brin_nd.c | 168 +++++++++++++++++ postgis/postgis_brin.h | 23 +++ regress/regress_brin_index.sql | 105 +++++++++++ regress/regress_brin_index_3d.sql | 123 +++++++++++++ regress/regress_brin_index_3d_expected | 132 ++++++++++++++ regress/regress_brin_index_expected | 40 +++++ regress/regress_brin_index_geography.sql | 61 +++++++ regress/regress_brin_index_geography_expected | 170 ++++++++++++++++++ regress/regress_lots_of_3dpoints.sql | 14 ++ regress/regress_lots_of_geographies.sql | 15 ++ 12 files changed, 951 insertions(+) create mode 100644 postgis/brin_2d.c create mode 100644 postgis/brin_common.c create mode 100644 postgis/brin_nd.c create mode 100644 postgis/postgis_brin.h create mode 100644 regress/regress_brin_index.sql create mode 100644 regress/regress_brin_index_3d.sql create mode 100644 regress/regress_brin_index_3d_expected create mode 100644 regress/regress_brin_index_expected create mode 100644 regress/regress_brin_index_geography.sql create mode 100644 regress/regress_brin_index_geography_expected create mode 100644 regress/regress_lots_of_3dpoints.sql create mode 100644 regress/regress_lots_of_geographies.sql diff --git a/postgis/brin_2d.c b/postgis/brin_2d.c new file mode 100644 index 000000000..f5fc2f53c --- /dev/null +++ b/postgis/brin_2d.c @@ -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); +} diff --git a/postgis/brin_common.c b/postgis/brin_common.c new file mode 100644 index 000000000..b07401469 --- /dev/null +++ b/postgis/brin_common.c @@ -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; +} diff --git a/postgis/brin_nd.c b/postgis/brin_nd.c new file mode 100644 index 000000000..db8a87149 --- /dev/null +++ b/postgis/brin_nd.c @@ -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); +} diff --git a/postgis/postgis_brin.h b/postgis/postgis_brin.h new file mode 100644 index 000000000..3c64c7017 --- /dev/null +++ b/postgis/postgis_brin.h @@ -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 +#include +#include +#include +#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); diff --git a/regress/regress_brin_index.sql b/regress/regress_brin_index.sql new file mode 100644 index 000000000..089523cbe --- /dev/null +++ b/regress/regress_brin_index.sql @@ -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; diff --git a/regress/regress_brin_index_3d.sql b/regress/regress_brin_index_3d.sql new file mode 100644 index 000000000..07c50da45 --- /dev/null +++ b/regress/regress_brin_index_3d.sql @@ -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; diff --git a/regress/regress_brin_index_3d_expected b/regress/regress_brin_index_3d_expected new file mode 100644 index 000000000..0c05fc689 --- /dev/null +++ b/regress/regress_brin_index_3d_expected @@ -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 diff --git a/regress/regress_brin_index_expected b/regress/regress_brin_index_expected new file mode 100644 index 000000000..009a0ae7d --- /dev/null +++ b/regress/regress_brin_index_expected @@ -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) diff --git a/regress/regress_brin_index_geography.sql b/regress/regress_brin_index_geography.sql new file mode 100644 index 000000000..ad90d82fd --- /dev/null +++ b/regress/regress_brin_index_geography.sql @@ -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; diff --git a/regress/regress_brin_index_geography_expected b/regress/regress_brin_index_geography_expected new file mode 100644 index 000000000..b427a5fc6 --- /dev/null +++ b/regress/regress_brin_index_geography_expected @@ -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 diff --git a/regress/regress_lots_of_3dpoints.sql b/regress/regress_lots_of_3dpoints.sql new file mode 100644 index 000000000..d99b2b9f1 --- /dev/null +++ b/regress/regress_lots_of_3dpoints.sql @@ -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; diff --git a/regress/regress_lots_of_geographies.sql b/regress/regress_lots_of_geographies.sql new file mode 100644 index 000000000..b4f7b106a --- /dev/null +++ b/regress/regress_lots_of_geographies.sql @@ -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; -- 2.40.0