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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
-- Tests for disable index hints
LOAD 'pg_hint_plan';
SET search_path TO public;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET pg_hint_plan.enable_hint TO on;
CREATE SCHEMA disable_index;
-- Single table
CREATE TABLE disable_index.t1 (c1 int, c2 int, PRIMARY KEY (c1));
CREATE INDEX t1_i1 ON disable_index.t1(c1);
INSERT INTO disable_index.t1 SELECT i, i % 10
FROM (SELECT generate_series(0, 300) i) t;
-- Tables with inheritance with indexes
CREATE TABLE disable_index.p3 (c1 int, c2 int, c3 int, c4 int, PRIMARY KEY (c1));
CREATE TABLE disable_index.p3_1_100 (CHECK (c4 >= 1 AND c4 < 101))
INHERITS (disable_index.p3);
CREATE TABLE disable_index.p3_100_200 (CHECK (c4 >= 101 AND c4 < 201))
INHERITS (disable_index.p3);
CREATE TABLE disable_index.p3_200_300 (CHECK (c4 >= 201 AND c4 < 301))
INHERITS (disable_index.p3);
INSERT INTO disable_index.p3 SELECT i, i, i % 10, i
FROM (SELECT generate_series(0, 300) i) t;
CREATE INDEX p3_c1_idx ON disable_index.p3(c1);
CREATE INDEX p3_1_100_c1_c2_idx ON disable_index.p3_1_100(c1, c2);
CREATE INDEX p3_1_100_c1_idx ON disable_index.p3_1_100(c1);
CREATE INDEX p3_100_200_c1_idx ON disable_index.p3_100_200(c1);
CREATE INDEX p3_200_300_c1_idx ON disable_index.p3_200_300(c1);
-- Partitioned tables and partitions
CREATE TABLE disable_index.pt2 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1, c2))
PARTITION BY RANGE(c1);
CREATE TABLE disable_index.pt2_0_1 PARTITION OF disable_index.pt2
FOR VALUES FROM (MINVALUE) TO (101) PARTITION BY RANGE (c2);
CREATE TABLE disable_index.pt2_0_2 PARTITION OF disable_index.pt2
FOR VALUES FROM (101) TO (201) PARTITION BY RANGE (c2);
CREATE TABLE disable_index.pt2_0_3 PARTITION OF disable_index.pt2
FOR VALUES FROM (201) TO (MAXVALUE) PARTITION BY RANGE (c2);
CREATE TABLE disable_index.pt2_1_1 PARTITION OF disable_index.pt2_0_1
FOR VALUES FROM (MINVALUE) TO (151);
CREATE TABLE disable_index.pt2_1_2 PARTITION OF disable_index.pt2_0_1
FOR VALUES FROM (151) TO (MAXVALUE);
CREATE TABLE disable_index.pt2_1_3 PARTITION OF disable_index.pt2_0_2
FOR VALUES FROM (MINVALUE) TO (151);
CREATE TABLE disable_index.pt2_1_4 PARTITION OF disable_index.pt2_0_2
FOR VALUES FROM (151) TO (MAXVALUE);
CREATE TABLE disable_index.pt2_1_5 PARTITION OF disable_index.pt2_0_3
FOR VALUES FROM (MINVALUE) TO (151);
CREATE TABLE disable_index.pt2_1_6 PARTITION OF disable_index.pt2_0_3
FOR VALUES FROM (151) TO (MAXVALUE);
INSERT INTO disable_index.pt2 SELECT i, i, i % 10, i
FROM (SELECT generate_series(0, 300) i) t;
-- Extra index, created on all partitions.
CREATE INDEX pt2_c1_idx ON disable_index.pt2 (c1, c2);
-- Hint conflicts
/*+ DisableIndex(t1) */
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
/*+DisableIndex(t1 t1_pkey)DisableIndex(t1 t1_i1)*/
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
-- non-existent index
/*+ DisableIndex(pt2 non_existent) */
EXPLAIN (COSTS OFF) SELECT * FROM disable_index.pt2 WHERE c1 IN (1, 101, 201);
-- DisableIndex + Scan method hint
/*+
DisableIndex(t1 t1_i1)
IndexScan(t1 t1_i1)
*/
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
/*+
DisableIndex(t1 t1_pkey)
NoIndexScan(t1)
*/
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
-- Regular tables
/*+ DisableIndex(t1 t1_i1) */
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
/*+ DisableIndex(t1 t1_pkey) */
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
/*+ DisableIndex(t1 t1_pkey t1_i1) */
EXPLAIN (COSTS false) SELECT * FROM disable_index.t1 WHERE c1 = 1;
-- Partitioning
SET enable_bitmapscan = OFF;
SET enable_seqscan = OFF;
-- Disable parallelism.
SET max_parallel_workers_per_gather = 0;
-- Push down the parent hint
/*+ DisableIndex(pt2 pt2_c1_idx) */
EXPLAIN (COSTS OFF) SELECT * FROM disable_index.pt2 WHERE c1 IN (1, 101, 201);
/*+ DisableIndex(p3 p3_c1_idx) */
EXPLAIN (COSTS OFF) SELECT * FROM disable_index.p3 WHERE c1 IN (1, 101, 201);
-- Specific partition
/*+
DisableIndex(pt2_1_1 pt2_1_1_c1_c2_idx)
DisableIndex(pt2_1_6 pt2_1_6_c1_c2_idx)
*/
EXPLAIN (COSTS OFF) SELECT * FROM disable_index.pt2 WHERE c1 IN (1, 101, 201);
/*+
DisableIndex(p3_1_100 p3_1_100_c1_c2_idx)
*/
EXPLAIN (COSTS OFF) SELECT * FROM disable_index.p3 WHERE c1 IN (1, 101, 201);
RESET enable_bitmapscan;
RESET enable_seqscan;
RESET max_parallel_workers_per_gather;
-- Cleanup
DROP SCHEMA disable_index CASCADE;
|
