| blob | 7ab6113c61914f1c88609a0dd9db6accb53ddc8a |
1 --
2 -- Test index AM property-reporting functions
3 --
4 select prop,
5 pg_indexam_has_property(a.oid, prop) as "AM",
6 pg_index_has_property('onek_hundred'::regclass, prop) as "Index",
7 pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as "Column"
8 from pg_am a,
9 unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
10 'orderable', 'distance_orderable', 'returnable',
11 'search_array', 'search_nulls',
12 'clusterable', 'index_scan', 'bitmap_scan',
13 'backward_scan',
14 'can_order', 'can_unique', 'can_multi_col',
15 'can_exclude', 'can_include',
16 'bogus']::text[])
17 with ordinality as u(prop,ord)
18 where a.amname = 'btree'
19 order by ord;
20 prop | AM | Index | Column
21 --------------------+----+-------+--------
22 asc | | | t
23 desc | | | f
24 nulls_first | | | f
25 nulls_last | | | t
26 orderable | | | t
27 distance_orderable | | | f
28 returnable | | | t
29 search_array | | | t
30 search_nulls | | | t
31 clusterable | | t |
32 index_scan | | t |
33 bitmap_scan | | t |
34 backward_scan | | t |
35 can_order | t | |
36 can_unique | t | |
37 can_multi_col | t | |
38 can_exclude | t | |
39 can_include | t | |
40 bogus | | |
41 (19 rows)
43 select prop,
44 pg_indexam_has_property(a.oid, prop) as "AM",
45 pg_index_has_property('gcircleind'::regclass, prop) as "Index",
46 pg_index_column_has_property('gcircleind'::regclass, 1, prop) as "Column"
47 from pg_am a,
48 unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
49 'orderable', 'distance_orderable', 'returnable',
50 'search_array', 'search_nulls',
51 'clusterable', 'index_scan', 'bitmap_scan',
52 'backward_scan',
53 'can_order', 'can_unique', 'can_multi_col',
54 'can_exclude', 'can_include',
55 'bogus']::text[])
56 with ordinality as u(prop,ord)
57 where a.amname = 'gist'
58 order by ord;
59 prop | AM | Index | Column
60 --------------------+----+-------+--------
61 asc | | | f
62 desc | | | f
63 nulls_first | | | f
64 nulls_last | | | f
65 orderable | | | f
66 distance_orderable | | | t
67 returnable | | | f
68 search_array | | | f
69 search_nulls | | | t
70 clusterable | | t |
71 index_scan | | t |
72 bitmap_scan | | t |
73 backward_scan | | f |
74 can_order | f | |
75 can_unique | f | |
76 can_multi_col | t | |
77 can_exclude | t | |
78 can_include | t | |
79 bogus | | |
80 (19 rows)
82 select prop,
83 pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree,
84 pg_index_column_has_property('hash_i4_index'::regclass, 1, prop) as hash,
85 pg_index_column_has_property('gcircleind'::regclass, 1, prop) as gist,
86 pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist_radix,
87 pg_index_column_has_property('sp_quad_ind'::regclass, 1, prop) as spgist_quad,
88 pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin,
89 pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin
90 from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
91 'orderable', 'distance_orderable', 'returnable',
92 'search_array', 'search_nulls',
93 'bogus']::text[])
94 with ordinality as u(prop,ord)
95 order by ord;
96 prop | btree | hash | gist | spgist_radix | spgist_quad | gin | brin
97 --------------------+-------+------+------+--------------+-------------+-----+------
98 asc | t | f | f | f | f | f | f
99 desc | f | f | f | f | f | f | f
100 nulls_first | f | f | f | f | f | f | f
101 nulls_last | t | f | f | f | f | f | f
102 orderable | t | f | f | f | f | f | f
103 distance_orderable | f | f | t | f | t | f | f
104 returnable | t | f | f | t | t | f | f
105 search_array | t | f | f | f | f | f | f
106 search_nulls | t | f | t | t | t | f | t
107 bogus | | | | | | |
108 (10 rows)
110 select prop,
111 pg_index_has_property('onek_hundred'::regclass, prop) as btree,
112 pg_index_has_property('hash_i4_index'::regclass, prop) as hash,
113 pg_index_has_property('gcircleind'::regclass, prop) as gist,
114 pg_index_has_property('sp_radix_ind'::regclass, prop) as spgist,
115 pg_index_has_property('botharrayidx'::regclass, prop) as gin,
116 pg_index_has_property('brinidx'::regclass, prop) as brin
117 from unnest(array['clusterable', 'index_scan', 'bitmap_scan',
118 'backward_scan',
119 'bogus']::text[])
120 with ordinality as u(prop,ord)
121 order by ord;
122 prop | btree | hash | gist | spgist | gin | brin
123 ---------------+-------+------+------+--------+-----+------
124 clusterable | t | f | t | f | f | f
125 index_scan | t | t | t | t | f | f
126 bitmap_scan | t | t | t | t | t | t
127 backward_scan | t | t | f | f | f | f
128 bogus | | | | | |
129 (5 rows)
131 select amname, prop, pg_indexam_has_property(a.oid, prop) as p
132 from pg_am a,
133 unnest(array['can_order', 'can_unique', 'can_multi_col',
134 'can_exclude', 'can_include', 'bogus']::text[])
135 with ordinality as u(prop,ord)
136 where amtype = 'i'
137 order by amname, ord;
138 amname | prop | p
139 --------+---------------+---
140 brin | can_order | f
141 brin | can_unique | f
142 brin | can_multi_col | t
143 brin | can_exclude | f
144 brin | can_include | f
145 brin | bogus |
146 btree | can_order | t
147 btree | can_unique | t
148 btree | can_multi_col | t
149 btree | can_exclude | t
150 btree | can_include | t
151 btree | bogus |
152 gin | can_order | f
153 gin | can_unique | f
154 gin | can_multi_col | t
155 gin | can_exclude | f
156 gin | can_include | f
157 gin | bogus |
158 gist | can_order | f
159 gist | can_unique | f
160 gist | can_multi_col | t
161 gist | can_exclude | t
162 gist | can_include | t
163 gist | bogus |
164 hash | can_order | f
165 hash | can_unique | f
166 hash | can_multi_col | f
167 hash | can_exclude | t
168 hash | can_include | f
169 hash | bogus |
170 spgist | can_order | f
171 spgist | can_unique | f
172 spgist | can_multi_col | f
173 spgist | can_exclude | t
174 spgist | can_include | t
175 spgist | bogus |
176 (36 rows)
178 --
179 -- additional checks for pg_index_column_has_property
180 --
181 CREATE TEMP TABLE foo (f1 int, f2 int, f3 int, f4 int);
182 CREATE INDEX fooindex ON foo (f1 desc, f2 asc, f3 nulls first, f4 nulls last);
183 select col, prop, pg_index_column_has_property(o, col, prop)
184 from (values ('fooindex'::regclass)) v1(o),
185 (values (1,'orderable'),(2,'asc'),(3,'desc'),
186 (4,'nulls_first'),(5,'nulls_last'),
187 (6, 'bogus')) v2(idx,prop),
188 generate_series(1,4) col
189 order by col, idx;
190 col | prop | pg_index_column_has_property
191 -----+-------------+------------------------------
192 1 | orderable | t
193 1 | asc | f
194 1 | desc | t
195 1 | nulls_first | t
196 1 | nulls_last | f
197 1 | bogus |
198 2 | orderable | t
199 2 | asc | t
200 2 | desc | f
201 2 | nulls_first | f
202 2 | nulls_last | t
203 2 | bogus |
204 3 | orderable | t
205 3 | asc | t
206 3 | desc | f
207 3 | nulls_first | t
208 3 | nulls_last | f
209 3 | bogus |
210 4 | orderable | t
211 4 | asc | t
212 4 | desc | f
213 4 | nulls_first | f
214 4 | nulls_last | t
215 4 | bogus |
216 (24 rows)
218 CREATE INDEX foocover ON foo (f1) INCLUDE (f2,f3);
219 select col, prop, pg_index_column_has_property(o, col, prop)
220 from (values ('foocover'::regclass)) v1(o),
221 (values (1,'orderable'),(2,'asc'),(3,'desc'),
222 (4,'nulls_first'),(5,'nulls_last'),
223 (6,'distance_orderable'),(7,'returnable'),
224 (8, 'bogus')) v2(idx,prop),
225 generate_series(1,3) col
226 order by col, idx;
227 col | prop | pg_index_column_has_property
228 -----+--------------------+------------------------------
229 1 | orderable | t
230 1 | asc | t
231 1 | desc | f
232 1 | nulls_first | f
233 1 | nulls_last | t
234 1 | distance_orderable | f
235 1 | returnable | t
236 1 | bogus |
237 2 | orderable | f
238 2 | asc |
239 2 | desc |
240 2 | nulls_first |
241 2 | nulls_last |
242 2 | distance_orderable | f
243 2 | returnable | t
244 2 | bogus |
245 3 | orderable | f
246 3 | asc |
247 3 | desc |
248 3 | nulls_first |
249 3 | nulls_last |
250 3 | distance_orderable | f
251 3 | returnable | t
252 3 | bogus |
253 (24 rows)