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 count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 228 229SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 230 231SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 232 233-- Now check the results from plain indexscan 234SET enable_seqscan = OFF; 235SET enable_indexscan = ON; 236SET enable_bitmapscan = OFF; 237 238EXPLAIN (COSTS OFF) 239SELECT * FROM fast_emp4000 240 WHERE home_base @ '(200,200),(2000,1000)'::box 241 ORDER BY (home_base[0])[0]; 242SELECT * FROM fast_emp4000 243 WHERE home_base @ '(200,200),(2000,1000)'::box 244 ORDER BY (home_base[0])[0]; 245 246EXPLAIN (COSTS OFF) 247SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 248SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 249 250EXPLAIN (COSTS OFF) 251SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 252SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 253 254EXPLAIN (COSTS OFF) 255SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 256 ORDER BY (poly_center(f1))[0]; 257SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 258 ORDER BY (poly_center(f1))[0]; 259 260EXPLAIN (COSTS OFF) 261SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 262 ORDER BY area(f1); 263SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 264 ORDER BY area(f1); 265 266EXPLAIN (COSTS OFF) 267SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 268SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 269 270EXPLAIN (COSTS OFF) 271SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 272SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 273 274EXPLAIN (COSTS OFF) 275SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 276SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 277 278EXPLAIN (COSTS OFF) 279SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 280SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 281 282EXPLAIN (COSTS OFF) 283SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 284SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 285 286EXPLAIN (COSTS OFF) 287SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 288SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 289 290EXPLAIN (COSTS OFF) 291SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 292SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 293 294EXPLAIN (COSTS OFF) 295SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 296SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 297 298EXPLAIN (COSTS OFF) 299SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 300SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 301 302EXPLAIN (COSTS OFF) 303SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 304SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 305 306EXPLAIN (COSTS OFF) 307SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 308SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 309 310EXPLAIN (COSTS OFF) 311SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 312SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 313 314EXPLAIN (COSTS OFF) 315SELECT * FROM point_tbl WHERE f1 IS NULL; 316SELECT * FROM point_tbl WHERE f1 IS NULL; 317 318EXPLAIN (COSTS OFF) 319SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 320SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 321 322EXPLAIN (COSTS OFF) 323SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 324SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 325 326EXPLAIN (COSTS OFF) 327SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 328SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 329 330EXPLAIN (COSTS OFF) 331SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 332SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 333 334EXPLAIN (COSTS OFF) 335SELECT count(*) FROM quad_point_tbl; 336SELECT count(*) FROM quad_point_tbl; 337 338EXPLAIN (COSTS OFF) 339SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 340SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 341 342EXPLAIN (COSTS OFF) 343SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 344SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 345 346EXPLAIN (COSTS OFF) 347SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 348SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 349 350EXPLAIN (COSTS OFF) 351SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 352SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 353 354EXPLAIN (COSTS OFF) 355SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 356SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 357 358EXPLAIN (COSTS OFF) 359SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 360SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 361 362EXPLAIN (COSTS OFF) 363SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 364SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 365 366EXPLAIN (COSTS OFF) 367SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 368SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 369 370EXPLAIN (COSTS OFF) 371SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 372SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 373 374EXPLAIN (COSTS OFF) 375SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 376SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 377 378EXPLAIN (COSTS OFF) 379SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 380SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 381 382EXPLAIN (COSTS OFF) 383SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 384SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 385 386EXPLAIN (COSTS OFF) 387SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 388SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 389 390EXPLAIN (COSTS OFF) 391SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 392SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 393 394EXPLAIN (COSTS OFF) 395SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 396SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 397 398EXPLAIN (COSTS OFF) 399SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 400SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 401 402EXPLAIN (COSTS OFF) 403SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 404SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 405 406EXPLAIN (COSTS OFF) 407SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 408SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 409 410EXPLAIN (COSTS OFF) 411SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 412SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 413 414EXPLAIN (COSTS OFF) 415SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 416SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 417 418EXPLAIN (COSTS OFF) 419SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 420SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 421 422EXPLAIN (COSTS OFF) 423SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 424SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 425 426EXPLAIN (COSTS OFF) 427SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 428SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 429 430EXPLAIN (COSTS OFF) 431SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 432SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 433 434EXPLAIN (COSTS OFF) 435SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 436SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 437 438EXPLAIN (COSTS OFF) 439SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 440SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 441 442EXPLAIN (COSTS OFF) 443SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 444SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 445 446EXPLAIN (COSTS OFF) 447SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 448SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 449 450EXPLAIN (COSTS OFF) 451SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 452SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 453 454EXPLAIN (COSTS OFF) 455SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 456SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 457 458-- Now check the results from bitmap indexscan 459SET enable_seqscan = OFF; 460SET enable_indexscan = OFF; 461SET enable_bitmapscan = ON; 462 463EXPLAIN (COSTS OFF) 464SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 465SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 466 467EXPLAIN (COSTS OFF) 468SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 469SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 470 471EXPLAIN (COSTS OFF) 472SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 473SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 474 475EXPLAIN (COSTS OFF) 476SELECT count(*) FROM quad_point_tbl; 477SELECT count(*) FROM quad_point_tbl; 478 479EXPLAIN (COSTS OFF) 480SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 481SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 482 483EXPLAIN (COSTS OFF) 484SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 485SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 486 487EXPLAIN (COSTS OFF) 488SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 489SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 490 491EXPLAIN (COSTS OFF) 492SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 493SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 494 495EXPLAIN (COSTS OFF) 496SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 497SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 498 499EXPLAIN (COSTS OFF) 500SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 501SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 502 503EXPLAIN (COSTS OFF) 504SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 505SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 506 507EXPLAIN (COSTS OFF) 508SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 509SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 510 511EXPLAIN (COSTS OFF) 512SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 513SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 514 515EXPLAIN (COSTS OFF) 516SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 517SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 518 519EXPLAIN (COSTS OFF) 520SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 521SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 522 523EXPLAIN (COSTS OFF) 524SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 525SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 526 527EXPLAIN (COSTS OFF) 528SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 529SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 530 531EXPLAIN (COSTS OFF) 532SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 533SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 534 535EXPLAIN (COSTS OFF) 536SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 537SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 538 539EXPLAIN (COSTS OFF) 540SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 541SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 542 543EXPLAIN (COSTS OFF) 544SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 545SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 546 547EXPLAIN (COSTS OFF) 548SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 549SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 550 551EXPLAIN (COSTS OFF) 552SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 553SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 554 555EXPLAIN (COSTS OFF) 556SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 557SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 558 559EXPLAIN (COSTS OFF) 560SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 561SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 562 563EXPLAIN (COSTS OFF) 564SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 565SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 566 567EXPLAIN (COSTS OFF) 568SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 569SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 570 571EXPLAIN (COSTS OFF) 572SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 573SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 574 575EXPLAIN (COSTS OFF) 576SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 577SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 578 579EXPLAIN (COSTS OFF) 580SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 581SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 582 583EXPLAIN (COSTS OFF) 584SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 585SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 586 587EXPLAIN (COSTS OFF) 588SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 589SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 590 591RESET enable_seqscan; 592RESET enable_indexscan; 593RESET enable_bitmapscan; 594 595-- 596-- GIN over int[] and text[] 597-- 598-- Note: GIN currently supports only bitmap scans, not plain indexscans 599-- 600 601SET enable_seqscan = OFF; 602SET enable_indexscan = OFF; 603SET enable_bitmapscan = ON; 604 605CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); 606 607explain (costs off) 608SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 609 610SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 611SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 612SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno; 613SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno; 614SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno; 615SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno; 616SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; 617SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno; 618SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno; 619SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno; 620SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno; 621SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno; 622SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 623SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; 624SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; 625SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 626 627CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); 628 629explain (costs off) 630SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 631 632SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 633SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; 634SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; 635SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; 636SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 637SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 638SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; 639SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno; 640SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 641SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno; 642SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno; 643SELECT * FROM array_index_op_test WHERE t <@ '{}' ORDER BY seqno; 644 645-- And try it with a multicolumn GIN index 646 647DROP INDEX intarrayidx, textarrayidx; 648 649CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t); 650 651SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 652SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 653SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno; 654SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; 655SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; 656SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; 657SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 658SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 659SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 660 661RESET enable_seqscan; 662RESET enable_indexscan; 663RESET enable_bitmapscan; 664 665-- 666-- Try a GIN index with a lot of items with same key. (GIN creates a posting 667-- tree when there are enough duplicates) 668-- 669CREATE TABLE array_gin_test (a int[]); 670 671INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g; 672 673CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a); 674 675SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}'; 676 677DROP TABLE array_gin_test; 678 679-- 680-- Test GIN index's reloptions 681-- 682CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) 683 WITH (FASTUPDATE=on, GIN_PENDING_LIST_LIMIT=128); 684\d+ gin_relopts_test 685 686-- 687-- HASH 688-- 689CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); 690 691CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); 692 693CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); 694 695CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60); 696 697CREATE UNLOGGED TABLE unlogged_hash_table (id int4); 698CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); 699DROP TABLE unlogged_hash_table; 700 701-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); 702 703-- Test hash index build tuplesorting. Force hash tuplesort using low 704-- maintenance_work_mem setting and fillfactor: 705SET maintenance_work_mem = '1MB'; 706CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10); 707EXPLAIN (COSTS OFF) 708SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 709SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 710DROP INDEX hash_tuplesort_idx; 711RESET maintenance_work_mem; 712 713 714-- 715-- Test functional index 716-- 717CREATE TABLE func_index_heap (f1 text, f2 text); 718CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); 719 720INSERT INTO func_index_heap VALUES('ABC','DEF'); 721INSERT INTO func_index_heap VALUES('AB','CDEFG'); 722INSERT INTO func_index_heap VALUES('QWE','RTY'); 723-- this should fail because of unique index: 724INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 725-- but this shouldn't: 726INSERT INTO func_index_heap VALUES('QWERTY'); 727 728-- while we're here, see that the metadata looks sane 729\d func_index_heap 730\d func_index_index 731 732 733-- 734-- Same test, expressional index 735-- 736DROP TABLE func_index_heap; 737CREATE TABLE func_index_heap (f1 text, f2 text); 738CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); 739 740INSERT INTO func_index_heap VALUES('ABC','DEF'); 741INSERT INTO func_index_heap VALUES('AB','CDEFG'); 742INSERT INTO func_index_heap VALUES('QWE','RTY'); 743-- this should fail because of unique index: 744INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 745-- but this shouldn't: 746INSERT INTO func_index_heap VALUES('QWERTY'); 747 748-- while we're here, see that the metadata looks sane 749\d func_index_heap 750\d func_index_index 751 752-- this should fail because of unsafe column type (anonymous record) 753create index on func_index_heap ((f1 || f2), (row(f1, f2))); 754 755 756-- 757-- Test unique index with included columns 758-- 759CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text); 760CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3); 761 762INSERT INTO covering_index_heap VALUES(1,1,'AAA'); 763INSERT INTO covering_index_heap VALUES(1,2,'AAA'); 764-- this should fail because of unique index on f1,f2: 765INSERT INTO covering_index_heap VALUES(1,2,'BBB'); 766-- and this shouldn't: 767INSERT INTO covering_index_heap VALUES(1,4,'AAA'); 768-- Try to build index on table that already contains data 769CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3); 770-- Try to use existing covering index as primary key 771ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX 772covering_pkey; 773DROP TABLE covering_index_heap; 774 775 776-- 777-- Also try building functional, expressional, and partial indexes on 778-- tables that already contain data. 779-- 780create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); 781create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); 782create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; 783 784-- 785-- Try some concurrent index builds 786-- 787-- Unfortunately this only tests about half the code paths because there are 788-- no concurrent updates happening to the table at the same time. 789 790CREATE TABLE concur_heap (f1 text, f2 text); 791-- empty table 792CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); 793CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1); 794INSERT INTO concur_heap VALUES ('a','b'); 795INSERT INTO concur_heap VALUES ('b','b'); 796-- unique index 797CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); 798CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1); 799-- check if constraint is set up properly to be enforced 800INSERT INTO concur_heap VALUES ('b','x'); 801-- check if constraint is enforced properly at build time 802CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); 803-- test that expression indexes and partial indexes work concurrently 804CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; 805CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; 806-- here we also check that you can default the index name 807CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); 808-- You can't do a concurrent index build in a transaction 809BEGIN; 810CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); 811COMMIT; 812-- test where predicate is able to do a transactional update during 813-- a concurrent build before switching pg_index state flags. 814CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE 815LANGUAGE plpgsql AS $$ 816BEGIN 817 EXECUTE 'SELECT txid_current()'; 818 RETURN true; 819END; $$; 820CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) 821 WHERE predicate_stable(); 822DROP INDEX concur_index8; 823DROP FUNCTION predicate_stable(); 824 825-- But you can do a regular index build in a transaction 826BEGIN; 827CREATE INDEX std_index on concur_heap(f2); 828COMMIT; 829 830-- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX 831VACUUM FULL concur_heap; 832REINDEX TABLE concur_heap; 833DELETE FROM concur_heap WHERE f1 = 'b'; 834VACUUM FULL concur_heap; 835\d concur_heap 836REINDEX TABLE concur_heap; 837\d concur_heap 838 839-- Temporary tables with concurrent builds and on-commit actions 840-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. 841-- PRESERVE ROWS, the default. 842CREATE TEMP TABLE concur_temp (f1 int, f2 text) 843 ON COMMIT PRESERVE ROWS; 844INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 845CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 846DROP INDEX CONCURRENTLY concur_temp_ind; 847DROP TABLE concur_temp; 848-- ON COMMIT DROP 849BEGIN; 850CREATE TEMP TABLE concur_temp (f1 int, f2 text) 851 ON COMMIT DROP; 852INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 853-- Fails when running in a transaction. 854CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 855COMMIT; 856-- ON COMMIT DELETE ROWS 857CREATE TEMP TABLE concur_temp (f1 int, f2 text) 858 ON COMMIT DELETE ROWS; 859INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 860CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 861DROP INDEX CONCURRENTLY concur_temp_ind; 862DROP TABLE concur_temp; 863 864-- 865-- Try some concurrent index drops 866-- 867DROP INDEX CONCURRENTLY "concur_index2"; -- works 868DROP INDEX CONCURRENTLY IF EXISTS "concur_index2"; -- notice 869 870-- failures 871DROP INDEX CONCURRENTLY "concur_index2", "concur_index3"; 872BEGIN; 873DROP INDEX CONCURRENTLY "concur_index5"; 874ROLLBACK; 875 876-- successes 877DROP INDEX CONCURRENTLY IF EXISTS "concur_index3"; 878DROP INDEX CONCURRENTLY "concur_index4"; 879DROP INDEX CONCURRENTLY "concur_index5"; 880DROP INDEX CONCURRENTLY "concur_index1"; 881DROP INDEX CONCURRENTLY "concur_heap_expr_idx"; 882 883\d concur_heap 884 885DROP TABLE concur_heap; 886 887-- 888-- Test ADD CONSTRAINT USING INDEX 889-- 890 891CREATE TABLE cwi_test( a int , b varchar(10), c char); 892 893-- add some data so that all tests have something to work with. 894 895INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6); 896 897CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b); 898ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx; 899 900\d cwi_test 901\d cwi_uniq_idx 902 903CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a); 904ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx, 905 ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY 906 USING INDEX cwi_uniq2_idx; 907 908\d cwi_test 909\d cwi_replaced_pkey 910 911DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it 912 913DROP TABLE cwi_test; 914 915-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables 916CREATE TABLE cwi_test(a int) PARTITION BY hash (a); 917create unique index on cwi_test (a); 918alter table cwi_test add primary key using index cwi_test_a_idx ; 919DROP TABLE cwi_test; 920 921-- 922-- Check handling of indexes on system columns 923-- 924CREATE TABLE oid_table (a INT) WITH OIDS; 925 926-- An index on the OID column should be allowed 927CREATE INDEX ON oid_table (oid); 928 929-- Other system columns cannot be indexed 930CREATE INDEX ON oid_table (ctid); 931 932-- nor used in expressions 933CREATE INDEX ON oid_table ((ctid >= '(1000,0)')); 934 935-- nor used in predicates 936CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)'; 937 938DROP TABLE oid_table; 939 940-- 941-- Tests for IS NULL/IS NOT NULL with b-tree indexes 942-- 943 944SELECT unique1, unique2 INTO onek_with_null FROM onek; 945INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL); 946CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1); 947 948SET enable_seqscan = OFF; 949SET enable_indexscan = ON; 950SET enable_bitmapscan = ON; 951 952SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 953SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 954SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 955SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 956SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 957SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 958 959DROP INDEX onek_nulltest; 960 961CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1); 962 963SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 964SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 965SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 966SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 967SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 968SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 969 970DROP INDEX onek_nulltest; 971 972CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1); 973 974SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 975SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 976SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 977SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 978SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 979SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 980 981DROP INDEX onek_nulltest; 982 983CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1); 984 985SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 986SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 987SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 988SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 989SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 990SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 991 992DROP INDEX onek_nulltest; 993 994-- Check initial-positioning logic too 995 996CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2); 997 998SET enable_seqscan = OFF; 999SET enable_indexscan = ON; 1000SET enable_bitmapscan = OFF; 1001 1002SELECT unique1, unique2 FROM onek_with_null 1003 ORDER BY unique2 LIMIT 2; 1004SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 1005 ORDER BY unique2 LIMIT 2; 1006SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0 1007 ORDER BY unique2 LIMIT 2; 1008 1009SELECT unique1, unique2 FROM onek_with_null 1010 ORDER BY unique2 DESC LIMIT 2; 1011SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 1012 ORDER BY unique2 DESC LIMIT 2; 1013SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999 1014 ORDER BY unique2 DESC LIMIT 2; 1015 1016RESET enable_seqscan; 1017RESET enable_indexscan; 1018RESET enable_bitmapscan; 1019 1020DROP TABLE onek_with_null; 1021 1022-- 1023-- Check bitmap index path planning 1024-- 1025 1026EXPLAIN (COSTS OFF) 1027SELECT * FROM tenk1 1028 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 1029SELECT * FROM tenk1 1030 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 1031 1032EXPLAIN (COSTS OFF) 1033SELECT count(*) FROM tenk1 1034 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 1035SELECT count(*) FROM tenk1 1036 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 1037 1038-- 1039-- Check behavior with duplicate index column contents 1040-- 1041 1042CREATE TABLE dupindexcols AS 1043 SELECT unique1 as id, stringu2::text as f1 FROM tenk1; 1044CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); 1045ANALYZE dupindexcols; 1046 1047EXPLAIN (COSTS OFF) 1048 SELECT count(*) FROM dupindexcols 1049 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 1050SELECT count(*) FROM dupindexcols 1051 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 1052 1053-- 1054-- Check ordering of =ANY indexqual results (bug in 9.2.0) 1055-- 1056 1057vacuum tenk1; -- ensure we get consistent plans here 1058 1059explain (costs off) 1060SELECT unique1 FROM tenk1 1061WHERE unique1 IN (1,42,7) 1062ORDER BY unique1; 1063 1064SELECT unique1 FROM tenk1 1065WHERE unique1 IN (1,42,7) 1066ORDER BY unique1; 1067 1068explain (costs off) 1069SELECT thousand, tenthous FROM tenk1 1070WHERE thousand < 2 AND tenthous IN (1001,3000) 1071ORDER BY thousand; 1072 1073SELECT thousand, tenthous FROM tenk1 1074WHERE thousand < 2 AND tenthous IN (1001,3000) 1075ORDER BY thousand; 1076 1077SET enable_indexonlyscan = OFF; 1078 1079explain (costs off) 1080SELECT thousand, tenthous FROM tenk1 1081WHERE thousand < 2 AND tenthous IN (1001,3000) 1082ORDER BY thousand; 1083 1084SELECT thousand, tenthous FROM tenk1 1085WHERE thousand < 2 AND tenthous IN (1001,3000) 1086ORDER BY thousand; 1087 1088RESET enable_indexonlyscan; 1089 1090-- 1091-- Check elimination of constant-NULL subexpressions 1092-- 1093 1094explain (costs off) 1095 select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); 1096 1097-- 1098-- Check matching of boolean index columns to WHERE conditions and sort keys 1099-- 1100 1101create temp table boolindex (b bool, i int, unique(b, i), junk float); 1102 1103explain (costs off) 1104 select * from boolindex order by b, i limit 10; 1105explain (costs off) 1106 select * from boolindex where b order by i limit 10; 1107explain (costs off) 1108 select * from boolindex where b = true order by i desc limit 10; 1109explain (costs off) 1110 select * from boolindex where not b order by i limit 10; 1111 1112-- 1113-- Test for multilevel page deletion 1114-- 1115CREATE TABLE delete_test_table (a bigint, b bigint, c bigint, d bigint); 1116INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,80000) i; 1117ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d); 1118DELETE FROM delete_test_table WHERE a > 40000; 1119VACUUM delete_test_table; 1120DELETE FROM delete_test_table WHERE a > 10; 1121VACUUM delete_test_table; 1122 1123-- 1124-- REINDEX (VERBOSE) 1125-- 1126CREATE TABLE reindex_verbose(id integer primary key); 1127\set VERBOSITY terse 1128REINDEX (VERBOSE) TABLE reindex_verbose; 1129DROP TABLE reindex_verbose; 1130 1131-- 1132-- REINDEX SCHEMA 1133-- 1134REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist 1135CREATE SCHEMA schema_to_reindex; 1136SET search_path = 'schema_to_reindex'; 1137CREATE TABLE table1(col1 SERIAL PRIMARY KEY); 1138INSERT INTO table1 SELECT generate_series(1,400); 1139CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL); 1140INSERT INTO table2 SELECT generate_series(1,400), 'abc'; 1141CREATE INDEX ON table2(col2); 1142CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2; 1143CREATE INDEX ON matview(col1); 1144CREATE VIEW view AS SELECT col2 FROM table2; 1145CREATE TABLE reindex_before AS 1146SELECT oid, relname, relfilenode, relkind, reltoastrelid 1147 FROM pg_class 1148 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 1149INSERT INTO reindex_before 1150SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid 1151FROM pg_class WHERE oid IN 1152 (SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0); 1153INSERT INTO reindex_before 1154SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid 1155FROM pg_class where oid in 1156 (select indexrelid from pg_index where indrelid in 1157 (select reltoastrelid from reindex_before where reltoastrelid > 0)); 1158REINDEX SCHEMA schema_to_reindex; 1159CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind 1160 FROM pg_class 1161 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 1162SELECT b.relname, 1163 b.relkind, 1164 CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' 1165 ELSE 'relfilenode has changed' END 1166 FROM reindex_before b JOIN pg_class a ON b.oid = a.oid 1167 ORDER BY 1; 1168REINDEX SCHEMA schema_to_reindex; 1169BEGIN; 1170REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction 1171END; 1172 1173-- Failure for unauthorized user 1174CREATE ROLE regress_reindexuser NOLOGIN; 1175SET SESSION ROLE regress_reindexuser; 1176REINDEX SCHEMA schema_to_reindex; 1177-- Permission failures with toast tables and indexes (pg_proc's toast here) 1178RESET ROLE; 1179GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser; 1180SET SESSION ROLE regress_reindexuser; 1181REINDEX TABLE pg_toast.pg_toast_1255; 1182REINDEX INDEX pg_toast.pg_toast_1255_index; 1183 1184-- Clean up 1185RESET ROLE; 1186REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser; 1187DROP ROLE regress_reindexuser; 1188\set VERBOSITY terse \\ -- suppress cascade details 1189DROP SCHEMA schema_to_reindex CASCADE; 1190