1-- 2-- SELECT 3-- 4-- btree index 5-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1 6-- 7SELECT * FROM onek 8 WHERE onek.unique1 < 10 9 ORDER BY onek.unique1; 10 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 11---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 12 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 13 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 14 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 15 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 16 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 17 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 18 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 19 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 20 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 21 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx 22(10 rows) 23 24-- 25-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 26-- 27SELECT onek.unique1, onek.stringu1 FROM onek 28 WHERE onek.unique1 < 20 29 ORDER BY unique1 using >; 30 unique1 | stringu1 31---------+---------- 32 19 | TAAAAA 33 18 | SAAAAA 34 17 | RAAAAA 35 16 | QAAAAA 36 15 | PAAAAA 37 14 | OAAAAA 38 13 | NAAAAA 39 12 | MAAAAA 40 11 | LAAAAA 41 10 | KAAAAA 42 9 | JAAAAA 43 8 | IAAAAA 44 7 | HAAAAA 45 6 | GAAAAA 46 5 | FAAAAA 47 4 | EAAAAA 48 3 | DAAAAA 49 2 | CAAAAA 50 1 | BAAAAA 51 0 | AAAAAA 52(20 rows) 53 54-- 55-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 56-- 57SELECT onek.unique1, onek.stringu1 FROM onek 58 WHERE onek.unique1 > 980 59 ORDER BY stringu1 using <; 60 unique1 | stringu1 61---------+---------- 62 988 | AMAAAA 63 989 | BMAAAA 64 990 | CMAAAA 65 991 | DMAAAA 66 992 | EMAAAA 67 993 | FMAAAA 68 994 | GMAAAA 69 995 | HMAAAA 70 996 | IMAAAA 71 997 | JMAAAA 72 998 | KMAAAA 73 999 | LMAAAA 74 981 | TLAAAA 75 982 | ULAAAA 76 983 | VLAAAA 77 984 | WLAAAA 78 985 | XLAAAA 79 986 | YLAAAA 80 987 | ZLAAAA 81(19 rows) 82 83-- 84-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | 85-- sort +1d -2 +0nr -1 86-- 87SELECT onek.unique1, onek.string4 FROM onek 88 WHERE onek.unique1 > 980 89 ORDER BY string4 using <, unique1 using >; 90 unique1 | string4 91---------+--------- 92 999 | AAAAxx 93 995 | AAAAxx 94 983 | AAAAxx 95 982 | AAAAxx 96 981 | AAAAxx 97 998 | HHHHxx 98 997 | HHHHxx 99 993 | HHHHxx 100 990 | HHHHxx 101 986 | HHHHxx 102 996 | OOOOxx 103 991 | OOOOxx 104 988 | OOOOxx 105 987 | OOOOxx 106 985 | OOOOxx 107 994 | VVVVxx 108 992 | VVVVxx 109 989 | VVVVxx 110 984 | VVVVxx 111(19 rows) 112 113-- 114-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | 115-- sort +1dr -2 +0n -1 116-- 117SELECT onek.unique1, onek.string4 FROM onek 118 WHERE onek.unique1 > 980 119 ORDER BY string4 using >, unique1 using <; 120 unique1 | string4 121---------+--------- 122 984 | VVVVxx 123 989 | VVVVxx 124 992 | VVVVxx 125 994 | VVVVxx 126 985 | OOOOxx 127 987 | OOOOxx 128 988 | OOOOxx 129 991 | OOOOxx 130 996 | OOOOxx 131 986 | HHHHxx 132 990 | HHHHxx 133 993 | HHHHxx 134 997 | HHHHxx 135 998 | HHHHxx 136 981 | AAAAxx 137 982 | AAAAxx 138 983 | AAAAxx 139 995 | AAAAxx 140 999 | AAAAxx 141(19 rows) 142 143-- 144-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | 145-- sort +0nr -1 +1d -2 146-- 147SELECT onek.unique1, onek.string4 FROM onek 148 WHERE onek.unique1 < 20 149 ORDER BY unique1 using >, string4 using <; 150 unique1 | string4 151---------+--------- 152 19 | OOOOxx 153 18 | VVVVxx 154 17 | HHHHxx 155 16 | OOOOxx 156 15 | VVVVxx 157 14 | AAAAxx 158 13 | OOOOxx 159 12 | AAAAxx 160 11 | OOOOxx 161 10 | AAAAxx 162 9 | HHHHxx 163 8 | HHHHxx 164 7 | VVVVxx 165 6 | OOOOxx 166 5 | HHHHxx 167 4 | HHHHxx 168 3 | VVVVxx 169 2 | OOOOxx 170 1 | OOOOxx 171 0 | OOOOxx 172(20 rows) 173 174-- 175-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | 176-- sort +0n -1 +1dr -2 177-- 178SELECT onek.unique1, onek.string4 FROM onek 179 WHERE onek.unique1 < 20 180 ORDER BY unique1 using <, string4 using >; 181 unique1 | string4 182---------+--------- 183 0 | OOOOxx 184 1 | OOOOxx 185 2 | OOOOxx 186 3 | VVVVxx 187 4 | HHHHxx 188 5 | HHHHxx 189 6 | OOOOxx 190 7 | VVVVxx 191 8 | HHHHxx 192 9 | HHHHxx 193 10 | AAAAxx 194 11 | OOOOxx 195 12 | AAAAxx 196 13 | OOOOxx 197 14 | AAAAxx 198 15 | VVVVxx 199 16 | OOOOxx 200 17 | HHHHxx 201 18 | VVVVxx 202 19 | OOOOxx 203(20 rows) 204 205-- 206-- test partial btree indexes 207-- 208-- As of 7.2, planner probably won't pick an indexscan without stats, 209-- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan 210-- followed by sort, because that could hide index ordering problems. 211-- 212ANALYZE onek2; 213SET enable_seqscan TO off; 214SET enable_bitmapscan TO off; 215SET enable_sort TO off; 216-- 217-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1 218-- 219SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10; 220 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 221---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 222 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 223 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 224 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 225 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 226 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 227 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 228 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 229 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 230 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 231 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx 232(10 rows) 233 234-- 235-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 236-- 237SELECT onek2.unique1, onek2.stringu1 FROM onek2 238 WHERE onek2.unique1 < 20 239 ORDER BY unique1 using >; 240 unique1 | stringu1 241---------+---------- 242 19 | TAAAAA 243 18 | SAAAAA 244 17 | RAAAAA 245 16 | QAAAAA 246 15 | PAAAAA 247 14 | OAAAAA 248 13 | NAAAAA 249 12 | MAAAAA 250 11 | LAAAAA 251 10 | KAAAAA 252 9 | JAAAAA 253 8 | IAAAAA 254 7 | HAAAAA 255 6 | GAAAAA 256 5 | FAAAAA 257 4 | EAAAAA 258 3 | DAAAAA 259 2 | CAAAAA 260 1 | BAAAAA 261 0 | AAAAAA 262(20 rows) 263 264-- 265-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 266-- 267SELECT onek2.unique1, onek2.stringu1 FROM onek2 268 WHERE onek2.unique1 > 980; 269 unique1 | stringu1 270---------+---------- 271 981 | TLAAAA 272 982 | ULAAAA 273 983 | VLAAAA 274 984 | WLAAAA 275 985 | XLAAAA 276 986 | YLAAAA 277 987 | ZLAAAA 278 988 | AMAAAA 279 989 | BMAAAA 280 990 | CMAAAA 281 991 | DMAAAA 282 992 | EMAAAA 283 993 | FMAAAA 284 994 | GMAAAA 285 995 | HMAAAA 286 996 | IMAAAA 287 997 | JMAAAA 288 998 | KMAAAA 289 999 | LMAAAA 290(19 rows) 291 292RESET enable_seqscan; 293RESET enable_bitmapscan; 294RESET enable_sort; 295SELECT two, stringu1, ten, string4 296 INTO TABLE tmp 297 FROM onek; 298-- 299-- awk '{print $1,$2;}' person.data | 300-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | 301-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | 302-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data 303-- 304-- SELECT name, age FROM person*; ??? check if different 305SELECT p.name, p.age FROM person* p; 306 name | age 307---------+----- 308 mike | 40 309 joe | 20 310 sally | 34 311 sandra | 19 312 alex | 30 313 sue | 50 314 denise | 24 315 sarah | 88 316 teresa | 38 317 nan | 28 318 leah | 68 319 wendy | 78 320 melissa | 28 321 joan | 18 322 mary | 8 323 jane | 58 324 liza | 38 325 jean | 28 326 jenifer | 38 327 juanita | 58 328 susan | 78 329 zena | 98 330 martie | 88 331 chris | 78 332 pat | 18 333 zola | 58 334 louise | 98 335 edna | 18 336 bertha | 88 337 sumi | 38 338 koko | 88 339 gina | 18 340 rean | 48 341 sharon | 78 342 paula | 68 343 julie | 68 344 belinda | 38 345 karen | 48 346 carina | 58 347 diane | 18 348 esther | 98 349 trudy | 88 350 fanny | 8 351 carmen | 78 352 lita | 25 353 pamela | 48 354 sandy | 38 355 trisha | 88 356 uma | 78 357 velma | 68 358 sharon | 25 359 sam | 30 360 bill | 20 361 fred | 28 362 larry | 60 363 jeff | 23 364 cim | 30 365 linda | 19 366(58 rows) 367 368-- 369-- awk '{print $1,$2;}' person.data | 370-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | 371-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | 372-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data | 373-- sort +1nr -2 374-- 375SELECT p.name, p.age FROM person* p ORDER BY age using >, name; 376 name | age 377---------+----- 378 esther | 98 379 louise | 98 380 zena | 98 381 bertha | 88 382 koko | 88 383 martie | 88 384 sarah | 88 385 trisha | 88 386 trudy | 88 387 carmen | 78 388 chris | 78 389 sharon | 78 390 susan | 78 391 uma | 78 392 wendy | 78 393 julie | 68 394 leah | 68 395 paula | 68 396 velma | 68 397 larry | 60 398 carina | 58 399 jane | 58 400 juanita | 58 401 zola | 58 402 sue | 50 403 karen | 48 404 pamela | 48 405 rean | 48 406 mike | 40 407 belinda | 38 408 jenifer | 38 409 liza | 38 410 sandy | 38 411 sumi | 38 412 teresa | 38 413 sally | 34 414 alex | 30 415 cim | 30 416 sam | 30 417 fred | 28 418 jean | 28 419 melissa | 28 420 nan | 28 421 lita | 25 422 sharon | 25 423 denise | 24 424 jeff | 23 425 bill | 20 426 joe | 20 427 linda | 19 428 sandra | 19 429 diane | 18 430 edna | 18 431 gina | 18 432 joan | 18 433 pat | 18 434 fanny | 8 435 mary | 8 436(58 rows) 437 438-- 439-- Test some cases involving whole-row Var referencing a subquery 440-- 441select foo from (select 1) as foo; 442 foo 443----- 444 (1) 445(1 row) 446 447select foo from (select null) as foo; 448 foo 449----- 450 () 451(1 row) 452 453select foo from (select 'xyzzy',1,null) as foo; 454 foo 455------------ 456 (xyzzy,1,) 457(1 row) 458 459-- 460-- Test VALUES lists 461-- 462select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) 463 WHERE onek.unique1 = v.i and onek.stringu1 = v.j; 464 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j 465---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+-------- 466 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA 467 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA 468(2 rows) 469 470-- a more complex case 471-- looks like we're coding lisp :-) 472select * from onek, 473 (values ((select i from 474 (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i) 475 order by i asc limit 1))) bar (i) 476 where onek.unique1 = bar.i; 477 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i 478---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+--- 479 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2 480(1 row) 481 482-- try VALUES in a subquery 483select * from onek 484 where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99)) 485 order by unique1; 486 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 487---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 488 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 489 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx 490 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx 491(3 rows) 492 493-- VALUES is also legal as a standalone query or a set-operation member 494VALUES (1,2), (3,4+4), (7,77.7); 495 column1 | column2 496---------+--------- 497 1 | 2 498 3 | 8 499 7 | 77.7 500(3 rows) 501 502VALUES (1,2), (3,4+4), (7,77.7) 503UNION ALL 504SELECT 2+2, 57 505UNION ALL 506TABLE int8_tbl; 507 column1 | column2 508------------------+------------------- 509 1 | 2 510 3 | 8 511 7 | 77.7 512 4 | 57 513 123 | 456 514 123 | 4567890123456789 515 4567890123456789 | 123 516 4567890123456789 | 4567890123456789 517 4567890123456789 | -4567890123456789 518(9 rows) 519 520-- 521-- Test ORDER BY options 522-- 523CREATE TEMP TABLE foo (f1 int); 524INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); 525SELECT * FROM foo ORDER BY f1; 526 f1 527---- 528 1 529 3 530 7 531 10 532 42 533 534 535(7 rows) 536 537SELECT * FROM foo ORDER BY f1 ASC; -- same thing 538 f1 539---- 540 1 541 3 542 7 543 10 544 42 545 546 547(7 rows) 548 549SELECT * FROM foo ORDER BY f1 NULLS FIRST; 550 f1 551---- 552 553 554 1 555 3 556 7 557 10 558 42 559(7 rows) 560 561SELECT * FROM foo ORDER BY f1 DESC; 562 f1 563---- 564 565 566 42 567 10 568 7 569 3 570 1 571(7 rows) 572 573SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 574 f1 575---- 576 42 577 10 578 7 579 3 580 1 581 582 583(7 rows) 584 585-- check if indexscans do the right things 586CREATE INDEX fooi ON foo (f1); 587SET enable_sort = false; 588SELECT * FROM foo ORDER BY f1; 589 f1 590---- 591 1 592 3 593 7 594 10 595 42 596 597 598(7 rows) 599 600SELECT * FROM foo ORDER BY f1 NULLS FIRST; 601 f1 602---- 603 604 605 1 606 3 607 7 608 10 609 42 610(7 rows) 611 612SELECT * FROM foo ORDER BY f1 DESC; 613 f1 614---- 615 616 617 42 618 10 619 7 620 3 621 1 622(7 rows) 623 624SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 625 f1 626---- 627 42 628 10 629 7 630 3 631 1 632 633 634(7 rows) 635 636DROP INDEX fooi; 637CREATE INDEX fooi ON foo (f1 DESC); 638SELECT * FROM foo ORDER BY f1; 639 f1 640---- 641 1 642 3 643 7 644 10 645 42 646 647 648(7 rows) 649 650SELECT * FROM foo ORDER BY f1 NULLS FIRST; 651 f1 652---- 653 654 655 1 656 3 657 7 658 10 659 42 660(7 rows) 661 662SELECT * FROM foo ORDER BY f1 DESC; 663 f1 664---- 665 666 667 42 668 10 669 7 670 3 671 1 672(7 rows) 673 674SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 675 f1 676---- 677 42 678 10 679 7 680 3 681 1 682 683 684(7 rows) 685 686DROP INDEX fooi; 687CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); 688SELECT * FROM foo ORDER BY f1; 689 f1 690---- 691 1 692 3 693 7 694 10 695 42 696 697 698(7 rows) 699 700SELECT * FROM foo ORDER BY f1 NULLS FIRST; 701 f1 702---- 703 704 705 1 706 3 707 7 708 10 709 42 710(7 rows) 711 712SELECT * FROM foo ORDER BY f1 DESC; 713 f1 714---- 715 716 717 42 718 10 719 7 720 3 721 1 722(7 rows) 723 724SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 725 f1 726---- 727 42 728 10 729 7 730 3 731 1 732 733 734(7 rows) 735 736-- 737-- Test planning of some cases with partial indexes 738-- 739-- partial index is usable 740explain (costs off) 741select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 742 QUERY PLAN 743----------------------------------------- 744 Index Scan using onek2_u2_prtl on onek2 745 Index Cond: (unique2 = 11) 746 Filter: (stringu1 = 'ATAAAA'::name) 747(3 rows) 748 749select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 750 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 751---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 752 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx 753(1 row) 754 755-- actually run the query with an analyze to use the partial index 756explain (costs off, analyze on, timing off, summary off) 757select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 758 QUERY PLAN 759----------------------------------------------------------------- 760 Index Scan using onek2_u2_prtl on onek2 (actual rows=1 loops=1) 761 Index Cond: (unique2 = 11) 762 Filter: (stringu1 = 'ATAAAA'::name) 763(3 rows) 764 765explain (costs off) 766select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 767 QUERY PLAN 768----------------------------------------- 769 Index Scan using onek2_u2_prtl on onek2 770 Index Cond: (unique2 = 11) 771 Filter: (stringu1 = 'ATAAAA'::name) 772(3 rows) 773 774select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 775 unique2 776--------- 777 11 778(1 row) 779 780-- partial index predicate implies clause, so no need for retest 781explain (costs off) 782select * from onek2 where unique2 = 11 and stringu1 < 'B'; 783 QUERY PLAN 784----------------------------------------- 785 Index Scan using onek2_u2_prtl on onek2 786 Index Cond: (unique2 = 11) 787(2 rows) 788 789select * from onek2 where unique2 = 11 and stringu1 < 'B'; 790 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 791---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 792 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx 793(1 row) 794 795explain (costs off) 796select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 797 QUERY PLAN 798---------------------------------------------- 799 Index Only Scan using onek2_u2_prtl on onek2 800 Index Cond: (unique2 = 11) 801(2 rows) 802 803select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 804 unique2 805--------- 806 11 807(1 row) 808 809-- but if it's an update target, must retest anyway 810explain (costs off) 811select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; 812 QUERY PLAN 813----------------------------------------------- 814 LockRows 815 -> Index Scan using onek2_u2_prtl on onek2 816 Index Cond: (unique2 = 11) 817 Filter: (stringu1 < 'B'::name) 818(4 rows) 819 820select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; 821 unique2 822--------- 823 11 824(1 row) 825 826-- partial index is not applicable 827explain (costs off) 828select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; 829 QUERY PLAN 830------------------------------------------------------- 831 Seq Scan on onek2 832 Filter: ((stringu1 < 'C'::name) AND (unique2 = 11)) 833(2 rows) 834 835select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; 836 unique2 837--------- 838 11 839(1 row) 840 841-- partial index implies clause, but bitmap scan must recheck predicate anyway 842SET enable_indexscan TO off; 843explain (costs off) 844select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 845 QUERY PLAN 846------------------------------------------------------------- 847 Bitmap Heap Scan on onek2 848 Recheck Cond: ((unique2 = 11) AND (stringu1 < 'B'::name)) 849 -> Bitmap Index Scan on onek2_u2_prtl 850 Index Cond: (unique2 = 11) 851(4 rows) 852 853select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 854 unique2 855--------- 856 11 857(1 row) 858 859RESET enable_indexscan; 860-- check multi-index cases too 861explain (costs off) 862select unique1, unique2 from onek2 863 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; 864 QUERY PLAN 865-------------------------------------------------------------------------------- 866 Bitmap Heap Scan on onek2 867 Recheck Cond: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0)) 868 Filter: (stringu1 < 'B'::name) 869 -> BitmapOr 870 -> Bitmap Index Scan on onek2_u2_prtl 871 Index Cond: (unique2 = 11) 872 -> Bitmap Index Scan on onek2_u1_prtl 873 Index Cond: (unique1 = 0) 874(8 rows) 875 876select unique1, unique2 from onek2 877 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; 878 unique1 | unique2 879---------+--------- 880 494 | 11 881 0 | 998 882(2 rows) 883 884explain (costs off) 885select unique1, unique2 from onek2 886 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; 887 QUERY PLAN 888-------------------------------------------------------------------------------- 889 Bitmap Heap Scan on onek2 890 Recheck Cond: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0)) 891 -> BitmapOr 892 -> Bitmap Index Scan on onek2_u2_prtl 893 Index Cond: (unique2 = 11) 894 -> Bitmap Index Scan on onek2_u1_prtl 895 Index Cond: (unique1 = 0) 896(7 rows) 897 898select unique1, unique2 from onek2 899 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; 900 unique1 | unique2 901---------+--------- 902 494 | 11 903 0 | 998 904(2 rows) 905 906-- 907-- Test some corner cases that have been known to confuse the planner 908-- 909-- ORDER BY on a constant doesn't really need any sorting 910SELECT 1 AS x ORDER BY x; 911 x 912--- 913 1 914(1 row) 915 916-- But ORDER BY on a set-valued expression does 917create function sillysrf(int) returns setof int as 918 'values (1),(10),(2),($1)' language sql immutable; 919select sillysrf(42); 920 sillysrf 921---------- 922 1 923 10 924 2 925 42 926(4 rows) 927 928select sillysrf(-1) order by 1; 929 sillysrf 930---------- 931 -1 932 1 933 2 934 10 935(4 rows) 936 937drop function sillysrf(int); 938-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict 939-- (see bug #5084) 940select * from (values (2),(null),(1)) v(k) where k = k order by k; 941 k 942--- 943 1 944 2 945(2 rows) 946 947select * from (values (2),(null),(1)) v(k) where k = k; 948 k 949--- 950 2 951 1 952(2 rows) 953 954