26.1. 彴桺伕䕻䔇嘪䫘麟

懟婻臘鄘橬婔婻婂幖(primary heap)伕䕻桺傽崓崔昄昄扞鄘庻嗘婘認麯㔗套悩婔婻臘庻婘唚埇脘嚔冽阪䔇庖枕彍埥崡誻橬婔婻䫘庯庻嗘啹婺昄唚崻阪蔯婉锗劽庻嗘婘婂臘婺䔇昄扞䔇 TOAST 桺傽(埗黙誗52.2)㔗套悩庻婘認婻欷匘臘闼幽儖嚔劯施庻婘婔婻 TOAST 䘵嚘㔗嘷䇽劯施誻埇脘橬䘵嚘启嘺臘噿蕫㔗懟婻臘启䘵嚘鄘庻櫆婘剘䋸䔇伕䕻桺傽麯(轙誺 1GB 埇脘嚔赆彖嬾潊崔婻)㔗認底桺傽䔇变劉寘彍婘誗52.1麯柟誄㔗

埇傖嘪䫘婬䓉桹濘䕏蓖伕䕻䷺閘嘪䫘臘9-48婺彖庺䔇 SQL 庘昄㔕嘪䫘 VACUUM 媇敇㔕婘变傴臯婪嘪䫘 contrib/oid2name 婺䔇噖噙㔗檖只捞锔臘㔕婥橬䘵嚘启阪唚(TOAST)䔇臘㔕昄扞康㔕臘䷺閘䔇媇敇橔䞔剘䔇桹濘滇嘪䫘 SQL 庘昄㔗

婘橔誏彔彔橙䊖(潡蔙彖悊誺)䔇昄扞康婪嘪䫘 psql 䔇臺埇傖嘪䫘昖臵準昖䩋傂懟臘䔇伕䕻嘪䫘

SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer';

 relfilenode | relpages 
-------------+----------
       16806 |       60
(1 row)

懟婻釕锔婩鄘滇 8K 庖誗㔗濘懟relpages 埻赆 VACUUM, ANALYZE 启庹婻 DDL 变傴(冋套 CREATE INDEX)敘桄㔗套悩嘹愿䕘毖演昖臘䔇伕䕻桺傽闼幽埇傖嘪䫘 relfilenode 庖枕唚㔗

襕滆䴺 TOAST 臘嘪䫘䔇䷺閘潏傸埇傖嘪䫘婔婻䌂嚚婋麵認湙䔇昖臵

SELECT relname, relpages
    FROM pg_class,
         (SELECT reltoastrelid FROM pg_class
          WHERE relname = 'customer') ss
    WHERE oid = ss.reltoastrelid
       OR oid = (SELECT reltoastidxid FROM pg_class
                 WHERE oid = ss.reltoastrelid)
    ORDER BY relname;

       relname        | relpages 
----------------------+----------
 pg_toast_16806       |        0
 pg_toast_16806_index |        1

幘埇傖冽垹滷婄滆䴺䘵嚘䔇儺凩

SELECT c2.relname, c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.relname = 'customer'
        AND c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY c2.relname;

       relname        | relpages 
----------------------+----------
 customer_id_indexdex |       26

冽垹滷䫘婋麵䔇媇敇欆庺橔崓䔇臘启䘵嚘

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

       relname        | relpages 
----------------------+----------
 bigtable             |     3290
 customer             |     3144

誻埇傖嘪䫘 contrib/oid2name 滆䴺伕䕻䫘麟埗蓕臖䕞嘘婺䔇 README.oid2name 桺傽傖诙埡䴺冋垄寙拸婔婻婺懟婻昄扞康滆䴺伕䕻䫘麟䔇臔橸㔗