1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
--
-- Create access method tests
--
-- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
-- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist
CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
ERROR: data type box has no default operator class for access method "gist2"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
-- Make operator class for boxes using gist2
CREATE OPERATOR CLASS box_ops DEFAULT
FOR TYPE box USING gist2 AS
OPERATOR 1 <<,
OPERATOR 2 &<,
OPERATOR 3 &&,
OPERATOR 4 &>,
OPERATOR 5 >>,
OPERATOR 6 ~=,
OPERATOR 7 @>,
OPERATOR 8 <@,
OPERATOR 9 &<|,
OPERATOR 10 <<|,
OPERATOR 11 |>>,
OPERATOR 12 |&>,
OPERATOR 13 ~,
OPERATOR 14 @,
FUNCTION 1 gist_box_consistent(internal, box, smallint, oid, internal),
FUNCTION 2 gist_box_union(internal, internal),
FUNCTION 3 gist_box_compress(internal),
FUNCTION 4 gist_box_decompress(internal),
FUNCTION 5 gist_box_penalty(internal, internal, internal),
FUNCTION 6 gist_box_picksplit(internal, internal),
FUNCTION 7 gist_box_same(box, box, internal),
FUNCTION 9 gist_box_fetch(internal);
-- Create gist2 index on fast_emp4000
CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
-- Now check the results from plain indexscan; temporarily drop existing
-- index grect2ind to ensure it doesn't capture the plan
BEGIN;
DROP INDEX grect2ind;
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
EXPLAIN (COSTS OFF)
SELECT * FROM fast_emp4000
WHERE home_base @ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
QUERY PLAN
----------------------------------------------------------------
Sort
Sort Key: ((home_base[0])[0])
-> Index Only Scan using grect2ind2 on fast_emp4000
Index Cond: (home_base @ '(2000,1000),(200,200)'::box)
(4 rows)
SELECT * FROM fast_emp4000
WHERE home_base @ '(200,200),(2000,1000)'::box
ORDER BY (home_base[0])[0];
home_base
-----------------------
(337,455),(240,359)
(1444,403),(1346,344)
(2 rows)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
QUERY PLAN
-------------------------------------------------------------
Aggregate
-> Index Only Scan using grect2ind2 on fast_emp4000
Index Cond: (home_base && '(1000,1000),(0,0)'::box)
(3 rows)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
count
-------
2
(1 row)
EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
QUERY PLAN
--------------------------------------------------------
Aggregate
-> Index Only Scan using grect2ind2 on fast_emp4000
Index Cond: (home_base IS NULL)
(3 rows)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
count
-------
278
(1 row)
ROLLBACK;
-- Try to drop access method: fail because of dependent objects
DROP ACCESS METHOD gist2;
ERROR: cannot drop access method gist2 because other objects depend on it
DETAIL: index grect2ind2 depends on operator class box_ops for access method gist2
HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- Drop access method cascade
DROP ACCESS METHOD gist2 CASCADE;
NOTICE: drop cascades to index grect2ind2
|
