PostgreSQL教程(三)：表的繼承和分區(qū)表詳解

更新時(shí)間：2015年05月04日 09:12:45 投稿：junjie

這篇文章主要介紹了PostgreSQL教程(三)：表的繼承和分區(qū)表詳解,本文講解了多表繼承、繼承和權(quán)限、什么是分區(qū)表、分區(qū)表實(shí)現(xiàn)、分區(qū)和約束排除等內(nèi)容,需要的朋友可以參考下

一、表的繼承：

這個(gè)概念對(duì)于很多已經(jīng)熟悉其他數(shù)據(jù)庫(kù)編程的開發(fā)人員而言會(huì)多少有些陌生，然而它的實(shí)現(xiàn)方式和設(shè)計(jì)原理卻是簡(jiǎn)單易懂，現(xiàn)在就讓我們從一個(gè)簡(jiǎn)單的例子開始吧。
1. 第一個(gè)繼承表：

復(fù)制代碼代碼如下:

    CREATE TABLE cities (   --父表

        name        text,

        population float,

        altitude     int

    );

    CREATE TABLE capitals ( --子表

        state      char(2)

    ) INHERITS (cities);

capitals表繼承自cities表的所有屬性。在PostgreSQL里，一個(gè)表可以從零個(gè)或多個(gè)其它表中繼承屬性，而且一個(gè)查詢既可以引用父表中的所有行，也可以引用父表的所有行加上其所有子表的行，其中后者是缺省行為。

復(fù)制代碼代碼如下:

    MyTest=# INSERT INTO cities values('Las Vegas', 1.53, 2174);  --插入父表

    INSERT 0 1

    MyTest=# INSERT INTO cities values('Mariposa',3.30,1953);     --插入父表

    INSERT 0 1

    MyTest=# INSERT INTO capitals values('Madison',4.34,845,'WI');--插入子表

    INSERT 0 1

    MyTest=# SELECT name, altitude FROM cities WHERE altitude > 500; --父表和子表的數(shù)據(jù)均被取出。

       name     | altitude

    -----------+----------

     Las Vegas |     2174

     Mariposa   |     1953

     Madison    |      845

    (3 rows)

    MyTest=# SELECT name, altitude FROM capitals WHERE altitude > 500; --只有子表的數(shù)據(jù)被取出。

      name   | altitude

    ---------+----------

     Madison |      845

    (1 row)

如果希望只從父表中提取數(shù)據(jù)，則需要在SQL中加入ONLY關(guān)鍵字，如：

復(fù)制代碼代碼如下:

    MyTest=# SELECT name,altitude FROM ONLY cities WHERE altitude > 500;

       name     | altitude

    -----------+----------

     Las Vegas |     2174

     Mariposa   |     1953

    (2 rows)

上例中cities前面的"ONLY"關(guān)鍵字表示該查詢應(yīng)該只對(duì)cities進(jìn)行查找而不包括繼承級(jí)別低于cities的表。許多我們已經(jīng)討論過(guò)的命令--SELECT，UPDATE和DELETE--支持這個(gè)"ONLY"符號(hào)。
在執(zhí)行整表數(shù)據(jù)刪除時(shí)，如果直接truncate父表，此時(shí)父表和其所有子表的數(shù)據(jù)均被刪除，如果只是truncate子表，那么其父表的數(shù)據(jù)將不會(huì)變化，只是子表中的數(shù)據(jù)被清空。

復(fù)制代碼代碼如下:

    MyTest=# TRUNCATE TABLE cities;  --父表和子表的數(shù)據(jù)均被刪除。

    TRUNCATE TABLE

    MyTest=# SELECT * FROM capitals;

     name | population | altitude | state

    ------+------------+----------+-------

    (0 rows)

2. 確定數(shù)據(jù)來(lái)源：
有時(shí)候你可能想知道某條記錄來(lái)自哪個(gè)表。在每個(gè)表里我們都有一個(gè)系統(tǒng)隱含字段tableoid，它可以告訴你表的來(lái)源：

復(fù)制代碼代碼如下:

    MyTest=# SELECT tableoid, name, altitude FROM cities WHERE altitude > 500;

     tableoid |   name    | altitude

    ----------+-----------+----------

        16532 | Las Vegas |     2174

        16532 | Mariposa  |     1953

        16538 | Madison   |      845

    (3 rows)

以上的結(jié)果只是給出了tableoid，僅僅通過(guò)該值，我們還是無(wú)法看出實(shí)際的表名。要完成此操作，我們就需要和系統(tǒng)表pg_class進(jìn)行關(guān)聯(lián)，以通過(guò)tableoid字段從該表中提取實(shí)際的表名，見以下查詢：

復(fù)制代碼代碼如下:

    MyTest=# SELECT p.relname, c.name, c.altitude FROM cities c,pg_class p WHERE c.altitude > 500 and c.tableoid = p.oid;

     relname  |   name    | altitude

    ----------+-----------+----------

     cities    | Las Vegas |     2174

     cities    | Mariposa   |     1953

     capitals | Madison    |      845

    (3 rows)

3. 數(shù)據(jù)插入的注意事項(xiàng)：
繼承并不自動(dòng)從INSERT或者COPY中向繼承級(jí)別中的其它表填充數(shù)據(jù)。在我們的例子里，下面的INSERT語(yǔ)句不會(huì)成功：

復(fù)制代碼代碼如下:

    INSERT INTO cities (name, population, altitude, state) VALUES ('New York', NULL, NULL, 'NY');

 

    我們可能希望數(shù)據(jù)被傳遞到capitals表里面去，但是這是不會(huì)發(fā)生的：INSERT總是插入明確聲明的那個(gè)表。

    4. 多表繼承：
    一個(gè)表可以從多個(gè)父表繼承，這種情況下它擁有父表們的字段的總和。子表中任意定義的字段也會(huì)加入其中。如果同一個(gè)字段名出現(xiàn)在多個(gè)父表中，或者同時(shí)出現(xiàn)在父表和子表的定義里，那么這些字段就會(huì)被"融合"，這樣在子表里面就只有一個(gè)這樣的字段。要想融合，字段必須是相同的數(shù)據(jù)類型，否則就會(huì)拋出一個(gè)錯(cuò)誤。融合的字段將會(huì)擁有它所繼承的字段的所有約束。

復(fù)制代碼代碼如下:

    CREATE TABLE parent1 (FirstCol integer);

    CREATE TABLE parent2 (FirstCol integer, SecondCol varchar(20));

    CREATE TABLE parent3 (FirstCol varchar(200)); 

    --子表child1將同時(shí)繼承自parent1和parent2表，而這兩個(gè)父表中均包含integer類型的FirstCol字段，因此child1可以創(chuàng)建成功。

    CREATE TABLE child1 (MyCol timestamp) INHERITS (parent1,parent2);

    --子表child2將不會(huì)創(chuàng)建成功，因?yàn)槠鋬蓚€(gè)父表中均包含F(xiàn)irstCol字段，但是它們的類型不相同。

    CREATE TABLE child2 (MyCol timestamp) INHERITS (parent1,parent3);

    --子表child3同樣不會(huì)創(chuàng)建成功，因?yàn)樗推涓副砭現(xiàn)irstCol字段，但是它們的類型不相同。

    CREATE TABLE child3 (FirstCol varchar(20)) INHERITS(parent1);

5. 繼承和權(quán)限：

    表訪問(wèn)權(quán)限并不會(huì)自動(dòng)繼承。因此，一個(gè)試圖訪問(wèn)父表的用戶還必須具有訪問(wèn)它的所有子表的權(quán)限，或者使用ONLY關(guān)鍵字只從父表中提取數(shù)據(jù)。在向現(xiàn)有的繼承層次添加新的子表的時(shí)候，請(qǐng)注意給它賦予所有權(quán)限。
    繼承特性的一個(gè)嚴(yán)重的局限性是索引(包括唯一約束)和外鍵約束只施用于單個(gè)表，而不包括它們的繼承的子表。這一點(diǎn)不管對(duì)引用表還是被引用表都是事實(shí)，因此在上面的例子里，如果我們聲明cities.name為UNIQUE或者是一個(gè)PRIMARY KEY，那么也不會(huì)阻止capitals表?yè)碛兄貜?fù)了名字的cities數(shù)據(jù)行。并且這些重復(fù)的行缺省時(shí)在查詢cities表的時(shí)候會(huì)顯示出來(lái)。實(shí)際上，缺省時(shí)capitals將完全沒有唯一約束，因此可能包含帶有同名的多個(gè)行。你應(yīng)該給capitals增加唯一約束，但是這樣做也不會(huì)避免與cities的重復(fù)。類似，如果我們聲明cities.name REFERENCES某些其它的表，這個(gè)約束不會(huì)自動(dòng)廣播到capitals。在這種條件下，你可以通過(guò)手工給capitals 增加同樣的REFERENCES約束來(lái)做到這點(diǎn)。

二、分區(qū)表：

    1. 概述分區(qū)表：
    分區(qū)的意思是把邏輯上的一個(gè)大表分割成物理上的幾塊兒，分區(qū)可以提供若干好處：
    1). 某些類型的查詢性能可以得到極大提升。
    2). 更新的性能也可以得到提升，因?yàn)楸淼拿繅K的索引要比在整個(gè)數(shù)據(jù)集上的索引要小。如果索引不能全部放在內(nèi)存里，那么在索引上的讀和寫都會(huì)產(chǎn)生更多的磁盤訪問(wèn)。
    3). 批量刪除可以用簡(jiǎn)單地刪除某個(gè)分區(qū)來(lái)實(shí)現(xiàn)。
    4). 將很少用的數(shù)據(jù)可以移動(dòng)到便宜的、慢一些地存儲(chǔ)介質(zhì)上。
    假設(shè)當(dāng)前的數(shù)據(jù)庫(kù)并不支持分區(qū)表，而我們的應(yīng)用所需處理的數(shù)據(jù)量也非常大，對(duì)于這種應(yīng)用場(chǎng)景，我們不得不人為的將該大表按照一定的規(guī)則，手工拆分成多個(gè)小表，讓每個(gè)小表包含不同區(qū)間的數(shù)據(jù)。這樣一來(lái)，我們就必須在數(shù)據(jù)插入、更新、刪除和查詢之前，先計(jì)算本次的指令需要操作的小表。對(duì)于有些查詢而言，由于查詢區(qū)間可能會(huì)跨越多個(gè)小表，這樣我們又不得不將多個(gè)小表的查詢結(jié)果進(jìn)行union操作，以合并來(lái)自多個(gè)表的數(shù)據(jù)，并最終形成一個(gè)結(jié)果集返回給客戶端?？梢姡绻覀冋谑褂玫臄?shù)據(jù)庫(kù)不支持分區(qū)表，那么在適合其應(yīng)用的場(chǎng)景下，我們就需要做很多額外的編程工作以彌補(bǔ)這一缺失。然而需要說(shuō)明的是，盡管功能可以勉強(qiáng)應(yīng)付，但是性能卻和分區(qū)表無(wú)法相提并論。
    目前PostgreSQL支持的分區(qū)形式主要為以下兩種：
    1). 范圍分區(qū): 表被一個(gè)或者多個(gè)鍵字字段分區(qū)成"范圍"，在這些范圍之間沒有重疊的數(shù)值分布到不同的分區(qū)里。比如，我們可以為特定的商業(yè)對(duì)象根據(jù)數(shù)據(jù)范圍分區(qū)，或者根據(jù)標(biāo)識(shí)符范圍分區(qū)。
    2). 列表分區(qū): 表是通過(guò)明確地列出每個(gè)分區(qū)里應(yīng)該出現(xiàn)那些鍵字值實(shí)現(xiàn)的。

2. 實(shí)現(xiàn)分區(qū)：
1). 創(chuàng)建"主表"，所有分區(qū)都從它繼承。

復(fù)制代碼代碼如下:

    CREATE TABLE measurement (            --主表

        city_id      int    NOT NULL,

        logdate     date  NOT NULL,

        peaktemp int,

    );

2). 創(chuàng)建幾個(gè)"子"表，每個(gè)都從主表上繼承。通常，這些"子"表將不會(huì)再增加任何字段。我們將把子表稱作分區(qū)，盡管它們就是普通的PostgreSQL表。

復(fù)制代碼代碼如下:

    CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);

    CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);

    ...

    CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);

    CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);

    CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);

上面創(chuàng)建的子表，均已年、月的形式進(jìn)行范圍劃分，不同年月的數(shù)據(jù)將歸屬到不同的子表內(nèi)。這樣的實(shí)現(xiàn)方式對(duì)于清空分區(qū)數(shù)據(jù)而言將極為方便和高效，即直接執(zhí)行DROP TABLE語(yǔ)句刪除相應(yīng)的子表，之后在根據(jù)實(shí)際的應(yīng)用考慮是否重建該子表(分區(qū))。相比于直接DROP子表，PostgreSQL還提供了另外一種更為方便的方式來(lái)管理子表：

復(fù)制代碼代碼如下:

ALTER TABLE measurement_yy06mm01 NO INHERIT measurement;

和直接DROP相比，該方式僅僅是使子表脫離了原有的主表，而存儲(chǔ)在子表中的數(shù)據(jù)仍然可以得到訪問(wèn)，因?yàn)榇藭r(shí)該表已經(jīng)被還原成一個(gè)普通的數(shù)據(jù)表了。這樣對(duì)于數(shù)據(jù)庫(kù)的DBA來(lái)說(shuō)，就可以在此時(shí)對(duì)該表進(jìn)行必要的維護(hù)操作，如數(shù)據(jù)清理、歸檔等，在完成諸多例行性的操作之后，就可以考慮是直接刪除該表(DROP TABLE)，還是先清空該表的數(shù)據(jù)(TRUNCATE TABLE)，之后再讓該表重新繼承主表，如：

復(fù)制代碼代碼如下:

ALTER TABLE measurement_yy06mm01 INHERIT measurement;

3). 給分區(qū)表增加約束，定義每個(gè)分區(qū)允許的健值。同時(shí)需要注意的是，定義的約束要確保在不同的分區(qū)里不會(huì)有相同的鍵值。因此，我們需要將上面"子"表的定義修改為以下形式：

復(fù)制代碼代碼如下:

    CREATE TABLE measurement_yy04mm02 (

        CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')

    ) INHERITS (measurement);

    CREATE TABLE measurement_yy04mm03 (

        CHECK (logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01')

    ) INHERITS (measurement);

    ...

    CREATE TABLE measurement_yy05mm11 (

        CHECK (logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01')

    ) INHERITS (measurement);

    CREATE TABLE measurement_yy05mm12 (

        CHECK (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')

    ) INHERITS (measurement);

    CREATE TABLE measurement_yy06mm01 (

        CHECK (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')

    ) INHERITS (measurement);

4). 盡可能基于鍵值創(chuàng)建索引。如果需要，我們也同樣可以為子表中的其它字段創(chuàng)建索引。

復(fù)制代碼代碼如下:

    CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);

    CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);

    ...

    CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);

    CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);

    CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);

5). 定義一個(gè)規(guī)則或者觸發(fā)器，把對(duì)主表的修改重定向到適當(dāng)?shù)姆謪^(qū)表。
如果數(shù)據(jù)只進(jìn)入最新的分區(qū)，我們可以設(shè)置一個(gè)非常簡(jiǎn)單的規(guī)則來(lái)插入數(shù)據(jù)。我們必須每個(gè)月都重新定義這個(gè)規(guī)則，即修改重定向插入的子表名，這樣它總是指向當(dāng)前分區(qū)。

復(fù)制代碼代碼如下:

    CREATE OR REPLACE RULE measurement_current_partition AS

    ON INSERT TO measurement

    DO INSTEAD

    INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

其中NEW是關(guān)鍵字，表示新數(shù)據(jù)字段的集合。這里可以通過(guò)點(diǎn)(.)操作符來(lái)獲取集合中的每一個(gè)字段。
我們可能想插入數(shù)據(jù)并且想讓服務(wù)器自動(dòng)定位應(yīng)該向哪個(gè)分區(qū)插入數(shù)據(jù)。我們可以用像下面這樣的更復(fù)雜的規(guī)則集來(lái)實(shí)現(xiàn)這個(gè)目標(biāo)。

復(fù)制代碼代碼如下:

    CREATE RULE measurement_insert_yy04mm02 AS

    ON INSERT TO measurement WHERE (logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')

    DO INSTEAD

    INSERT INTO measurement_yy04mm02 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    ...

    CREATE RULE measurement_insert_yy05mm12 AS

    ON INSERT TO measurement WHERE (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')

    DO INSTEAD

    INSERT INTO measurement_yy05mm12 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    CREATE RULE measurement_insert_yy06mm01 AS

    ON INSERT TO measurement WHERE (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')

    DO INSTEAD

    INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);

    請(qǐng)注意每個(gè)規(guī)則里面的WHERE子句正好匹配其分區(qū)的CHECK約束。
    可以看出，一個(gè)復(fù)雜的分區(qū)方案可能要求相當(dāng)多的DDL。在上面的例子里我們需要每個(gè)月創(chuàng)建一次新分區(qū)，因此寫一個(gè)腳本自動(dòng)生成需要的DDL是明智的。除此之外，我們還不難推斷出，分區(qū)表對(duì)于新數(shù)據(jù)的批量插入操作有一定的抑制，這一點(diǎn)在Oracle中也同樣如此。
    除了上面介紹的通過(guò)Rule的方式重定向主表的數(shù)據(jù)到各個(gè)子表，我們還可以通過(guò)觸發(fā)器的方式來(lái)完成此操作，相比于基于Rule的重定向方法，基于觸發(fā)器的方式可能會(huì)帶來(lái)更好的插入效率，特別是針對(duì)非批量插入的情況。然而對(duì)于批量插入而言，由于Rule的額外開銷是基于表的，而不是基于行的，因此效果會(huì)好于觸發(fā)器方式。另一個(gè)需要注意的是，copy操作將會(huì)忽略Rules，如果我們想要通過(guò)COPY方法來(lái)插入數(shù)據(jù)，你只能將數(shù)據(jù)直接copy到正確的子表，而不是主表。這種限制對(duì)于觸發(fā)器來(lái)說(shuō)是不會(huì)造成任何問(wèn)題的?；赗ule的重定向方式還存在另外一個(gè)問(wèn)題，就是當(dāng)插入的數(shù)據(jù)不在任何子表的約束中時(shí)，PostgreSQL也不會(huì)報(bào)錯(cuò)，而是將數(shù)據(jù)直接保留在主表中。

6). 添加新分區(qū)：

這里將介紹兩種添加新分區(qū)的方式，第一種方法簡(jiǎn)單且直觀，我們只是創(chuàng)建新的子表，同時(shí)為其定義新的檢查約束，如：

復(fù)制代碼代碼如下:

    CREATE TABLE measurement_y2008m02 (

        CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )

    ) INHERITS (measurement);

第二種方法的創(chuàng)建步驟相對(duì)繁瑣，但更為靈活和實(shí)用。見以下四步：

復(fù)制代碼代碼如下:

    /* 創(chuàng)建一個(gè)獨(dú)立的數(shù)據(jù)表(measurement_y2008m02)，該表在創(chuàng)建時(shí)以將來(lái)的主表(measurement)為模板，包含模板表的缺省值(DEFAULTS)和一致性約束(CONSTRAINTS)。*/

    CREATE TABLE measurement_y2008m02

        (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

    /* 為該表創(chuàng)建未來(lái)作為子表時(shí)需要使用的檢查約束。*/

    ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02

        CHECK (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01');

    /* 導(dǎo)入數(shù)據(jù)到該表。下面只是給出一種導(dǎo)入數(shù)據(jù)的方式作為例子。在導(dǎo)入數(shù)據(jù)之后，如有可能，還可以做進(jìn)一步的數(shù)據(jù)處理，如數(shù)據(jù)轉(zhuǎn)換、過(guò)濾等。*/

    \copy measurement_y2008m02 from 'measurement_y2008m02'

    /* 在適當(dāng)?shù)臅r(shí)候，或者說(shuō)在需要的時(shí)候，讓該表繼承主表。*/

    ALTER TABLE measurement_y2008m02 INHERIT measurement;

7). 確保postgresql.conf里的配置參數(shù)constraint_exclusion是打開的。沒有這個(gè)參數(shù)，查詢不會(huì)按照需要進(jìn)行優(yōu)化。這里我們需要做的是確保該選項(xiàng)在配置文件中沒有被注釋掉。

復(fù)制代碼代碼如下:

    /> pwd

    /opt/PostgreSQL/9.1/data

    /> cat postgresql.conf | grep "constraint_exclusion"

    constraint_exclusion = partition        # on, off, or partition

3. 分區(qū)和約束排除：
約束排除(Constraint exclusion)是一種查詢優(yōu)化技巧，它改進(jìn)了用上面方法定義的表分區(qū)的性能。比如：

復(fù)制代碼代碼如下:

    SET constraint_exclusion = on;

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

如果沒有約束排除，上面的查詢會(huì)掃描measurement表中的每一個(gè)分區(qū)。打開了約束排除之后，規(guī)劃器將檢查每個(gè)分區(qū)的約束然后再視圖證明該分區(qū)不需要被掃描，因?yàn)樗荒馨魏畏蟇HERE子句條件的數(shù)據(jù)行。如果規(guī)劃器可以證明這個(gè)，它就把該分區(qū)從查詢規(guī)劃里排除出去。
你可以使用EXPLAIN命令顯示一個(gè)規(guī)劃在constraint_exclusion打開和關(guān)閉情況下的不同。用上面方法設(shè)置的表的典型的缺省規(guī)劃是：

復(fù)制代碼代碼如下:

    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_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0)
                   Filter: (logdate >= '2006-01-01'::date)
             -> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0)
                   Filter: (logdate >= '2006-01-01'::date)
    ...
             -> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0)
                   Filter: (logdate >= '2006-01-01'::date)
             -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)
                   Filter: (logdate >= '2006-01-01'::date)

從上面的查詢計(jì)劃中可以看出，PostgreSQL掃描了所有分區(qū)。下面我們?cè)倏匆幌麓蜷_約束排除之后的查詢計(jì)劃：

復(fù)制代碼代碼如下:

    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_yy06mm01 measurement  (cost=0.00..30.38 rows=543 width=0)

                   Filter: (logdate >= '2006-01-01'::date)

    請(qǐng)注意，約束排除只由CHECK約束驅(qū)動(dòng)，而不會(huì)由索引驅(qū)動(dòng)。
    目前版本的PostgreSQL中該配置的缺省值是partition，該值是介于on和off之間的一種行為方式，即規(guī)劃器只會(huì)將約束排除應(yīng)用于基于分區(qū)表的查詢，而on設(shè)置則會(huì)為所有查詢都進(jìn)行約束排除，那么對(duì)于普通數(shù)據(jù)表而言，也將不得不承擔(dān)由該機(jī)制而產(chǎn)生的額外開銷。

    約束排除在使用時(shí)有以下幾點(diǎn)注意事項(xiàng)：
    1). 約束排除只是在查詢的WHERE子句包含約束的時(shí)候才生效。一個(gè)參數(shù)化的查詢不會(huì)被優(yōu)化，因?yàn)樵谶\(yùn)行時(shí)規(guī)劃器不知道該參數(shù)會(huì)選擇哪個(gè)分區(qū)。因此像CURRENT_DATE這樣的函數(shù)必須避免。把分區(qū)鍵值和另外一個(gè)表的字段連接起來(lái)也不會(huì)得到優(yōu)化。
    2). 在CHECK約束里面要避免跨數(shù)據(jù)類型的比較，因?yàn)槟壳耙?guī)劃器會(huì)無(wú)法證明這樣的條件為假。比如，下面的約束會(huì)在x是整數(shù)字段的時(shí)候可用，但是在x是一個(gè)bigint的時(shí)候不能用：
    CHECK (x = 1)
    對(duì)于bigint字段，我們必須使用類似下面這樣的約束：
    CHECK (x = 1::bigint)
    這個(gè)問(wèn)題并不僅僅局限于bigint數(shù)據(jù)類型，它可能會(huì)發(fā)生在任何約束的缺省數(shù)據(jù)類型與其比較的字段的數(shù)據(jù)類型不匹配的場(chǎng)合。在提交的查詢里的跨數(shù)據(jù)類型的比較通常是OK的，只是不能在CHECK條件里。
    3). 在主表上的UPDATE和DELETE命令并不執(zhí)行約束排除。
    4). 在規(guī)劃器進(jìn)行約束排除時(shí)，主表上的所有分區(qū)的所有約束都將會(huì)被檢查，因此，大量的分區(qū)會(huì)顯著增加查詢規(guī)劃的時(shí)間。
    5). 在執(zhí)行ANALYZE語(yǔ)句時(shí)，要為每一個(gè)分區(qū)都執(zhí)行該命令，而不是僅僅對(duì)主表執(zhí)行該命令。

您可能感興趣的文章: