PostgreSQL 9.6.0 锟街诧拷
锟斤拷一页	锟斤拷一锟斤拷	锟斤拷 11. 锟斤拷锟斤拷	锟斤拷一页

11.11. 只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷

PostgreSQL锟叫碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷示每一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟芥储锟节憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷PostgreSQL锟斤拷锟斤拷锟叫憋拷锟斤拷为锟矫憋拷锟斤拷锟斤拷锟斤拷之锟解。锟斤拷锟斤拷味锟斤拷锟斤拷一锟斤拷锟斤拷通锟斤拷锟斤拷扫锟斤拷锟叫ｏ拷每锟斤拷取一锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟酵讹拷锟斤拷取锟斤拷锟斤拷锟捷★拷锟斤拷锟解，锟斤拷然锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟侥匡拷锟斤拷锟斤拷WHERE锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟叫讹拷锟斤拷拢锟斤拷一锟金，碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫的憋拷锟叫匡拷锟杰分诧拷锟节讹拷锟叫碉拷锟轿何地凤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟缴拷锟侥堆凤拷锟绞诧拷锟街匡拷锟杰伙拷锟芥及锟斤拷锟杰讹拷远训锟斤拷锟斤拷锟斤拷锟绞ｏ拷锟斤拷锟斤拷芑锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节达拷统锟侥达拷锟斤拷锟较ｏ拷锟斤拷锟斤拷 11.5 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷位图扫锟斤拷锟斤拷图通锟斤拷锟斤拷锟斤拷锟街达拷卸逊锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷挚锟斤拷锟斤拷锟斤拷锟斤拷锟叫э拷锟揭诧拷锟斤拷锟斤拷锟斤拷锟斤拷眩锟斤拷锟�

为锟剿斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷猓�PostgreSQL支锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷越锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷卮锟斤拷询锟斤拷锟斤拷锟斤拷锟斤拷锟轿何堆凤拷锟绞★拷锟斤拷锟斤拷锟剿硷拷锟斤拷锟街憋拷哟锟矫恳伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟街憋拷臃锟斤拷锟街碉拷锟斤拷锟斤拷锟斤拷锟饺ワ拷慰锟斤拷锟截的讹拷锟筋。锟斤拷使锟斤拷锟斤拷锟街凤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡ｏ拷

锟斤拷锟斤拷锟斤拷锟酵憋拷锟斤拷支锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借。B-锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷支锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借。GiST 锟斤拷 SP-GiST 锟斤拷锟斤拷只锟斤拷某些锟斤拷锟斤拷锟斤拷锟斤拷支锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借。锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵诧拷支锟斤拷锟斤拷锟斤拷扫锟借。锟阶诧拷锟揭拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷洗娲拷锟斤拷呖锟斤拷锟斤拷毓锟斤拷锟矫恳伙拷锟斤拷锟斤拷锟斤拷锟斤拷应锟斤拷原始锟斤拷锟斤拷值锟斤拷GIN 锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷支锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷姆锟斤拷锟斤拷锟斤拷锟轿拷锟斤拷锟矫恳伙拷锟斤拷锟斤拷锟斤拷锟酵拷锟街伙拷锟斤拷锟皆硷拷锟斤拷锟街碉拷锟揭伙拷锟斤拷帧锟�
锟斤拷询锟斤拷锟斤拷只锟斤拷锟矫存储锟节革拷锟斤拷锟斤拷锟叫碉拷锟叫★拷锟斤拷锟界，锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节憋拷锟斤拷锟斤拷x锟斤拷y锟较ｏ拷锟斤拷锟矫憋拷锟斤拷锟斤拷一锟斤拷锟斤拷z锟斤拷锟斤拷些锟斤拷询锟斤拷锟斤拷使锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借：
```
SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;
```
锟斤拷锟斤拷锟斤拷些锟斤拷询锟斤拷锟斤拷使锟斤拷只锟斤拷锟斤拷锟斤拷锟侥诧拷询锟斤拷
```
SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;
```
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟桔的ｏ拷锟斤拷锟斤拷式锟斤拷锟斤拷锟酵诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷痈锟斤拷樱锟斤拷锟�

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭拷锟斤拷锟矫达拷貌锟窖拷锟揭拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷值锟斤拷锟斤拷锟皆达拷锟斤拷锟斤拷锟矫碉拷锟斤拷锟斤拷瞬趴锟斤拷锟绞癸拷锟街伙拷锟斤拷锟斤拷锟斤拷锟缴拷琛ｏ拷锟斤拷嵌锟�PostgreSQL锟叫碉拷锟轿何憋拷扫锟借还锟斤拷一锟斤拷锟斤拷锟斤拷锟揭拷螅罕锟斤拷锟斤拷锟街っ恳伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷卸愿貌锟窖拷锟� MVCC 锟斤拷锟斤拷锟斤拷"锟缴硷拷锟斤拷"锟斤拷锟斤拷锟斤拷 13 锟斤拷锟斤拷锟斤拷锟桔碉拷锟斤拷锟斤拷锟斤拷锟缴硷拷锟斤拷锟斤拷息锟斤拷锟斤拷锟芥储锟斤拷锟斤拷锟斤拷锟斤拷锟叫ｏ拷只锟芥储锟节讹拷锟斤拷锟叫★拷锟斤拷耍锟秸б伙拷锟斤拷坪锟矫恳伙拷锟斤拷屑锟斤拷锟斤拷锟斤拷锟斤拷锟轿讹拷锟斤拷要锟斤拷一锟轿堆凤拷锟绞★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟睫改癸拷锟斤拷确实锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角ｏ拷锟斤拷锟节猴拷锟劫革拷锟侥碉拷锟斤拷锟斤拷锟斤拷一锟街凤拷锟斤拷锟斤拷锟皆斤拷锟斤拷锟斤拷锟斤拷锟解。PostgreSQL为锟斤拷锟斤拷锟叫碉拷每一锟斤拷页锟斤拷锟斤拷锟斤拷欠锟斤拷锟斤拷锟斤拷锟斤拷械锟斤拷械锟斤拷锟斤拷涠硷拷愎伙拷锟斤拷锟斤拷锟斤拷诙锟斤拷锟斤拷械锟角帮拷约锟轿达拷锟斤拷锟斤拷锟斤拷穸伎杉锟斤拷锟斤拷锟斤拷锟斤拷息锟芥储锟节该憋拷锟斤拷锟缴硷拷锟斤拷映锟斤拷锟斤拷一锟斤拷位锟叫★拷锟斤拷锟揭碉拷一锟斤拷锟斤拷选锟斤拷锟斤拷锟斤拷锟街伙拷锟斤拷锟斤拷锟斤拷锟缴拷锟斤拷锟斤拷锟接︼拷锟揭筹拷锟侥可硷拷锟斤拷映锟斤拷位锟斤拷锟斤拷锟斤拷锟轿伙拷锟斤拷锟斤拷茫锟斤拷锟矫达拷锟揭伙拷芯锟斤拷强杉锟斤拷牟锟斤拷腋锟斤拷锟斤拷菘锟斤拷锟斤拷直锟接憋拷锟斤拷锟截★拷锟斤拷锟斤拷锟轿幻伙拷斜锟斤拷锟斤拷茫锟斤拷锟矫达拷捅锟斤拷锟斤拷锟绞讹拷锟斤拷锟斤拷确锟斤拷锟斤拷一锟斤拷锟角凤拷杉锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷诒锟阶硷拷锟斤拷锟缴拷锟斤拷没锟斤拷锟斤拷锟斤拷锟斤拷锟狡★拷锟斤拷锟斤拷锟斤拷锟节成癸拷锟斤拷锟斤拷锟斤拷拢锟斤拷锟斤拷址锟斤拷锟揭诧拷前讯远训姆锟斤拷驶锟斤拷锟斤拷硕钥杉锟斤拷锟接筹拷锟侥凤拷锟绞★拷锟斤拷锟斤拷锟斤拷锟节可硷拷锟斤拷映锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷亩锟揭★拷母锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷苑锟斤拷士杉锟斤拷锟接筹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 I/O 要锟劫很多。锟节大部凤拷锟斤拷锟斤拷拢锟斤拷杉锟斤拷锟接筹拷锟斤拷锟斤拷腔岜伙拷锟斤拷锟斤拷锟斤拷诖锟斤拷械幕锟斤拷锟斤拷小锟�

锟斤拷之锟斤拷锟斤拷然锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷使锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借，锟斤拷锟斤拷只锟叫该憋拷锟侥讹拷页锟斤拷锟斤拷锟叫很达拷一锟斤拷锟街的★拷锟斤拷锟叫讹拷锟缴硷拷锟斤拷映锟斤拷位锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡★拷锟斤拷锟斤拷锟斤拷锟叫很达拷一锟斤拷锟斤拷锟叫诧拷锟斤拷锟斤拷锟侥的憋拷锟角很筹拷锟斤拷锟侥ｏ拷锟斤拷也锟斤拷锟斤拷一锟斤拷扫锟斤拷锟斤拷实锟斤拷锟叫非筹拷锟斤拷锟矫★拷

为锟斤拷锟斤拷效锟斤拷使锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借，锟斤拷锟斤拷选锟今创斤拷时只锟斤拷前锟斤拷锟斤拷锟斤拷匹锟斤拷WHERE锟接句，锟斤拷锟矫猴拷锟斤拷锟斤拷斜锟斤拷锟�"锟截猴拷"锟斤拷锟斤拷锟斤拷锟皆凤拷锟截诧拷询锟斤拷锟斤拷锟斤拷锟斤拷荨锟斤拷锟斤拷纾拷锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷锟侥诧拷询

SELECT y FROM tab WHERE x = 'key';

锟斤拷锟斤拷锟斤拷锟斤拷锟窖拷拇锟酵筹拷锟斤拷锟斤拷锟街伙拷锟�x锟较达拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角ｏ拷锟斤拷(x, y)锟较达拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟皆斤拷锟斤拷锟街诧拷询实锟斤拷为只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借。锟斤拷锟斤拷之前锟斤拷锟桔的ｏ拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷只锟斤拷x锟较碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷丝锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷只锟斤拷知锟斤拷锟斤拷锟斤拷锟斤拷锟角撅拷态时锟斤拷锟街凤拷锟斤拷锟脚撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷注锟解，锟斤拷锟斤拷要锟斤拷一锟斤拷锟角ｏ拷锟斤拷锟斤拷锟斤拷应锟矫讹拷锟斤拷锟斤拷(x, y)锟斤拷锟斤拷锟斤拷(y, x)锟较★拷锟斤拷锟斤拷锟斤拷为锟斤拷锟节大部凤拷锟斤拷锟斤拷锟斤拷锟酵ｏ拷锟截憋拷锟斤拷 B-锟斤拷锟斤拷锟斤拷说锟斤拷锟斤拷锟斤拷锟斤拷前锟斤拷锟斤拷锟斤拷锟叫碉拷锟斤拷锟斤拷效锟绞诧拷锟竭★拷

原锟斤拷锟较ｏ拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷员锟斤拷锟斤拷诒锟斤拷锟绞斤拷锟斤拷锟斤拷锟斤拷锟斤拷纾拷锟斤拷锟揭伙拷锟�f(x)锟较碉拷锟斤拷锟斤拷锟斤拷x锟斤拷一锟斤拷锟斤拷锟叫ｏ拷锟斤拷锟斤拷锟皆帮拷

SELECT f(x) FROM tab WHERE f(x) < 1;

锟斤拷为只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷执锟叫ｏ拷锟斤拷锟�f()锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷郯锟斤拷锟侥猴拷锟斤拷锟斤拷锟斤拷锟角筹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷PostgreSQL锟侥规划锟斤拷锟斤拷前锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟角很达拷锟斤拷锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫诧拷询锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟芥划锟斤拷锟脚会考锟斤拷锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷执锟斤拷一锟斤拷锟斤拷询锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷校锟斤拷锟斤拷锟斤拷锟�f(x)锟斤拷锟斤拷锟斤拷之锟解，锟斤拷询锟斤拷锟斤拷锟斤拷锟斤拷锟街诧拷锟斤拷要x锟斤拷锟斤拷锟角规划锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷识锟斤拷锟斤拷一锟姐，锟斤拷锟斤拷锟斤拷锟矫筹拷锟斤拷锟斤拷使锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷慕锟斤拷邸锟斤拷锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟姐够锟叫硷拷值锟斤拷锟斤拷一锟街斤拷锟斤拷锟斤拷锟斤拷前迅锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�(f(x), x)锟较ｏ拷锟斤拷锟叫第讹拷锟斤拷锟斤拷实锟斤拷锟较诧拷锟斤拷锟结被使锟矫ｏ拷锟斤拷只锟斤拷锟斤拷锟斤拷说锟斤拷锟芥划锟斤拷锟斤拷锟斤拷使锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷选锟斤拷锟斤拷目锟斤拷锟角憋拷锟斤拷锟截革拷锟斤拷锟斤拷f(x)锟斤拷一锟斤拷锟斤拷锟斤拷木锟绞撅拷枪婊拷锟斤拷锟揭伙拷锟斤拷锟窖诧拷锟节匡拷锟斤拷锟斤拷WHERE锟接撅拷锟叫讹拷f(x)锟斤拷使锟斤拷匹锟戒到锟斤拷锟斤拷锟叫★拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街简单诧拷询锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟芥及锟斤拷锟斤拷锟接的诧拷询锟叫就诧拷锟斤拷锟剿★拷锟斤拷些锟斤拷锟姐将锟斤拷未锟斤拷锟斤拷PostgreSQL锟芥本锟斤拷锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷也锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷之锟斤拷锟斤拷锟斤拷锟斤拷趣锟侥癸拷系锟斤拷锟斤拷锟斤拷锟斤拷 11-3锟斤拷锟斤拷展示锟侥诧拷锟斤拷锟斤拷锟斤拷锟斤拷

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

原锟斤拷锟较ｏ拷锟斤拷锟角匡拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞癸拷锟街伙拷锟斤拷锟斤拷锟斤拷锟缴拷锟斤拷锟斤拷锟斤拷锟斤拷询

SELECT target FROM tests WHERE subject = 'some-subject' AND success;

锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟解：WHERE锟接撅拷锟斤拷锟矫碉拷锟角诧拷锟斤拷锟斤拷为锟斤拷锟斤拷锟斤拷锟斤拷械锟�success锟斤拷锟斤拷锟斤拷锟斤拷耍锟斤拷锟斤拷强锟斤拷锟绞癸拷锟街伙拷锟斤拷锟斤拷锟斤拷锟缴拷瑁拷锟轿拷锟斤拷锟斤拷锟绞憋拷苹锟斤拷锟斤拷锟揭拷锟斤拷录锟斤拷WHERE锟接撅拷锟斤拷歉锟斤拷锟斤拷郑锟斤拷诟锟斤拷锟斤拷锟斤拷锟斤拷业锟斤拷锟斤拷锟斤拷锟斤拷锟截讹拷锟斤拷锟斤拷success = true锟斤拷锟斤拷锟斤拷诩苹锟斤拷屑锟斤拷锟斤拷锟斤拷锟斤拷值锟斤拷锟揭拷锟斤拷锟斤拷锟斤拷浴锟�PostgreSQL 9.6 锟斤拷锟皆猴拷陌姹撅拷锟斤拷锟绞讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷扫锟借，锟斤拷锟角旧版本锟睫凤拷锟斤拷锟斤拷锟斤拷锟斤拷

锟斤拷一页	锟斤拷始页	锟斤拷一页
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�	锟斤拷一锟斤拷	锟斤拷锟斤拷锟斤拷锟绞癸拷锟�