TOAST table with pgfincore

背景

pgfincore 可以修改文件的posix_advise属性，输入参数是对象名。如表名，索引名。

注意有些PostgreSQL数据类型的存储如果选择了为extended或者EXTERNAL存储时。实际上这些内容是存储在TOAST表里面。

例如text 类型.

下面来看一个例子 :

digoal=> \d text  
     Table "digoal.text"  
 Column |  Type   | Modifiers   
--------+---------+-----------  
 id     | integer | not null  
 info   | text    |   
Indexes:  
    "text_pkey" PRIMARY KEY, btree (id), tablespace "digoal_idx"  

这个表有个toast表, 如下 :

digoal=> select relname from pg_class where oid=(select reltoastrelid from pg_class where relname='text');  
     relname        
------------------  
 pg_toast_1293339  

还有个toast索引 :

digoal=> select relname from pg_class where oid=(select reltoastidxid from pg_class where oid=(select reltoastrelid from pg_class where relname='text'));  
        relname           
------------------------  
 pg_toast_1293339_index  

下面我想把text表的posix_fadvise修改一下 :

digoal=> select * from pgfadvise_willneed('text');  
                    relpath                     | os_page_size | rel_os_pages | os_pages_free   
------------------------------------------------+--------------+--------------+---------------  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124614 |         4096 |        64922 |         30319  
digoal=> select * from pgfadvise_willneed('text_pkey');  
                    relpath                     | os_page_size | rel_os_pages | os_pages_free   
------------------------------------------------+--------------+--------------+---------------  
 pg_tblspc/16393/PG_9.1_201105231/16394/6124616 |         4096 |        27424 |         31007  

显然没有包含TOAST表的内容。

接下来看看如何包含TOAST表的内容 :

首先要看看TOAST表放在哪个schema下面 :

digoal=> select nspname from pg_namespace where oid=(select relnamespace from pg_class where oid=1293342);  
 nspname    
----------  
 pg_toast  

pg_toast的owner是postgres, 因此需要使用超级用户 :

digoal=> \dn *.*  
        List of schemas  
        Name        |  Owner     
--------------------+----------  
 digoal             | digoal  
 information_schema | postgres  
 pg_catalog         | postgres  
 pg_temp_1          | postgres  
 pg_toast           | postgres  
 pg_toast_temp_1    | postgres  
 public             | postgres  

接下来就可以把TOAST表和它的索引放入内存了 :

digoal=# select * from pgfadvise_willneed('pg_toast.pg_toast_1293339');  
                      relpath                      | os_page_size | rel_os_pages | os_pages_free   
---------------------------------------------------+--------------+--------------+---------------  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615    |         4096 |       262144 |         32027  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.1  |         4096 |       262144 |         30055  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.2  |         4096 |       262144 |         31436  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.3  |         4096 |       262144 |         30018  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.4  |         4096 |       262144 |         31654  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.5  |         4096 |       262144 |         29949  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.6  |         4096 |       262144 |         30010  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.7  |         4096 |       262144 |         31135  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.8  |         4096 |       262144 |         30281  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.9  |         4096 |       262144 |         31313  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.10 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.11 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.12 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.13 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.14 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.15 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.16 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.17 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.18 |         4096 |       262144 |         31253  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124615.19 |         4096 |        19264 |         31253  
(20 rows)  
digoal=# select * from pgfadvise_willneed('pg_toast.pg_toast_1293339_index');  
                    relpath                     | os_page_size | rel_os_pages | os_pages_free   
------------------------------------------------+--------------+--------------+---------------  
 pg_tblspc/16392/PG_9.1_201105231/16394/6124617 |         4096 |        54842 |         30332  
(1 row)  

digoal’s 大量PostgreSQL文章入口