diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml index 5b2b9db..ed65579 100644 --- a/doc/src/sgml/spgist.sgml +++ b/doc/src/sgml/spgist.sgml @@ -64,12 +64,13 @@ Built-in <acronym>SP-GiST</acronym> Operator Classes - + Name Indexed Data Type Indexable Operators + Ordering Operators @@ -84,6 +85,9 @@ >^~= + + <-> + quad_point_ops @@ -96,6 +100,9 @@ >^ ~= + + <-> + range_ops @@ -111,6 +118,8 @@ >> @> + + box_ops @@ -129,6 +138,8 @@ |>> |&> + + poly_ops @@ -147,6 +158,8 @@ |>> |&> + + text_ops @@ -163,6 +176,8 @@ ~>~ ^@ + + inet_ops @@ -180,6 +195,8 @@ <= = + + @@ -191,6 +208,12 @@ supports the same operators but uses a different index data structure which may offer better performance in some applications. + + The quad_point_ops, kd_point_ops + classes support the <-> ordering operator, which enables + the k-nearest neighbor (k-NN) search over indexed + point datasets. + diff --git a/src/backend/access/spgist/Makefile b/src/backend/access/spgist/Makefile index 14948a5..5be3df5 100644 --- a/src/backend/access/spgist/Makefile +++ b/src/backend/access/spgist/Makefile @@ -14,6 +14,7 @@ include $(top_builddir)/src/Makefile.global OBJS = spgutils.o spginsert.o spgscan.o spgvacuum.o spgvalidate.o \ spgdoinsert.o spgxlog.o \ - spgtextproc.o spgquadtreeproc.o spgkdtreeproc.o + spgtextproc.o spgquadtreeproc.o spgkdtreeproc.o \ + spgproc.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/access/spgist/spgkdtreeproc.c b/src/backend/access/spgist/spgkdtreeproc.c index 556f3a4..91403ff 100644 --- a/src/backend/access/spgist/spgkdtreeproc.c +++ b/src/backend/access/spgist/spgkdtreeproc.c @@ -17,6 +17,7 @@ #include "access/spgist.h" #include "access/stratnum.h" +#include "access/spgist_private.h" #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/geo_decls.h" @@ -162,6 +163,7 @@ spg_kd_inner_consistent(PG_FUNCTION_ARGS) double coord; int which; int i; + BOX boxes[2]; Assert(in->hasPrefix); coord = DatumGetFloat8(in->prefixDatum); @@ -248,12 +250,75 @@ spg_kd_inner_consistent(PG_FUNCTION_ARGS) } /* We must descend into the children identified by which */ - out->nodeNumbers = (int *) palloc(sizeof(int) * 2); out->nNodes = 0; + + if (!which) + PG_RETURN_VOID(); + + out->nodeNumbers = (int *) palloc(sizeof(int) * 2); + + if (in->norderbys > 0) + { + BOX infArea; + BOX *area; + + out->distances = (double **) palloc(sizeof(double *) * in->nNodes); + out->traversalValues = (void **) palloc(sizeof(void *) * in->nNodes); + + if (in->level == 0) + { + float8 inf = get_float8_infinity(); + + area = box_fill(&infArea, -inf, inf, -inf, inf); + } + else + { + area = (BOX *) in->traversalValue; + Assert(area); + } + + boxes[0].low = area->low; + boxes[1].high = area->high; + + if (in->level % 2) + { + /* split box by x */ + boxes[0].high.x = boxes[1].low.x = coord; + boxes[0].high.y = area->high.y; + boxes[1].low.y = area->low.y; + } + else + { + /* split box by y */ + boxes[0].high.y = boxes[1].low.y = coord; + boxes[0].high.x = area->high.x; + boxes[1].low.x = area->low.x; + } + } + for (i = 1; i <= 2; i++) { if (which & (1 << i)) - out->nodeNumbers[out->nNodes++] = i - 1; + { + out->nodeNumbers[out->nNodes] = i - 1; + + if (in->norderbys > 0) + { + MemoryContext oldCtx = MemoryContextSwitchTo( + in->traversalMemoryContext); + BOX *box = box_copy(&boxes[i - 1]); + + MemoryContextSwitchTo(oldCtx); + + out->traversalValues[out->nNodes] = box; + + spg_point_distance(BoxPGetDatum(box), + in->norderbys, in->orderbyKeys, + &out->distances[out->nNodes], false); + } + + out->nNodes++; + } } /* Set up level increments, too */ diff --git a/src/backend/access/spgist/spgproc.c b/src/backend/access/spgist/spgproc.c new file mode 100644 index 0000000..23a8d09 --- /dev/null +++ b/src/backend/access/spgist/spgproc.c @@ -0,0 +1,68 @@ +/*------------------------------------------------------------------------- + * + * spgproc.c + * Common procedures for SP-GiST. + * + * + * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/access/spgist/spgproc.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include + +#include "access/spgist_private.h" +#include "utils/builtins.h" +#include "utils/geo_decls.h" + +/* Point-box distance in the assumption that box is aligned by axis */ +static double +point_box_distance(Point *point, BOX *box) +{ + double dx, + dy; + + if (isnan(point->x) || isnan(box->low.x) || + isnan(point->y) || isnan(box->low.y)) + return get_float8_nan(); + + if (point->x < box->low.x) + dx = box->low.x - point->x; + else if (point->x > box->high.x) + dx = point->x - box->high.x; + else + dx = 0.0; + + if (point->y < box->low.y) + dy = box->low.y - point->y; + else if (point->y > box->high.y) + dy = point->y - box->high.y; + else + dy = 0.0; + + return HYPOT(dx, dy); +} + +void +spg_point_distance(Datum to, int norderbys, ScanKey orderbyKeys, + double **distances, bool isLeaf) +{ + double *distance; + int sk_num; + + distance = *distances = (double *) palloc(norderbys * sizeof(double)); + + for (sk_num = 0; sk_num < norderbys; ++sk_num, ++orderbyKeys, ++distance) + { + Point *point = DatumGetPointP(orderbyKeys->sk_argument); + + *distance = isLeaf ? point_dt(point, DatumGetPointP(to)) + : point_box_distance(point, DatumGetBoxP(to)); + } +} diff --git a/src/backend/access/spgist/spgquadtreeproc.c b/src/backend/access/spgist/spgquadtreeproc.c index 8700ff3..7883ad4 100644 --- a/src/backend/access/spgist/spgquadtreeproc.c +++ b/src/backend/access/spgist/spgquadtreeproc.c @@ -17,6 +17,7 @@ #include "access/spgist.h" #include "access/stratnum.h" +#include "access/spgist_private.h" #include "catalog/pg_type.h" #include "utils/builtins.h" #include "utils/geo_decls.h" @@ -77,6 +78,32 @@ getQuadrant(Point *centroid, Point *tst) return 0; } +/* Returns bounding box of a given quadrant */ +static BOX * +getQuadrantArea(BOX *area, Point *centroid, int quadrant) +{ + BOX *box = (BOX *) palloc(sizeof(BOX)); + + switch (quadrant) + { + case 1: + box->high = area->high; + box->low = *centroid; + break; + case 2: + box_fill(box, centroid->x, area->high.x, area->low.y, centroid->y); + break; + case 3: + box->high = *centroid; + box->low = area->low; + break; + case 4: + box_fill(box, area->low.x, centroid->x, centroid->y, area->high.y); + break; + } + + return box; +} Datum spg_quad_choose(PG_FUNCTION_ARGS) @@ -196,19 +223,56 @@ spg_quad_inner_consistent(PG_FUNCTION_ARGS) spgInnerConsistentIn *in = (spgInnerConsistentIn *) PG_GETARG_POINTER(0); spgInnerConsistentOut *out = (spgInnerConsistentOut *) PG_GETARG_POINTER(1); Point *centroid; + BOX infArea; + BOX *area = NULL; int which; int i; Assert(in->hasPrefix); centroid = DatumGetPointP(in->prefixDatum); + if (in->norderbys > 0) + { + out->distances = (double **) palloc(sizeof(double *) * in->nNodes); + out->traversalValues = (void **) palloc(sizeof(void *) * in->nNodes); + + if (in->level == 0) + { + double inf = get_float8_infinity(); + + area = box_fill(&infArea, -inf, inf, -inf, inf); + } + else + { + area = in->traversalValue; + Assert(area); + } + } + if (in->allTheSame) { /* Report that all nodes should be visited */ out->nNodes = in->nNodes; out->nodeNumbers = (int *) palloc(sizeof(int) * in->nNodes); for (i = 0; i < in->nNodes; i++) + { out->nodeNumbers[i] = i; + + if (in->norderbys > 0) + { + MemoryContext oldCtx = MemoryContextSwitchTo( + in->traversalMemoryContext); + /* Use parent quadrant box as traversalValue */ + BOX *quadrant = box_copy(area); + + MemoryContextSwitchTo(oldCtx); + + out->traversalValues[i] = quadrant; + spg_point_distance(BoxPGetDatum(quadrant), + in->norderbys, in->orderbyKeys, + &out->distances[i], false); + } + } PG_RETURN_VOID(); } @@ -286,13 +350,37 @@ spg_quad_inner_consistent(PG_FUNCTION_ARGS) break; /* no need to consider remaining conditions */ } + out->levelAdds = palloc(sizeof(int) * 4); + for (i = 0; i < 4; ++i) + out->levelAdds[i] = 1; + /* We must descend into the quadrant(s) identified by which */ out->nodeNumbers = (int *) palloc(sizeof(int) * 4); out->nNodes = 0; + for (i = 1; i <= 4; i++) { if (which & (1 << i)) - out->nodeNumbers[out->nNodes++] = i - 1; + { + out->nodeNumbers[out->nNodes] = i - 1; + + if (in->norderbys > 0) + { + MemoryContext oldCtx = MemoryContextSwitchTo( + in->traversalMemoryContext); + BOX *quadrant = getQuadrantArea(area, centroid, i); + + MemoryContextSwitchTo(oldCtx); + + out->traversalValues[out->nNodes] = quadrant; + + spg_point_distance(BoxPGetDatum(quadrant), + in->norderbys, in->orderbyKeys, + &out->distances[out->nNodes], false); + } + + out->nNodes++; + } } PG_RETURN_VOID(); @@ -356,5 +444,10 @@ spg_quad_leaf_consistent(PG_FUNCTION_ARGS) break; } + if (res && in->norderbys > 0) + /* ok, it passes -> let's compute the distances */ + spg_point_distance(in->leafDatum, + in->norderbys, in->orderbykeys, &out->distances, true); + PG_RETURN_BOOL(res); } diff --git a/src/include/access/spgist_private.h b/src/include/access/spgist_private.h index 7d11225..3874a04 100644 --- a/src/include/access/spgist_private.h +++ b/src/include/access/spgist_private.h @@ -457,4 +457,8 @@ extern void spgPageIndexMultiDelete(SpGistState *state, Page page, extern bool spgdoinsert(Relation index, SpGistState *state, ItemPointer heapPtr, Datum datum, bool isnull); +/* spgproc.c */ +extern void spg_point_distance(Datum to, int norderbys, + ScanKey orderbyKeys, double **distances, bool isLeaf); + #endif /* SPGIST_PRIVATE_H */ diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat index fb58f77..f8f149e 100644 --- a/src/include/catalog/pg_amop.dat +++ b/src/include/catalog/pg_amop.dat @@ -1401,6 +1401,10 @@ { amopfamily => 'spgist/quad_point_ops', amoplefttype => 'point', amoprighttype => 'box', amopstrategy => '8', amopopr => '<@(point,box)', amopmethod => 'spgist' }, +{ amopfamily => 'spgist/quad_point_ops', amoplefttype => 'point', + amoprighttype => 'point', amopstrategy => '15', amopopr => '<->(point,point)', + amopmethod => 'spgist', amoppurpose => 'o', + amopsortfamily => 'btree/float_ops' }, # SP-GiST kd_point_ops { amopfamily => 'spgist/kd_point_ops', amoplefttype => 'point', @@ -1421,6 +1425,10 @@ { amopfamily => 'spgist/kd_point_ops', amoplefttype => 'point', amoprighttype => 'box', amopstrategy => '8', amopopr => '<@(point,box)', amopmethod => 'spgist' }, +{ amopfamily => 'spgist/kd_point_ops', amoplefttype => 'point', + amoprighttype => 'point', amopstrategy => '15', amopopr => '<->(point,point)', + amopmethod => 'spgist', amoppurpose => 'o', + amopsortfamily => 'btree/float_ops' }, # SP-GiST text_ops { amopfamily => 'spgist/text_ops', amoplefttype => 'text', diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out index 24cd3c5..4570a39 100644 --- a/src/test/regress/expected/amutils.out +++ b/src/test/regress/expected/amutils.out @@ -83,7 +83,8 @@ select prop, pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree, pg_index_column_has_property('hash_i4_index'::regclass, 1, prop) as hash, pg_index_column_has_property('gcircleind'::regclass, 1, prop) as gist, - pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist, + pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist_radix, + pg_index_column_has_property('sp_quad_ind'::regclass, 1, prop) as spgist_quad, pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin, pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', @@ -92,18 +93,18 @@ select prop, 'bogus']::text[]) with ordinality as u(prop,ord) order by ord; - prop | btree | hash | gist | spgist | gin | brin ---------------------+-------+------+------+--------+-----+------ - asc | t | f | f | f | f | f - desc | f | f | f | f | f | f - nulls_first | f | f | f | f | f | f - nulls_last | t | f | f | f | f | f - orderable | t | f | f | f | f | f - distance_orderable | f | f | t | f | f | f - returnable | t | f | f | t | f | f - search_array | t | f | f | f | f | f - search_nulls | t | f | t | t | f | t - bogus | | | | | | + prop | btree | hash | gist | spgist_radix | spgist_quad | gin | brin +--------------------+-------+------+------+--------------+-------------+-----+------ + asc | t | f | f | f | f | f | f + desc | f | f | f | f | f | f | f + nulls_first | f | f | f | f | f | f | f + nulls_last | t | f | f | f | f | f | f + orderable | t | f | f | f | f | f | f + distance_orderable | f | f | t | f | t | f | f + returnable | t | f | f | t | t | f | f + search_array | t | f | f | f | f | f | f + search_nulls | t | f | t | t | t | f | t + bogus | | | | | | | (10 rows) select prop, diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index fc81088..b513489 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -294,6 +294,15 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 1 (1 row) +CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl; +CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM quad_point_tbl WHERE p IS NOT NULL; SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; count ------- @@ -889,6 +898,74 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; (1 row) EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Order By: (p <-> '(0,0)'::point) +(3 rows) + +CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl; +SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx +ON seq.n = idx.n AND seq.dist = idx.dist AND seq.p ~= idx.p +WHERE seq.dist IS NULL OR idx.dist IS NULL; + n | dist | p | n | dist | p +-------+------+---+-------+------+--- + 11001 | | | | | + 11001 | | | | | + 11001 | | | | | + | | | 11001 | | + | | | 11001 | | + | | | 11001 | | +(6 rows) + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) + Order By: (p <-> '(0,0)'::point) +(4 rows) + +CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx +ON seq.n = idx.n AND seq.dist = idx.dist AND seq.p ~= idx.p +WHERE seq.dist IS NULL OR idx.dist IS NULL; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM quad_point_tbl WHERE p IS NOT NULL; + QUERY PLAN +----------------------------------------------------------- + WindowAgg + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p IS NOT NULL) + Order By: (p <-> '(333,400)'::point) +(4 rows) + +CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM quad_point_tbl WHERE p IS NOT NULL; +SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx +ON seq.n = idx.n AND seq.dist = idx.dist AND seq.p ~= idx.p +WHERE seq.dist IS NULL OR idx.dist IS NULL; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; QUERY PLAN --------------------------------------------------------- @@ -994,6 +1071,71 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; (1 row) EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl; + QUERY PLAN +------------------------------------------------------- + WindowAgg + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Order By: (p <-> '(0,0)'::point) +(3 rows) + +CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl; +SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx +ON seq.n = idx.n AND +(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL) +WHERE seq.n IS NULL OR idx.n IS NULL; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------- + WindowAgg + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) + Order By: (p <-> '(0,0)'::point) +(4 rows) + +CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx +ON seq.n = idx.n AND +(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL) +WHERE seq.n IS NULL OR idx.n IS NULL; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM kd_point_tbl WHERE p IS NOT NULL; + QUERY PLAN +------------------------------------------------------- + WindowAgg + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p IS NOT NULL) + Order By: (p <-> '(333,400)'::point) +(4 rows) + +CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM kd_point_tbl WHERE p IS NOT NULL; +SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx +ON seq.n = idx.n AND +(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL) +WHERE seq.n IS NULL OR idx.n IS NULL; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; QUERY PLAN ------------------------------------------------------------ diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 3c6d853..7bcc03b 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1911,6 +1911,7 @@ ORDER BY 1, 2, 3; 4000 | 12 | <= 4000 | 12 | |&> 4000 | 14 | >= + 4000 | 15 | <-> 4000 | 15 | > 4000 | 16 | @> 4000 | 18 | = @@ -1924,7 +1925,7 @@ ORDER BY 1, 2, 3; 4000 | 26 | >> 4000 | 27 | >>= 4000 | 28 | ^@ -(122 rows) +(123 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing diff --git a/src/test/regress/sql/amutils.sql b/src/test/regress/sql/amutils.sql index 8ca85ec..06e7fa1 100644 --- a/src/test/regress/sql/amutils.sql +++ b/src/test/regress/sql/amutils.sql @@ -40,7 +40,8 @@ select prop, pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree, pg_index_column_has_property('hash_i4_index'::regclass, 1, prop) as hash, pg_index_column_has_property('gcircleind'::regclass, 1, prop) as gist, - pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist, + pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist_radix, + pg_index_column_has_property('sp_quad_ind'::regclass, 1, prop) as spgist_quad, pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin, pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index f9e7118..f4418d5 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -198,6 +198,18 @@ SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; +CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl; + +CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + +CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM quad_point_tbl WHERE p IS NOT NULL; + SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; @@ -364,6 +376,36 @@ SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl; +CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl; +SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx +ON seq.n = idx.n AND seq.dist = idx.dist AND seq.p ~= idx.p +WHERE seq.dist IS NULL OR idx.dist IS NULL; + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx +ON seq.n = idx.n AND seq.dist = idx.dist AND seq.p ~= idx.p +WHERE seq.dist IS NULL OR idx.dist IS NULL; + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM quad_point_tbl WHERE p IS NOT NULL; +CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM quad_point_tbl WHERE p IS NOT NULL; +SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx +ON seq.n = idx.n AND seq.dist = idx.dist AND seq.p ~= idx.p +WHERE seq.dist IS NULL OR idx.dist IS NULL; + +EXPLAIN (COSTS OFF) SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; @@ -392,6 +434,39 @@ SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl; +CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl; +SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx +ON seq.n = idx.n AND +(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL) +WHERE seq.n IS NULL OR idx.n IS NULL; + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS +SELECT rank() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p +FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; +SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx +ON seq.n = idx.n AND +(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL) +WHERE seq.n IS NULL OR idx.n IS NULL; + +EXPLAIN (COSTS OFF) +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM kd_point_tbl WHERE p IS NOT NULL; +CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS +SELECT rank() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p +FROM kd_point_tbl WHERE p IS NOT NULL; +SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx +ON seq.n = idx.n AND +(seq.dist = idx.dist AND seq.p ~= idx.p OR seq.p IS NULL AND idx.p IS NULL) +WHERE seq.n IS NULL OR idx.n IS NULL; + +EXPLAIN (COSTS OFF) SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';