diff options
Diffstat (limited to 'src/test/regress/expected/create_index_spgist.out')
| -rw-r--r-- | src/test/regress/expected/create_index_spgist.out | 1371 |
1 files changed, 1371 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_index_spgist.out b/src/test/regress/expected/create_index_spgist.out new file mode 100644 index 0000000..5c04df9 --- /dev/null +++ b/src/test/regress/expected/create_index_spgist.out @@ -0,0 +1,1371 @@ +-- +-- SP-GiST index tests +-- +CREATE TABLE quad_point_tbl AS + SELECT point(unique1,unique2) AS p FROM tenk1; +INSERT INTO quad_point_tbl + SELECT '(333.0,400.0)'::point FROM generate_series(1,1000); +INSERT INTO quad_point_tbl VALUES (NULL), (NULL), (NULL); +CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p); +CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl; +CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops); +CREATE TABLE radix_text_tbl AS + SELECT name AS t FROM road WHERE name !~ '^[0-9]'; +INSERT INTO radix_text_tbl + SELECT 'P0123456789abcdef' FROM generate_series(1,1000); +INSERT INTO radix_text_tbl VALUES ('P0123456789abcde'); +INSERT INTO radix_text_tbl VALUES ('P0123456789abcdefF'); +CREATE INDEX sp_radix_ind ON radix_text_tbl USING spgist (t); +-- get non-indexed results for comparison purposes +SET enable_seqscan = ON; +SET enable_indexscan = OFF; +SET enable_bitmapscan = OFF; +SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; + count +------- + 3 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; + count +------- + 11000 +(1 row) + +SELECT count(*) FROM quad_point_tbl; + count +------- + 11003 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; + count +------- + 5000 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; + count +------- + 5999 +(1 row) + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS +SELECT row_number() 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 row_number() 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 row_number() 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 +------- + 1000 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; + count +------- + 1 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; + count +------- + 272 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; + count +------- + 272 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; + count +------- + 273 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + count +------- + 273 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; + count +------- + 1 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; + count +------- + 2 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; + count +------- + 50 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; + count +------- + 50 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; + count +------- + 48 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; + count +------- + 48 +(1 row) + +SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; + count +------- + 2 +(1 row) + +-- Now check the results from plain indexscan +SET enable_seqscan = OFF; +SET enable_indexscan = ON; +SET enable_bitmapscan = OFF; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p IS NULL) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; + count +------- + 3 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p IS NOT NULL) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; + count +------- + 11000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl +(2 rows) + +SELECT count(*) FROM quad_point_tbl; + count +------- + 11003 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p << '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p >> '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p <<| '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p |>> '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_quad_ind on quad_point_tbl + Index Cond: (p ~= '(4585,365)'::point) +(3 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT row_number() 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 row_number() 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 +WHERE seq.dist IS DISTINCT FROM idx.dist; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT row_number() 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 row_number() 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 +WHERE seq.dist IS DISTINCT FROM idx.dist; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT row_number() 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 row_number() 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 +WHERE seq.dist IS DISTINCT FROM idx.dist; + 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 +--------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p << '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p >> '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p <<| '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p |>> '(5000,4000)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_kd_ind on kd_point_tbl + Index Cond: (p ~= '(4585,365)'::point) +(3 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT row_number() 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 row_number() 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 +WHERE seq.dist IS DISTINCT FROM idx.dist; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT row_number() 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 row_number() 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 +WHERE seq.dist IS DISTINCT FROM idx.dist; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT row_number() 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 row_number() 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 +WHERE seq.dist IS DISTINCT FROM idx.dist; + n | dist | p | n | dist | p +---+------+---+---+------+--- +(0 rows) + +-- test KNN scan with included columns +-- the distance numbers are not exactly the same across platforms +SET extra_float_digits = 0; +CREATE INDEX ON quad_point_tbl_ord_seq1 USING spgist(p) INCLUDE(dist); +EXPLAIN (COSTS OFF) +SELECT p, dist FROM quad_point_tbl_ord_seq1 ORDER BY p <-> '0,0' LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------------------- + Limit + -> Index Only Scan using quad_point_tbl_ord_seq1_p_dist_idx on quad_point_tbl_ord_seq1 + Order By: (p <-> '(0,0)'::point) +(3 rows) + +SELECT p, dist FROM quad_point_tbl_ord_seq1 ORDER BY p <-> '0,0' LIMIT 10; + p | dist +-----------+------------------ + (59,21) | 62.6258732474047 + (88,104) | 136.235090927411 + (39,143) | 148.222805262888 + (139,160) | 211.945747775227 + (209,38) | 212.42645786248 + (157,156) | 221.325552072055 + (175,150) | 230.488611432322 + (236,34) | 238.436574375661 + (263,28) | 264.486294540946 + (322,53) | 326.33265236565 +(10 rows) + +RESET extra_float_digits; +-- check ORDER BY distance to NULL +SELECT (SELECT p FROM kd_point_tbl ORDER BY p <-> pt, p <-> '0,0' LIMIT 1) +FROM (VALUES (point '1,2'), (NULL), ('1234,5678')) pts(pt); + p +------------- + (59,21) + (59,21) + (1239,5647) +(3 rows) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t = 'P0123456789abcdef'::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; + count +------- + 1000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t = 'P0123456789abcde'::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t = 'P0123456789abcdefF'::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t < 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; + count +------- + 272 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t ~<~ 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; + count +------- + 272 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t <= 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; + count +------- + 273 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t ~<=~ 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + count +------- + 273 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t = 'Aztec Ct '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t = 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; + count +------- + 2 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; + QUERY PLAN +----------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t >= 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t ~>=~ 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------- + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t > 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; + count +------- + 48 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t ~>~ 'Worth St '::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; + count +------- + 48 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t ^@ 'Worth'::text) +(3 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; + count +------- + 2 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth'); + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Index Only Scan using sp_radix_ind on radix_text_tbl + Index Cond: (t ^@ 'Worth'::text) + Filter: starts_with(t, 'Worth'::text) +(4 rows) + +SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth'); + count +------- + 2 +(1 row) + +-- Now check the results from bitmap indexscan +SET enable_seqscan = OFF; +SET enable_indexscan = OFF; +SET enable_bitmapscan = ON; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; + QUERY PLAN +---------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p IS NULL) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p IS NULL) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; + count +------- + 3 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; + QUERY PLAN +---------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p IS NOT NULL) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p IS NOT NULL) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; + count +------- + 11000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl; + QUERY PLAN +---------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + -> Bitmap Index Scan on sp_quad_ind +(3 rows) + +SELECT count(*) FROM quad_point_tbl; + count +------- + 11003 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: ('(1000,1000),(200,200)'::box @> p) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p << '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p << '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p >> '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p >> '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p <<| '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p <<| '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p |>> '(5000,4000)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p |>> '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on quad_point_tbl + Recheck Cond: (p ~= '(4585,365)'::point) + -> Bitmap Index Scan on sp_quad_ind + Index Cond: (p ~= '(4585,365)'::point) +(5 rows) + +SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: ('(1000,1000),(200,200)'::box @> p) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p <@ '(1000,1000),(200,200)'::box) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; + count +------- + 1057 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p << '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p << '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; + count +------- + 6000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + QUERY PLAN +------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p >> '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p >> '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; + count +------- + 4999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p <<| '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p <<| '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; + count +------- + 5000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; + QUERY PLAN +-------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p |>> '(5000,4000)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p |>> '(5000,4000)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; + count +------- + 5999 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + QUERY PLAN +------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on kd_point_tbl + Recheck Cond: (p ~= '(4585,365)'::point) + -> Bitmap Index Scan on sp_kd_ind + Index Cond: (p ~= '(4585,365)'::point) +(5 rows) + +SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; + QUERY PLAN +----------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t = 'P0123456789abcdef'::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t = 'P0123456789abcdef'::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; + count +------- + 1000 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t = 'P0123456789abcde'::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t = 'P0123456789abcde'::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t = 'P0123456789abcdefF'::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t = 'P0123456789abcdefF'::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t < 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t < 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; + count +------- + 272 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t ~<~ 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t ~<~ 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; + count +------- + 272 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; + QUERY PLAN +----------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t <= 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t <= 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; + count +------- + 273 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + QUERY PLAN +------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t ~<=~ 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t ~<=~ 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; + count +------- + 273 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t = 'Aztec Ct '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t = 'Aztec Ct '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; + count +------- + 1 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t = 'Worth St '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t = 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; + count +------- + 2 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; + QUERY PLAN +----------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t >= 'Worth St '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t >= 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t ~>=~ 'Worth St '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t ~>=~ 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; + count +------- + 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; + QUERY PLAN +---------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t > 'Worth St '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t > 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; + count +------- + 48 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t ~>~ 'Worth St '::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t ~>~ 'Worth St '::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; + count +------- + 48 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; + QUERY PLAN +------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Recheck Cond: (t ^@ 'Worth'::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t ^@ 'Worth'::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; + count +------- + 2 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth'); + QUERY PLAN +------------------------------------------------ + Aggregate + -> Bitmap Heap Scan on radix_text_tbl + Filter: starts_with(t, 'Worth'::text) + -> Bitmap Index Scan on sp_radix_ind + Index Cond: (t ^@ 'Worth'::text) +(5 rows) + +SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth'); + count +------- + 2 +(1 row) + +RESET enable_seqscan; +RESET enable_indexscan; +RESET enable_bitmapscan; |