PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔	䆹5. 昄扞垔幬	媆誕	嬉誕

5.9. 彖寺

PostgreSQL 櫇毕嘺橸䔇臘彖寺媘脘㔗橸誗柟誄婺傔幽驔襕臘彖寺傖埪套嘘婘昄扞康螆螇婺嘪䫘臘彖寺㔗

5.9.1. 楗誄

彖寺䔇懟攺滇檪锂膏婪䔇婔婻崓臘彖嬾潊䬷䊖婪䔇庹庖㔗彖寺埇傖柊冕苖幾喘崇

昊底䌂傋䔇昖臵攓脘埇傖冖彄悕崓柊剺㔗䬹彆滇臘婺螪閞䯺膄醻䔇臯嘉庯婔婻剘䋸彖寺潡儏昄庹婻彖寺婪䔇愙喕婋㔗彖寺埇傖废儏䘵嚘嘷䓇傯蔯埇傖儖醻嘪䫘䯺鄘彖䔇䘵嚘庻櫆婘喙庻婺㔗套悩䘵嚘婉脘噘鄘櫆婘喙庻婺闼幽婘䘵嚘婪䔇臂启喍鄘嚔库䫘敘崔䔇伕䕻螪閞㔗
嘷昖臵潡敘桄婔婻彖寺䔇崓鄘彖螄嘘施誂䂺欆柟闼婻彖寺蔯婉滇嘪䫘䘵嚘䥂昼䔇螪閞昘婻臘埇傖诙冖噘崓䔇攓脘柊剺㔗
套悩驔襕崓麟媹蘘潡蔙役鍴䔇螄嘘嘉庯剘䋸䔇彖寺婪闼幽埇傖锔誺䕘毖臂埡潡役鍴闼婻彖寺傖诙冖噘崓䔇攓脘柊剺啹婺 ALTER TABLE 懫淉嘩崓麟䔇昄扞襕媆䔇崔㔗垄劯施誻埇傖镪噉䫌庯崓麟 DELETE 凚躘䔇 VACUUM 轙蘘㔗
冽儏䫘䔇昄扞埇傖䓂媘彄冪垩婔底䔇晵锘庻嗘傋蘘婪㔗

認䓉喘崇锔婩埻橬婘臘埇脘嚔埻冖麂婩崓䔇愙喕婋欉橬備唚㔗彄庘崔崓䔇臘嚔傯彖寺婺櫽䕪埡喿庯噙嘷䔇庫䫘婉誺橬婻嘺橸䔇拺毺蓇彍儌滇臘䔇崓償轙誺庖昄扞康橉媇単䔇䬷䊖喙庻崓償㔗

䕞嬉PostgreSQL 櫇毕锔誺臘䂓欪誕臯彖寺㔗懟婻彖寺媙釂啔婺剘䋸婔婻佽臘䔇床臘誕臯录傺㔗佽臘躻躆锔婩滇䷺䔇垄䔇庻婘埻滇婺庖傼臘昘婻昄扞镖㔗嘹婘臘商垂䯄彖寺幋嬉庫臖噽䖘旬䂓欪(埗黙誗5.8)㔗

PostgreSQL 埇傖垂䯄婋麵嘵嚟䔇彖寺

评啘彖寺: 臘赆婔婻潡蔙崔婻噿髞庖枕彖寺潊"评啘"認底评啘婘婉劯䔇彖寺麯澇橬麉培㔗懫套潏傸埇傖婺䬹垔䔇嘖婔凹茇湹扞昄扞评啘彖寺潡蔙湹扞湺臖严评啘彖寺㔗
彖臘彖寺: 臘锔誺滯䇞婄彖庺懟婻彖寺麯庫臖庺䯄闼底噿髞庖唚垂䯄㔗

5.9.2. 垂䯄彖寺

襕螆䘞婔婻彖寺䔇臘啔婋麵䔇準黴

录傺"婂臘"欔橬彖寺鄘傯垄䂓欪㔗

認婻臘婺澇橬昄扞婉襕婘認婻臘婪垔幬傂嘘演昖亥溘鍴麂嘹婯橕亥溘劯湙幘锗䫘庯欔橬彖寺㔗劯湙婘噽婪垔幬傂嘘䘵嚘潡蔙嫇婔亥溘幘澇橬懟幬㔗
录傺庹婻"床臘"懟婻鄘傯婂臘婪䂓欪㔗锔婩認底臘婉嚔嵂媹傂嘘庖枕㔗

潏傸儖檪床臘䓄嘩彖寺儘䞇垄傸儌滇捞锔䔇 PostgreSQL 臘㔗
䂍彖寺臘嵂媹亥溘垔幬懟婻彖寺噕螩䔇啖唚㔗

噩傋䔇冋床滇
```
CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
```
䇞媺認底亥溘脘崘媺臕婘婉劯䔇彖寺麯婉嚔橬麉培䔇髞唚㔗婔婻婩蓕䔇髍臇滇螆䘞婋麵認湙䔇评啘
```
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
```
認湙啔滇髍臇䔇啹婺垄澇臘橙斔啖唚 200 匂庯闼婻评啘㔗

臙濘懟婘评啘启彖臘彖寺䔇臺濘桹麵澇橬傔幽寺彆認底橇臺埻滇䫘庯柟誄䔇㔗
凹庯懟婻彖寺婘噿髞庖庖枕婪录傺婔婻䘵嚘傖埪噽垄嘹愿录傺䔇䘵嚘㔗噿髞庖庖枕䘵嚘幽麂婖湚媙驔䔇嘖滇婘崓崔昄愙喕婋垄滇冽橬婞媷䔇㔗套悩嘹婯橕噿髞庖唚滇嫇婔䔇闼幽嘹庫臖攂滇䂍懟婻彖寺录傺婔婻嫇婔潡蔙婂髞亥溘㔗
埥崡垔幬婔婻蓇彍潡蔙蓥埏単檪凹婂臘䔇媞櫹麉垔劏彄劽锗䔇彖寺臘㔗
䇞媺 postgresql.conf 麯䔇陉䘞埗昄 constraint_exclusion 滇欷嚔䔇㔗澇橬認婻埗昄昖臵婉嚔毬䙓驔襕誕臯嚻寡㔗

懫套啺螆潏傸婺婔婻噘崓䔇善檔庯噸埩悇锹昄扞康㔗臖噸埩懟崷鄘敋麟橔醻橷庥傖埪懟婻婄寺䔇善檔庯體嫞㔗楗媕婪潏傸驔襕婔婻認湙䔇臘

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

潏傸䘖長崓崔昄昖臵鄘埻嚔螪閞橔劯婔变橔劯婔婻橽潡蔙橔劯婔婻庼庥䔇昄扞啹婺認婻臘䔇婂襕䫘锫滇婺䞇䊖庖崺婘亪檖只㔗婺庖废儏驔襕庻嗘䔇斓昄扞潏傸喿垔唚媺䘍橔誏婬幘䔇橬䫘昄扞㔗婘懟婻橽䔇嚔崘潏傸鄘嚔役鍴橔斓䔇婔婻橽䔇昄扞㔗

婘認䓉愙喕婋潏傸埇傖嘪䫘彖寺準婞媷垂䯄欔橬凹臘䔇婉劯驔挗㔗婋麵䔇準黴柟誄庖婪麵䔇驔挗彖寺埇傖認湙螆䘞

婂臘滇 measurement 臘儌償婪麵闼湙弄滯㔗
䇽劯潏傸婺懟婻橽录傺婔婻彖寺
```
CREATE TABLE measurement_y2004m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);
```
懟婻彖寺鄘滇拖橬躻噌喙垹䔇垯昘䔇臘埻滇垄傸傯 measurement 臘䂓欪垔幬㔗

認湙儌蓼喿庖潏傸䔇婔婻閞鵻役鍴斓昄扞㔗懟婻橽潏傸驔襕啔䔇埻滇婘橔斓䔇床臘婪欓臯婔婻 DROP TABLE 䇽劯婺桄橽傘录傺婔婻桄䔇床臘㔗

潏傸媙釂嵂媹麂麉培䔇臘亥溘欔傖潏傸䔇傺臘臔橸儌埻潊

CREATE TABLE measurement_y2004m02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 (
    CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
    CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);

潏傸埇脘誻驔襕婘噿髞庖庖枕婪橬䘵嚘

CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate);
CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate);
...
CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate);
CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate);
CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);

潏傸锬拷噽婉傺䆋敘崔䔇䘵嚘㔗

套悩昄扞埻誕噖橔桄䔇彖寺潏傸埇傖螆䘞婔婻麂婩䞔剘䔇蓇彍準某噖昄扞㔗潏傸媙釂懟婻橽鄘麉桄垔幬認婻蓇彍認湙垄攂滇毺劏嘷嬉彖寺㔗

CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

潏傸埇脘愿某噖昄扞幽婫愿螷橉媇単躻媘垔嘉庫臖劏巻婻彖寺某噖昄扞㔗潏傸埇傖䫘婋麵認婻崉溗䔇蓇彍镖準垂䯄認婻䕞湺㔗

CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
    INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
CREATE RULE measurement_insert_y2006m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

臙濘懟懟婻蓇彍麯麵䔇 WHERE 床埖溼喘对陉噽彖寺䔇 CHECK 亥溘㔗

潏傸埇傖䩋庺婔婻崉溗䔇彖寺桹懽埇脘襕挗䕩嘷崔䔇 DDL 㔗婘婪麵䔇冋床麯潏傸驔襕懟婻橽录傺婔渇桄彖寺啹溴喍婔婻臔橸躻媘䫘潊驔襕䔇 DDL 滇滯捺䔇㔗

彖寺幘埇傖嘪䫘婔婻 UNION ALL 蓖商準垬毐

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2004m02
UNION ALL SELECT * FROM measurement_y2004m03
...
UNION ALL SELECT * FROM measurement_y2005m11
UNION ALL SELECT * FROM measurement_y2005m12
UNION ALL SELECT * FROM measurement_y2006m01;

嘖滇麉傺臘商幘䂍嵂媹启役鍴昄扞镖麯䔇䋸䆋彖寺嵂媹庖鵺崡䔇準黴㔗

5.9.3. 䞇䊖彖寺

锔婩彖寺镖婘垔幬臘䔇施唍儌噾䂟䇞垔庖嘖潏傸婩婩驔襕变橘攓䔇役鍴斓彖寺幽時媹桄彖寺㔗彖寺橔麉襕䔇喘崇滇垄脘敄彄喘崇䔇锗庫認婻驔挗傖悕媆䔇锘庥淉嘩彖寺䔇䂷悇蔯婉滇䖕若䔇䬷䊖䓂媘崓麟昄扞㔗

役鍴斓昄扞橔䞔剘䔇桹濘滇役鍴婉喉驔襕䔇彖寺

DROP TABLE measurement_y2003m02;

認婻变傴埇傖誙锘役鍴昄寙劆昄䍆婺溇螄嘘䔇彖寺啹婺垄婉驔襕剘䋸役鍴懟婔溇螄嘘㔗

誻埇傖婘役鍴彖寺䔇劯施媺䘍噽嘩婺婔婻臘螪閞䔇脘媕

ALTER TABLE measurement_y2003m02 NO INHERIT measurement;

認儖噕螩儖準凹認底昄扞欓臯噽垄䔇淉嘩(懫套嘪䫘 COPY, pg_dump 幋䌂䔇噖噙誕臯崺傘)㔗幽婫溴施幘滇欓臯噽垄昄扞淉(昄扞蕔镖潡誊臯檖臘京)䔇橬彷施橺㔗

劯湙潏傸埇傖償嬉麵录傺橔彺䔇彖寺婔湙录傺婔婻桄䔇䷺彖寺準崇䊖桄昄扞㔗

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

橬施婘彖寺䂷悇幋崡录傺桄臘幽婘婔枕施閘幋劯儖噽埻婺彖寺敘婺桹冪㔗啹婺認儖噕螩婘臖臘埻婺彖寺幋嬉凹噽婺䔇昄扞誕臯媹蘘㔕演昖㔕蘸扵幋䌂䔇淉嘩㔗

CREATE TABLE measurement_y2006m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02
   CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' );
\copy measurement_y2006m02 from 'measurement_y2006m02'
-- 噽垄埇脘䔇昄扞庖崺噖嘩
ALTER TABLE measurement_y2006m02 INHERIT measurement;

5.9.4. 彖寺启亥溘毐鍴

亥溘毐鍴滇婔䓉昖臵嚻寡檔噓垄櫹誕庖䫘婪誄桹濘垔幬䔇臘彖寺䔇攓脘㔗懫套

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

套悩澇橬亥溘毐鍴婪麵䔇昖臵嚔欆柟 measurement 臘婺䔇懟婔婻彖寺㔗欷嚔庖亥溘毐鍴幋劯蓇彐単儖演昖懟婻彖寺䔇亥溘䇽劯臘商臕滯臖彖寺婉驔襕赆欆柟(啹婺垄婉脘寙劆傂嘘严劽 WHERE 床埖溇傽䔇昄扞臯)㔗套悩蓇彐単埇傖臕滯認婻垄儌檪臖彖寺傯昖臵蓇彐麯毐鍴庺寂㔗

嘹埇傖嘪䫘 EXPLAIN 变傴滆䴺婔婻蓇彐婘 constraint_exclusion 欷嚔启噿閺愙喕婋䔇婉劯㔗䫘婪麵桹濘螆䘞䔇臘䔇噩傋䔇䚺䩕蓇彐滇

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
...
         ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

鄘彖潡蔙噘鄘彖寺埇脘嚔嘪䫘䘵嚘欆柟蔯婉滇噘臘欆柟婉誺認麯襕臘膆䔇懟攺滇澇橬媙襕欆柟斓彖寺儌埇傖啂享認婻昖臵㔗婘欷嚔亥溘毐鍴幋劯潏傸埇傖冖彄䫘潊劯湙啂享䔇滯滆䞔寡䔇蓇彐

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

臙濘懟亥溘毐鍴埻䫌 CHECK 亥溘鷌媘蔯婉嚔䫌䘵嚘鷌媘㔗啹溴婘噿髞庖庖枕婪垔幬䘵嚘滇澇橬媙襕䔇㔗婘䂍庺䔇彖寺婪滇劥驔襕傺䆋䘵嚘埡喿庯闼底欆柟臖彖寺䔇昖臵锔婩滇欆柟臖彖寺䔇婔崓鄘彖誻滇埻滇婔償鄘彖㔗凹庯劯蔙䘵嚘锔婩鄘橬婞媷凹庯嬉蔙彍澇橬傔幽喘崇㔗

5.9.5. 躥只

婋麵䔇濘懟庋釹锗劽庯噾彖寺䔇臘

嘷嬉澇橬媂濘黯臕欔橬䔇 CHECK 亥溘滇庐桖䔇㔗昄扞康䔇螆螇蔙媙釂躻噌媺臕認婔䗹㔗
嘷嬉澇橬䞔冪䔇桹濘媺臕昄扞婉赆某噖婂臘㔗婂臘婪䔇 CHECK (false) 亥溘儖赆欔橬彖寺䂓欪欔傖婉脘䫘庯溴䕞䔇㔗婔䓉桹濘滇婘婂臘婪螆䘞婔婻哋䂽檖髍䔇 ON INSERT 蓥埏単潡蔙儖某噖淉嘩麉垔劏躿溼䇞䔇床臘(傯蔯镪噉婪誄傺螞䔇婔崓幖蓇彍)㔗

婋麵䔇濘懟庋釹锗劽庯亥溘毐鍴

亥溘毐鍴埻滇婘 WHERE 床埖寙劆亥溘䔇施唍欉䫘昽㔗婔婻埗昄寡䔇昖臵婉嚔赆嚻寡啹婺婘誊臯施蓇彐単婉䘖長臖埗昄嚔锬拷巻婻彖寺㔗䫌庯昊底寘啹償 CURRENT_DATE 認湙"䘿垔䔇"庘昄媙釂镪噉㔗
婘 CHECK 亥溘麯麵镪噉虘昄扞䌂傋䔇懫膄啹婺䕞嬉蓇彐単嚔方濘臕滯認湙䔇溇傽婺啺㔗懫套婋麵䔇亥溘嚔婘 x 滇 integer 庖枕䔇施唍埇䫘嘖滇婘 x 滇婔婻 bigint 䔇施唍婉脘䫘
```
CHECK ( x = 1 )
```
凹庯 bigint 庖枕潏傸媙釂嘪䫘䌂嚚婋麵認湙䔇亥溘
```
CHECK ( x = 1::bigint )
```
認婻閞鵻幽婉備備匔鍊庯 bigint 昄扞䌂傋垄埇脘嚔埏䫘婘傂嘘亥溘䔇䚺䩕昄扞䌂傋婯噽懫膄䔇庖枕䔇昄扞䌂傋婉对陉䔇婺劽㔗婘柊庴䔇昖臵麯䔇虘昄扞䌂傋䔇懫膄锔婩滇埇傖䔇埻滇婉脘婘 CHECK 溇傽麯㔗
婂臘䔇欔橬彖寺婪麵䔇欔橬亥溘鄘螴婺滇亥溘毐鍴庖䔇啹溴崓麟䔇彖寺嚔滆菖嵂媹螇䞖昖臵蓇彐䔇施閘㔗
彆媻螄嘹傉䇽驔襕婺懟婻彖寺䋸䆋誊臯 ANALYZE 㔗䌂嚚婋麵䔇变傴
```
ANALYZE measurement;
```
埻嚔崇䊖婂臘㔗

劯锔	饡釕	嬉誕
䂓欪	婪婔亓	噽垄昄扞康凹茇