1-- 2-- CREATE_INDEX 3-- Create ancillary data structures (i.e. indices) 4-- 5 6-- 7-- BTREE 8-- 9CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops); 10 11CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops); 12 13CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops); 14 15CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops); 16 17CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops); 18 19CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops); 20 21CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops); 22 23CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops); 24 25CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops); 26 27CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous); 28 29CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops); 30 31CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops); 32 33CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops); 34 35CREATE INDEX rix ON road USING btree (name text_ops); 36 37CREATE INDEX iix ON ihighway USING btree (name text_ops); 38 39CREATE INDEX six ON shighway USING btree (name text_ops); 40 41-- test comments 42COMMENT ON INDEX six_wrong IS 'bad index'; 43COMMENT ON INDEX six IS 'good index'; 44COMMENT ON INDEX six IS NULL; 45 46-- 47-- BTREE ascending/descending cases 48-- 49-- we load int4/text from pure descending data (each key is a new 50-- low key) and name/f8 from pure ascending data (each key is a new 51-- high key). we had a bug where new low keys would sometimes be 52-- "lost". 53-- 54CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops); 55 56CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops); 57 58CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops); 59 60CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops); 61 62-- 63-- BTREE partial indices 64-- 65CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops) 66 where unique1 < 20 or unique1 > 980; 67 68CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops) 69 where stringu1 < 'B'; 70 71CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops) 72 where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K'; 73 74-- 75-- GiST (rtree-equivalent opclasses only) 76-- 77CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base); 78 79CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); 80 81CREATE INDEX gcircleind ON circle_tbl USING gist (f1); 82 83INSERT INTO POINT_TBL(f1) VALUES (NULL); 84 85CREATE INDEX gpointind ON point_tbl USING gist (f1); 86 87CREATE TEMP TABLE gpolygon_tbl AS 88 SELECT polygon(home_base) AS f1 FROM slow_emp4000; 89INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); 90INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' ); 91 92CREATE TEMP TABLE gcircle_tbl AS 93 SELECT circle(home_base) AS f1 FROM slow_emp4000; 94 95CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1); 96 97CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1); 98 99-- 100-- SP-GiST 101-- 102 103CREATE TABLE quad_point_tbl AS 104 SELECT point(unique1,unique2) AS p FROM tenk1; 105 106INSERT INTO quad_point_tbl 107 SELECT '(333.0,400.0)'::point FROM generate_series(1,1000); 108 109INSERT INTO quad_point_tbl VALUES (NULL), (NULL), (NULL); 110 111CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p); 112 113CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl; 114 115CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops); 116 117CREATE TABLE radix_text_tbl AS 118 SELECT name AS t FROM road WHERE name !~ '^[0-9]'; 119 120INSERT INTO radix_text_tbl 121 SELECT 'P0123456789abcdef' FROM generate_series(1,1000); 122INSERT INTO radix_text_tbl VALUES ('P0123456789abcde'); 123INSERT INTO radix_text_tbl VALUES ('P0123456789abcdefF'); 124 125CREATE INDEX sp_radix_ind ON radix_text_tbl USING spgist (t); 126 127-- 128-- Test GiST and SP-GiST indexes 129-- 130 131-- get non-indexed results for comparison purposes 132 133SET enable_seqscan = ON; 134SET enable_indexscan = OFF; 135SET enable_bitmapscan = OFF; 136 137SELECT * FROM fast_emp4000 138 WHERE home_base @ '(200,200),(2000,1000)'::box 139 ORDER BY (home_base[0])[0]; 140 141SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 142 143SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 144 145SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 146 ORDER BY (poly_center(f1))[0]; 147 148SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 149 ORDER BY area(f1); 150 151SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 152 153SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 154 155SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 156 157SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 158 159SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 160 161SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 162 163SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 164 165SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 166 167SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 168 169SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 170 171SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 172 173SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 174 175SELECT * FROM point_tbl WHERE f1 IS NULL; 176 177SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 178 179SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 180 181SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 182 183SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 184 185SELECT count(*) FROM quad_point_tbl; 186 187SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 188 189SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 190 191SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 192 193SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 194 195SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 196 197SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 198 199SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 200 201SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 202 203SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 204 205SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 206 207SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 208 209SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 210 211SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 212 213SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 214 215SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 216 217SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 218 219SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 220 221SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 222 223SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 224 225SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 226 227SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 228 229SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 230 231-- Now check the results from plain indexscan 232SET enable_seqscan = OFF; 233SET enable_indexscan = ON; 234SET enable_bitmapscan = OFF; 235 236EXPLAIN (COSTS OFF) 237SELECT * FROM fast_emp4000 238 WHERE home_base @ '(200,200),(2000,1000)'::box 239 ORDER BY (home_base[0])[0]; 240SELECT * FROM fast_emp4000 241 WHERE home_base @ '(200,200),(2000,1000)'::box 242 ORDER BY (home_base[0])[0]; 243 244EXPLAIN (COSTS OFF) 245SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 246SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 247 248EXPLAIN (COSTS OFF) 249SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 250SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 251 252EXPLAIN (COSTS OFF) 253SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 254 ORDER BY (poly_center(f1))[0]; 255SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 256 ORDER BY (poly_center(f1))[0]; 257 258EXPLAIN (COSTS OFF) 259SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 260 ORDER BY area(f1); 261SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 262 ORDER BY area(f1); 263 264EXPLAIN (COSTS OFF) 265SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 266SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 267 268EXPLAIN (COSTS OFF) 269SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 270SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 271 272EXPLAIN (COSTS OFF) 273SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 274SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 275 276EXPLAIN (COSTS OFF) 277SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 278SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 279 280EXPLAIN (COSTS OFF) 281SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 282SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 283 284EXPLAIN (COSTS OFF) 285SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 286SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 287 288EXPLAIN (COSTS OFF) 289SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 290SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 291 292EXPLAIN (COSTS OFF) 293SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 294SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 295 296EXPLAIN (COSTS OFF) 297SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 298SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 299 300EXPLAIN (COSTS OFF) 301SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 302SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 303 304EXPLAIN (COSTS OFF) 305SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 306SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 307 308EXPLAIN (COSTS OFF) 309SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 310SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 311 312EXPLAIN (COSTS OFF) 313SELECT * FROM point_tbl WHERE f1 IS NULL; 314SELECT * FROM point_tbl WHERE f1 IS NULL; 315 316EXPLAIN (COSTS OFF) 317SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 318SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 319 320EXPLAIN (COSTS OFF) 321SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 322SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 323 324EXPLAIN (COSTS OFF) 325SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 326SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 327 328EXPLAIN (COSTS OFF) 329SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 330SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 331 332EXPLAIN (COSTS OFF) 333SELECT count(*) FROM quad_point_tbl; 334SELECT count(*) FROM quad_point_tbl; 335 336EXPLAIN (COSTS OFF) 337SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 338SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 339 340EXPLAIN (COSTS OFF) 341SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 342SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 343 344EXPLAIN (COSTS OFF) 345SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 346SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 347 348EXPLAIN (COSTS OFF) 349SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 350SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 351 352EXPLAIN (COSTS OFF) 353SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 354SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 355 356EXPLAIN (COSTS OFF) 357SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 358SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 359 360EXPLAIN (COSTS OFF) 361SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 362SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 363 364EXPLAIN (COSTS OFF) 365SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 366SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 367 368EXPLAIN (COSTS OFF) 369SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 370SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 371 372EXPLAIN (COSTS OFF) 373SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 374SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 375 376EXPLAIN (COSTS OFF) 377SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 378SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 379 380EXPLAIN (COSTS OFF) 381SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 382SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 383 384EXPLAIN (COSTS OFF) 385SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 386SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 387 388EXPLAIN (COSTS OFF) 389SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 390SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 391 392EXPLAIN (COSTS OFF) 393SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 394SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 395 396EXPLAIN (COSTS OFF) 397SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 398SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 399 400EXPLAIN (COSTS OFF) 401SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 402SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 403 404EXPLAIN (COSTS OFF) 405SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 406SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 407 408EXPLAIN (COSTS OFF) 409SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 410SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 411 412EXPLAIN (COSTS OFF) 413SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 414SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 415 416EXPLAIN (COSTS OFF) 417SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 418SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 419 420EXPLAIN (COSTS OFF) 421SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 422SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 423 424EXPLAIN (COSTS OFF) 425SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 426SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 427 428EXPLAIN (COSTS OFF) 429SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 430SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 431 432EXPLAIN (COSTS OFF) 433SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 434SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 435 436EXPLAIN (COSTS OFF) 437SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 438SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 439 440EXPLAIN (COSTS OFF) 441SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 442SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 443 444EXPLAIN (COSTS OFF) 445SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 446SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 447 448EXPLAIN (COSTS OFF) 449SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 450SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 451 452-- Now check the results from bitmap indexscan 453SET enable_seqscan = OFF; 454SET enable_indexscan = OFF; 455SET enable_bitmapscan = ON; 456 457EXPLAIN (COSTS OFF) 458SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 459SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 460 461EXPLAIN (COSTS OFF) 462SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 463SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 464 465EXPLAIN (COSTS OFF) 466SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 467SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 468 469EXPLAIN (COSTS OFF) 470SELECT count(*) FROM quad_point_tbl; 471SELECT count(*) FROM quad_point_tbl; 472 473EXPLAIN (COSTS OFF) 474SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 475SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 476 477EXPLAIN (COSTS OFF) 478SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 479SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 480 481EXPLAIN (COSTS OFF) 482SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 483SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 484 485EXPLAIN (COSTS OFF) 486SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 487SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 488 489EXPLAIN (COSTS OFF) 490SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 491SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 492 493EXPLAIN (COSTS OFF) 494SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 495SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 496 497EXPLAIN (COSTS OFF) 498SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 499SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 500 501EXPLAIN (COSTS OFF) 502SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 503SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 504 505EXPLAIN (COSTS OFF) 506SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 507SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 508 509EXPLAIN (COSTS OFF) 510SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 511SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 512 513EXPLAIN (COSTS OFF) 514SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 515SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 516 517EXPLAIN (COSTS OFF) 518SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 519SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 520 521EXPLAIN (COSTS OFF) 522SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 523SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 524 525EXPLAIN (COSTS OFF) 526SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 527SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 528 529EXPLAIN (COSTS OFF) 530SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 531SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 532 533EXPLAIN (COSTS OFF) 534SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 535SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 536 537EXPLAIN (COSTS OFF) 538SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 539SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 540 541EXPLAIN (COSTS OFF) 542SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 543SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 544 545EXPLAIN (COSTS OFF) 546SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 547SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 548 549EXPLAIN (COSTS OFF) 550SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 551SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 552 553EXPLAIN (COSTS OFF) 554SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 555SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 556 557EXPLAIN (COSTS OFF) 558SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 559SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 560 561EXPLAIN (COSTS OFF) 562SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 563SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 564 565EXPLAIN (COSTS OFF) 566SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 567SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 568 569EXPLAIN (COSTS OFF) 570SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 571SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 572 573EXPLAIN (COSTS OFF) 574SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 575SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 576 577EXPLAIN (COSTS OFF) 578SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 579SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 580 581RESET enable_seqscan; 582RESET enable_indexscan; 583RESET enable_bitmapscan; 584 585-- 586-- GIN over int[] and text[] 587-- 588-- Note: GIN currently supports only bitmap scans, not plain indexscans 589-- 590 591SET enable_seqscan = OFF; 592SET enable_indexscan = OFF; 593SET enable_bitmapscan = ON; 594 595CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); 596 597explain (costs off) 598SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 599 600SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 601SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 602SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno; 603SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno; 604SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno; 605SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno; 606SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; 607SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno; 608SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno; 609SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno; 610SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno; 611SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno; 612SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 613SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; 614SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; 615SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 616 617CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); 618 619explain (costs off) 620SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 621 622SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 623SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; 624SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; 625SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; 626SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 627SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 628SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; 629SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno; 630SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 631SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno; 632SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno; 633SELECT * FROM array_index_op_test WHERE t <@ '{}' ORDER BY seqno; 634 635-- And try it with a multicolumn GIN index 636 637DROP INDEX intarrayidx, textarrayidx; 638 639CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t); 640 641SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 642SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 643SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno; 644SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; 645SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; 646SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; 647SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 648SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 649SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 650 651RESET enable_seqscan; 652RESET enable_indexscan; 653RESET enable_bitmapscan; 654 655-- 656-- Try a GIN index with a lot of items with same key. (GIN creates a posting 657-- tree when there are enough duplicates) 658-- 659CREATE TABLE array_gin_test (a int[]); 660 661INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g; 662 663CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a); 664 665SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}'; 666 667DROP TABLE array_gin_test; 668 669-- 670-- Test GIN index's reloptions 671-- 672CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) 673 WITH (FASTUPDATE=on, GIN_PENDING_LIST_LIMIT=128); 674\d+ gin_relopts_test 675 676-- 677-- HASH 678-- 679CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); 680 681CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); 682 683CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); 684 685CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops); 686 687CREATE UNLOGGED TABLE unlogged_hash_table (id int4); 688CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); 689DROP TABLE unlogged_hash_table; 690 691-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); 692 693-- Test hash index build tuplesorting. Force hash tuplesort using low 694-- maintenance_work_mem setting and fillfactor: 695SET maintenance_work_mem = '1MB'; 696CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10); 697EXPLAIN (COSTS OFF) 698SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 699SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 700DROP INDEX hash_tuplesort_idx; 701RESET maintenance_work_mem; 702 703 704-- 705-- Test functional index 706-- 707CREATE TABLE func_index_heap (f1 text, f2 text); 708CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); 709 710INSERT INTO func_index_heap VALUES('ABC','DEF'); 711INSERT INTO func_index_heap VALUES('AB','CDEFG'); 712INSERT INTO func_index_heap VALUES('QWE','RTY'); 713-- this should fail because of unique index: 714INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 715-- but this shouldn't: 716INSERT INTO func_index_heap VALUES('QWERTY'); 717 718-- while we're here, see that the metadata looks sane 719\d func_index_heap 720\d func_index_index 721 722 723-- 724-- Same test, expressional index 725-- 726DROP TABLE func_index_heap; 727CREATE TABLE func_index_heap (f1 text, f2 text); 728CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); 729 730INSERT INTO func_index_heap VALUES('ABC','DEF'); 731INSERT INTO func_index_heap VALUES('AB','CDEFG'); 732INSERT INTO func_index_heap VALUES('QWE','RTY'); 733-- this should fail because of unique index: 734INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 735-- but this shouldn't: 736INSERT INTO func_index_heap VALUES('QWERTY'); 737 738-- while we're here, see that the metadata looks sane 739\d func_index_heap 740\d func_index_index 741 742-- this should fail because of unsafe column type (anonymous record) 743create index on func_index_heap ((f1 || f2), (row(f1, f2))); 744 745 746-- 747-- Also try building functional, expressional, and partial indexes on 748-- tables that already contain data. 749-- 750create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); 751create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); 752create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; 753 754-- 755-- Try some concurrent index builds 756-- 757-- Unfortunately this only tests about half the code paths because there are 758-- no concurrent updates happening to the table at the same time. 759 760CREATE TABLE concur_heap (f1 text, f2 text); 761-- empty table 762CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); 763CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1); 764INSERT INTO concur_heap VALUES ('a','b'); 765INSERT INTO concur_heap VALUES ('b','b'); 766-- unique index 767CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); 768CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1); 769-- check if constraint is set up properly to be enforced 770INSERT INTO concur_heap VALUES ('b','x'); 771-- check if constraint is enforced properly at build time 772CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); 773-- test that expression indexes and partial indexes work concurrently 774CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; 775CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; 776-- here we also check that you can default the index name 777CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); 778-- You can't do a concurrent index build in a transaction 779BEGIN; 780CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); 781COMMIT; 782-- test where predicate is able to do a transactional update during 783-- a concurrent build before switching pg_index state flags. 784CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE 785LANGUAGE plpgsql AS $$ 786BEGIN 787 EXECUTE 'SELECT txid_current()'; 788 RETURN true; 789END; $$; 790CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) 791 WHERE predicate_stable(); 792DROP INDEX concur_index8; 793DROP FUNCTION predicate_stable(); 794 795-- But you can do a regular index build in a transaction 796BEGIN; 797CREATE INDEX std_index on concur_heap(f2); 798COMMIT; 799 800-- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX 801VACUUM FULL concur_heap; 802REINDEX TABLE concur_heap; 803DELETE FROM concur_heap WHERE f1 = 'b'; 804VACUUM FULL concur_heap; 805\d concur_heap 806REINDEX TABLE concur_heap; 807\d concur_heap 808 809-- Temporary tables with concurrent builds and on-commit actions 810-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. 811-- PRESERVE ROWS, the default. 812CREATE TEMP TABLE concur_temp (f1 int, f2 text) 813 ON COMMIT PRESERVE ROWS; 814INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 815CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 816DROP INDEX CONCURRENTLY concur_temp_ind; 817DROP TABLE concur_temp; 818-- ON COMMIT DROP 819BEGIN; 820CREATE TEMP TABLE concur_temp (f1 int, f2 text) 821 ON COMMIT DROP; 822INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 823-- Fails when running in a transaction. 824CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 825COMMIT; 826-- ON COMMIT DELETE ROWS 827CREATE TEMP TABLE concur_temp (f1 int, f2 text) 828 ON COMMIT DELETE ROWS; 829INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 830CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 831DROP INDEX CONCURRENTLY concur_temp_ind; 832DROP TABLE concur_temp; 833 834-- 835-- Try some concurrent index drops 836-- 837DROP INDEX CONCURRENTLY "concur_index2"; -- works 838DROP INDEX CONCURRENTLY IF EXISTS "concur_index2"; -- notice 839 840-- failures 841DROP INDEX CONCURRENTLY "concur_index2", "concur_index3"; 842BEGIN; 843DROP INDEX CONCURRENTLY "concur_index5"; 844ROLLBACK; 845 846-- successes 847DROP INDEX CONCURRENTLY IF EXISTS "concur_index3"; 848DROP INDEX CONCURRENTLY "concur_index4"; 849DROP INDEX CONCURRENTLY "concur_index5"; 850DROP INDEX CONCURRENTLY "concur_index1"; 851DROP INDEX CONCURRENTLY "concur_heap_expr_idx"; 852 853\d concur_heap 854 855DROP TABLE concur_heap; 856 857-- 858-- Test ADD CONSTRAINT USING INDEX 859-- 860 861CREATE TABLE cwi_test( a int , b varchar(10), c char); 862 863-- add some data so that all tests have something to work with. 864 865INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6); 866 867CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b); 868ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx; 869 870\d cwi_test 871\d cwi_uniq_idx 872 873CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a); 874ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx, 875 ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY 876 USING INDEX cwi_uniq2_idx; 877 878\d cwi_test 879\d cwi_replaced_pkey 880 881DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it 882 883DROP TABLE cwi_test; 884 885-- 886-- Check handling of indexes on system columns 887-- 888CREATE TABLE oid_table (a INT) WITH OIDS; 889 890-- An index on the OID column should be allowed 891CREATE INDEX ON oid_table (oid); 892 893-- Other system columns cannot be indexed 894CREATE INDEX ON oid_table (ctid); 895 896-- nor used in expressions 897CREATE INDEX ON oid_table ((ctid >= '(1000,0)')); 898 899-- nor used in predicates 900CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)'; 901 902DROP TABLE oid_table; 903 904-- 905-- Tests for IS NULL/IS NOT NULL with b-tree indexes 906-- 907 908SELECT unique1, unique2 INTO onek_with_null FROM onek; 909INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL); 910CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1); 911 912SET enable_seqscan = OFF; 913SET enable_indexscan = ON; 914SET enable_bitmapscan = ON; 915 916SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 917SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 918SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 919SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 920SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 921SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 922 923DROP INDEX onek_nulltest; 924 925CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1); 926 927SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 928SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 929SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 930SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 931SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 932SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 933 934DROP INDEX onek_nulltest; 935 936CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1); 937 938SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 939SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 940SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 941SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 942SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 943SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 944 945DROP INDEX onek_nulltest; 946 947CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1); 948 949SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 950SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 951SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 952SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 953SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 954SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 955 956DROP INDEX onek_nulltest; 957 958-- Check initial-positioning logic too 959 960CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2); 961 962SET enable_seqscan = OFF; 963SET enable_indexscan = ON; 964SET enable_bitmapscan = OFF; 965 966SELECT unique1, unique2 FROM onek_with_null 967 ORDER BY unique2 LIMIT 2; 968SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 969 ORDER BY unique2 LIMIT 2; 970SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0 971 ORDER BY unique2 LIMIT 2; 972 973SELECT unique1, unique2 FROM onek_with_null 974 ORDER BY unique2 DESC LIMIT 2; 975SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 976 ORDER BY unique2 DESC LIMIT 2; 977SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999 978 ORDER BY unique2 DESC LIMIT 2; 979 980RESET enable_seqscan; 981RESET enable_indexscan; 982RESET enable_bitmapscan; 983 984DROP TABLE onek_with_null; 985 986-- 987-- Check bitmap index path planning 988-- 989 990EXPLAIN (COSTS OFF) 991SELECT * FROM tenk1 992 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 993SELECT * FROM tenk1 994 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 995 996EXPLAIN (COSTS OFF) 997SELECT count(*) FROM tenk1 998 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 999SELECT count(*) FROM tenk1 1000 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 1001 1002-- 1003-- Check behavior with duplicate index column contents 1004-- 1005 1006CREATE TABLE dupindexcols AS 1007 SELECT unique1 as id, stringu2::text as f1 FROM tenk1; 1008CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); 1009ANALYZE dupindexcols; 1010 1011EXPLAIN (COSTS OFF) 1012 SELECT count(*) FROM dupindexcols 1013 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 1014SELECT count(*) FROM dupindexcols 1015 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 1016 1017-- 1018-- Check ordering of =ANY indexqual results (bug in 9.2.0) 1019-- 1020 1021vacuum tenk1; -- ensure we get consistent plans here 1022 1023explain (costs off) 1024SELECT unique1 FROM tenk1 1025WHERE unique1 IN (1,42,7) 1026ORDER BY unique1; 1027 1028SELECT unique1 FROM tenk1 1029WHERE unique1 IN (1,42,7) 1030ORDER BY unique1; 1031 1032explain (costs off) 1033SELECT thousand, tenthous FROM tenk1 1034WHERE thousand < 2 AND tenthous IN (1001,3000) 1035ORDER BY thousand; 1036 1037SELECT thousand, tenthous FROM tenk1 1038WHERE thousand < 2 AND tenthous IN (1001,3000) 1039ORDER BY thousand; 1040 1041SET enable_indexonlyscan = OFF; 1042 1043explain (costs off) 1044SELECT thousand, tenthous FROM tenk1 1045WHERE thousand < 2 AND tenthous IN (1001,3000) 1046ORDER BY thousand; 1047 1048SELECT thousand, tenthous FROM tenk1 1049WHERE thousand < 2 AND tenthous IN (1001,3000) 1050ORDER BY thousand; 1051 1052RESET enable_indexonlyscan; 1053 1054-- 1055-- Check elimination of constant-NULL subexpressions 1056-- 1057 1058explain (costs off) 1059 select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); 1060 1061-- 1062-- REINDEX (VERBOSE) 1063-- 1064CREATE TABLE reindex_verbose(id integer primary key); 1065\set VERBOSITY terse 1066REINDEX (VERBOSE) TABLE reindex_verbose; 1067DROP TABLE reindex_verbose; 1068 1069-- 1070-- REINDEX SCHEMA 1071-- 1072REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist 1073CREATE SCHEMA schema_to_reindex; 1074SET search_path = 'schema_to_reindex'; 1075CREATE TABLE table1(col1 SERIAL PRIMARY KEY); 1076INSERT INTO table1 SELECT generate_series(1,400); 1077CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL); 1078INSERT INTO table2 SELECT generate_series(1,400), 'abc'; 1079CREATE INDEX ON table2(col2); 1080CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2; 1081CREATE INDEX ON matview(col1); 1082CREATE VIEW view AS SELECT col2 FROM table2; 1083CREATE TABLE reindex_before AS 1084SELECT oid, relname, relfilenode, relkind, reltoastrelid 1085 FROM pg_class 1086 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 1087INSERT INTO reindex_before 1088SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid 1089FROM pg_class WHERE oid IN 1090 (SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0); 1091INSERT INTO reindex_before 1092SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid 1093FROM pg_class where oid in 1094 (select indexrelid from pg_index where indrelid in 1095 (select reltoastrelid from reindex_before where reltoastrelid > 0)); 1096REINDEX SCHEMA schema_to_reindex; 1097CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind 1098 FROM pg_class 1099 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 1100SELECT b.relname, 1101 b.relkind, 1102 CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' 1103 ELSE 'relfilenode has changed' END 1104 FROM reindex_before b JOIN pg_class a ON b.oid = a.oid 1105 ORDER BY 1; 1106REINDEX SCHEMA schema_to_reindex; 1107BEGIN; 1108REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction 1109END; 1110 1111-- Failure for unauthorized user 1112CREATE ROLE regress_reindexuser NOLOGIN; 1113SET SESSION ROLE regress_reindexuser; 1114REINDEX SCHEMA schema_to_reindex; 1115 1116-- Clean up 1117RESET ROLE; 1118DROP ROLE regress_reindexuser; 1119SET client_min_messages TO 'warning'; 1120DROP SCHEMA schema_to_reindex CASCADE; 1121RESET client_min_messages; 1122