contains_ops contains_ops_compat bounding_box_gist gnomo healpix\

moc

REGRESS_9_5 = index_9.5# experimental for spoint3

REGRESS_9_5 = index_9.5knn# experimental for spoint3

TESTS = init_test tables points euler circle line ellipse poly path box index\

contains_ops contains_ops_compat bounding_box_gist gnomo healpix\

ifeq ($(pg_version_9_5_plus),y)

REGRESS += $(REGRESS_9_5)

TESTS += $(REGRESS_9_5)

PGS_SQL += $(PGS_SQL_9_5)

crushtest: REGRESS +=$(CRUSH_TESTS)

crushtest: installcheck

SET enable_bitmapscan = OFF;

SET enable_indexonlyscan = ON;

EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p <@ scircle '<(1,1),0.3>';

QUERY PLAN

--------------------------------------------------------

QUERY PLAN

-------------------------------------------------------

Aggregate

-> IndexOnlyScan using spoint3_idx on spheretmp1b

-> Index Scan using spoint3_idx on spheretmp1b

Index Cond: (p <@ '<(1 , 1) , 0.3>'::scircle)

(3 rows)

(1 row)

EXPLAIN (COSTS OFF) SELECT count(*) FROM spheretmp1b WHERE p = spoint '(3.09 , 1.25)';

QUERY PLAN

--------------------------------------------------------

QUERY PLAN

---------------------------------------------------

Aggregate

-> IndexOnlyScan using spoint3_idx on spheretmp1b

-> Index Scan using spoint3_idx on spheretmp1b

Index Cond: (p = '(3.09 , 1.25)'::spoint)

(3 rows)

CREATE TABLE points (id int, p spoint, pos int);

INSERT INTO points (id, p) SELECT x, spoint(random()*6.28, (2*random()-1)*1.57) FROM generate_series(1,314159) x;

CREATE INDEX i ON points USING gist (p spoint3);

SET enable_indexscan = true;

EXPLAIN (costs off) SELECT p <-> spoint (0.2, 0.3) FROM points ORDER BY 1 LIMIT 10;

QUERY PLAN

-------------------------------------------------

Limit

-> Index Scan using i on points

Order By: (p <-> '(0.2 , 0.3)'::spoint)

(3 rows)

UPDATE points SET pos = n FROM (SELECT id, row_number() OVER (ORDER BY p <-> spoint (0.2, 0.3)) n FROM points ORDER BY p <-> spoint (0.2, 0.3) LIMIT 10) sel WHERE points.id = sel.id;

SET enable_indexscan = false;

SELECT pos, row_number() OVER (ORDER BY p <-> spoint (0.2, 0.3)) n FROM points ORDER BY p <-> spoint (0.2, 0.3) LIMIT 10;

pos | n

-----+----

1 | 1

2 | 2

3 | 3

4 | 4

5 | 5

6 | 6

7 | 7

8 | 8

9 | 9

10 | 10

(10 rows)

DROP TABLE points;

CREATETABLEpoints (idint, p spoint, posint);

INSERT INTO points (id, p)SELECT x, spoint(random()*6.28, (2*random()-1)*1.57)FROM generate_series(1,314159) x;

CREATEINDEXiON points USING gist (p spoint3);

SET enable_indexscan= true;

EXPLAIN (costs off)SELECT p<-> spoint (0.2,0.3)FROM pointsORDER BY1LIMIT10;

UPDATE pointsSET pos= nFROM (SELECT id, row_number() OVER (ORDER BY p<-> spoint (0.2,0.3)) nFROM pointsORDER BY p<-> spoint (0.2,0.3)LIMIT10) selWHEREpoints.id=sel.id;

SET enable_indexscan= false;

SELECT pos, row_number() OVER (ORDER BY p<-> spoint (0.2,0.3)) nFROM pointsORDER BY p<-> spoint (0.2,0.3)LIMIT10;

DROPTABLE points;