| -- |
| -- SP-GiST index tests |
| -- |
| CREATE TABLE quad_point_tbl AS |
| SELECT point(unique1,unique2) AS p FROM tenk1; |
| ANALYZE quad_point_tbl; |
| 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'); |
| ANALYZE radix_text_tbl; |
| 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) |
| |
| set optimizer_enable_tablescan = OFF; |
| -- 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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p IS NULL) |
| Optimizer: Postgres query optimizer |
| (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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p IS NOT NULL) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Optimizer: Postgres query optimizer |
| (5 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p <@ '(1000,1000),(200,200)'::box) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p <@ '(1000,1000),(200,200)'::box) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p << '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p >> '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p <<| '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p |>> '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p ~= '(4585,365)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| Order By: ((p <-> '(0,0)'::point)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(0,0)'::point)) |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Order By: (p <-> '(0,0)'::point) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| Order By: ((p <-> '(0,0)'::point)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(0,0)'::point)) |
| -> 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| Order By: ((p <-> '(333,400)'::point)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(333,400)'::point)) |
| -> Index Only Scan using sp_quad_ind on quad_point_tbl |
| Index Cond: (p IS NOT NULL) |
| Order By: (p <-> '(333,400)'::point) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| --------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p <@ '(1000,1000),(200,200)'::box) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| --------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p <@ '(1000,1000),(200,200)'::box) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p << '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p >> '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p <<| '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p |>> '(5000,4000)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p ~= '(4585,365)'::point) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| Order By: ((p <-> '(0,0)'::point)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(0,0)'::point)) |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Order By: (p <-> '(0,0)'::point) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| Order By: ((p <-> '(0,0)'::point)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(0,0)'::point)) |
| -> 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| Order By: ((p <-> '(333,400)'::point)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(333,400)'::point)) |
| -> Index Only Scan using sp_kd_ind on kd_point_tbl |
| Index Cond: (p IS NOT NULL) |
| Order By: (p <-> '(333,400)'::point) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((p <-> '(0,0)'::point)) |
| -> 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) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| ------------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t = 'P0123456789abcdef'::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t = 'P0123456789abcde'::text) |
| Optimizer: Postgres query optimizer |
| (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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t = 'P0123456789abcdefF'::text) |
| Optimizer: Postgres query optimizer |
| (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 |
| ---------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t < 'Aztec Ct '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t ~<~ 'Aztec Ct '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ----------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t <= 'Aztec Ct '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t ~<=~ 'Aztec Ct '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t = 'Aztec Ct '::text) |
| Optimizer: Postgres query optimizer |
| (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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t = 'Worth St '::text) |
| Optimizer: Postgres query optimizer |
| (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 |
| ----------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t >= 'Worth St '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t ~>=~ 'Worth St '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ---------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t > 'Worth St '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| ------------------------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t ~>~ 'Worth St '::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t ^@ 'Worth'::text) |
| Optimizer: Postgres query optimizer |
| (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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Index Only Scan using sp_radix_ind on radix_text_tbl |
| Index Cond: (t ^@ 'Worth'::text) |
| Filter: starts_with(t, 'Worth'::text) |
| Optimizer: Postgres query optimizer |
| (6 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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on quad_point_tbl |
| Recheck Cond: (p IS NULL) |
| -> Bitmap Index Scan on sp_quad_ind |
| Index Cond: (p IS NULL) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| ---------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ---------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Bitmap Heap Scan on quad_point_tbl |
| -> Bitmap Index Scan on sp_quad_ind |
| Optimizer: Postgres query optimizer |
| (6 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 |
| --------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| --------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| --------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| --------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ----------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Bitmap Heap Scan on radix_text_tbl |
| Recheck Cond: (t = 'P0123456789abcde'::text) |
| -> Bitmap Index Scan on sp_radix_ind |
| Index Cond: (t = 'P0123456789abcde'::text) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> Bitmap Heap Scan on radix_text_tbl |
| Recheck Cond: (t = 'P0123456789abcdefF'::text) |
| -> Bitmap Index Scan on sp_radix_ind |
| Index Cond: (t = 'P0123456789abcdefF'::text) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| ---------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ----------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> 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) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| -> Gather Motion 1:1 (slice1; segments: 1) |
| -> 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) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| ----------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ---------------------------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| ------------------------------------------------------------------------------------------ |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial 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) |
| Optimizer: Postgres query optimizer |
| (8 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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Bitmap Heap Scan on radix_text_tbl |
| Recheck Cond: (t ^@ 'Worth'::text) |
| -> Bitmap Index Scan on sp_radix_ind |
| Index Cond: (t ^@ 'Worth'::text) |
| Optimizer: Postgres query optimizer |
| (7 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 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> 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) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| SELECT count(*) FROM radix_text_tbl WHERE starts_with(t, 'Worth'); |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| RESET optimizer_enable_tablescan; |
| RESET enable_seqscan; |
| RESET enable_indexscan; |
| RESET enable_bitmapscan; |
