Last active
February 16, 2021 10:29
-
-
Save araqnid/1298358 to your computer and use it in GitHub Desktop.
example script demonstrating heap-only tuples on postgresql; you need the pageinspect extension installed
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create temp table t1(t1_id serial primary key, reference varchar(16) not null unique, value varchar(16) not null); | |
copy t1(reference, value) from stdin; | |
FOO foo | |
BAR bar | |
QUUX quux | |
\. | |
create temp view t1_combined as | |
select t1_id, reference, value, ctid, lp_flags, lp_off, case when t_ctid <> ctid then t_ctid end as t_ctid, | |
t_xmin, xmin_visible, case when t_xmax::text <> '0' then t_xmax end as t_xmax, xmax_visible, | |
xmin_visible and (xmax_visible is null or not xmax_visible or t_locked <> '') as visible, t_hot_updated, t_heap_only | |
from (select *, | |
t_xmin_valid and txid_visible_in_snapshot(t_xmin::text::bigint, txid_current_snapshot()) as xmin_visible, | |
t_xmax_valid and txid_visible_in_snapshot(t_xmax::text::bigint, txid_current_snapshot()) as xmax_visible | |
from (select ('(' || 0 || ',' || lp || ')')::tid as ctid, | |
lp, lp_off, case lp_flags when 0 then 'UNUSED' when 1 then 'NORMAL' when 2 then 'REDIRECT' when 3 then 'DEAD' end as lp_flags, | |
lp_len, t_xmin, t_xmax, t_field3, t_ctid, (t_infomask&1)<>0 as t_hasnull, (t_infomask&2)<>0 as t_hasvarwidth, | |
(t_infomask&4)<>0 as t_hasexternal, (t_infomask&8)<>0 as t_hasoid, (t_infomask&32)<>0 as t_combocid, | |
case t_infomask & 192 when 64 then 'EXCL' when 128 then 'SHARE' when 0 then '' when 192 then 'INVALID' end as t_locked, | |
(t_infomask&256)<>0 as t_xmin_committed, (t_infomask&512)=0 as t_xmin_valid, | |
(t_infomask&1024)<>0 as t_xmax_committed, (t_infomask&2048)=0 as t_xmax_valid, | |
(t_infomask&4096)<>0 as t_xmax_is_multi, (t_infomask&8192)<>0 as t_updated, | |
(t_infomask&16384)<>0 as t_moved_off, (t_infomask&32768)<>0 as t_moved_in, | |
t_infomask2&2047 as t_natts, (t_infomask2&16384)<>0 as t_hot_updated, | |
(t_infomask2&32768)<>0 as t_heap_only, | |
t_hoff, t_bits, t_oid | |
from heap_page_items(get_raw_page('t1', 0))) format_heap_page_items | |
) heap | |
full outer join (select ctid, * from t1) t1 using (ctid); | |
create temp view t1_indices as | |
select ctid, pkey_content.itemoffset as pkey_itemoffset, pkey_content.data as pkey_data, auxkey_content.itemoffset as auxkey_itemoffset, auxkey_content.data as auxkey_data | |
from bt_page_items('t1_pkey', 1) pkey_content | |
full outer join bt_page_items('t1_reference_key', 1) auxkey_content using (ctid); | |
\echo ******************************************************************************** | |
\echo * Initial table | |
\echo | |
select * from t1_combined; | |
select * from t1_indices; | |
\echo ******************************************************************************** | |
\echo * Update non-indexed column | |
\echo * - index entries untouched | |
\echo * - old tuple at ctid (0,1) has t_hot_updated set | |
\echo * - new tuple at ctid (0,4) has t_heap_only set | |
\echo * - t_ctid of (0,1) points to (0,4) | |
\echo | |
begin; | |
update t1 set value = 'mumble' where t1_id = 1; | |
end; | |
select * from t1_combined; | |
select * from t1_indices; | |
\echo ******************************************************************************** | |
\echo * Update non-indexed column again | |
\echo * - tuple at ctid (0,4) now just points to ctid (0,5) and is redundant | |
\echo | |
begin; | |
update t1 set value = 'womble' where t1_id = 1; | |
end; | |
select * from t1_combined; | |
select * from t1_indices; | |
\echo ******************************************************************************** | |
\echo * Vacuum table | |
\echo * - line pointer ctid (0,1) converted to REDIRECT since index entries still point to it | |
\echo * - redundant tuple at ctid (0,4) reclaimed for reuse | |
\echo | |
vacuum t1; | |
select * from t1_combined; | |
select * from t1_indices; | |
\echo ******************************************************************************** | |
\echo * Update indexed column | |
\echo * - New index entries written for new tuple at ctid (0,4) which is now reused | |
\echo | |
update t1 set reference = 'WOMBLE' where t1_id = 1; | |
select * from t1_combined; | |
select * from t1_indices; | |
\echo ******************************************************************************** | |
\echo * Update indexed column to contain same value | |
\echo * - even though indexed column is mentioned in update, this makes a heap-only change | |
\echo * - current version is now (0,6) but indices still indicate (0,4) | |
\echo | |
update t1 set reference = 'WOMBLE', value = 'womble2' where t1_id = 1; | |
select * from t1_combined; | |
select * from t1_indices; | |
\echo ******************************************************************************** | |
\echo * Vacuum table | |
\echo * - ctid (0,1) now reclaimed, index entries pointing to it removed | |
\echo * - ctid (0,5) reclaimed too, it never had index entries pointing to it | |
\echo | |
vacuum t1; | |
select * from t1_combined; | |
select * from t1_indices; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
psql:pg_hot_example.sql:1: NOTICE: CREATE TABLE will create implicit sequence "t1_t1_id_seq" for serial column "t1.t1_id" | |
psql:pg_hot_example.sql:1: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" | |
psql:pg_hot_example.sql:1: NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_reference_key" for table "t1" | |
CREATE TABLE | |
CREATE VIEW | |
CREATE VIEW | |
******************************************************************************** | |
* Initial table | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼───────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
1 │ FOO │ foo │ (0,1) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8072 │ │ 30268 │ t │ │ f │ t │ f │ f | |
(3 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00 | |
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00 | |
(3 rows) | |
******************************************************************************** | |
* Update non-indexed column | |
* - index entries untouched | |
* - old tuple at ctid (0,1) has t_hot_updated set | |
* - new tuple at ctid (0,4) has t_heap_only set | |
* - t_ctid of (0,1) points to (0,4) | |
BEGIN | |
UPDATE 1 | |
COMMIT | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
│ │ │ (0,1) │ NORMAL │ 8152 │ (0,4) │ 30268 │ t │ 30271 │ t │ f │ t │ f | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8072 │ │ 30268 │ t │ │ f │ t │ f │ f | |
1 │ FOO │ mumble │ (0,4) │ NORMAL │ 8032 │ │ 30271 │ t │ │ f │ t │ f │ t | |
(4 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00 | |
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00 | |
(3 rows) | |
******************************************************************************** | |
* Update non-indexed column again | |
* - tuple at ctid (0,4) now just points to ctid (0,5) and is redundant | |
BEGIN | |
UPDATE 1 | |
COMMIT | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
│ │ │ (0,1) │ NORMAL │ 8152 │ (0,4) │ 30268 │ t │ 30271 │ t │ f │ t │ f | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8072 │ │ 30268 │ t │ │ f │ t │ f │ f | |
│ │ │ (0,4) │ NORMAL │ 8032 │ (0,5) │ 30271 │ t │ 30272 │ t │ f │ t │ t | |
1 │ FOO │ womble │ (0,5) │ NORMAL │ 7992 │ │ 30272 │ t │ │ f │ t │ f │ t | |
(5 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00 | |
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00 | |
(3 rows) | |
******************************************************************************** | |
* Vacuum table | |
* - line pointer ctid (0,1) converted to REDIRECT since index entries still point to it | |
* - redundant tuple at ctid (0,4) reclaimed for reuse | |
VACUUM | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
│ │ │ (0,1) │ REDIRECT │ 5 │ │ │ │ │ │ │ │ | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
│ │ │ (0,4) │ UNUSED │ 0 │ │ │ │ │ │ │ │ | |
1 │ FOO │ womble │ (0,5) │ NORMAL │ 8072 │ │ 30272 │ t │ │ f │ t │ f │ t | |
(5 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,1) │ 1 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00 | |
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00 | |
(3 rows) | |
******************************************************************************** | |
* Update indexed column | |
* - New index entries written for new tuple at ctid (0,4) which is now reused | |
UPDATE 1 | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
│ │ │ (0,1) │ REDIRECT │ 5 │ │ │ │ │ │ │ │ | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
1 │ WOMBLE │ womble │ (0,4) │ NORMAL │ 8024 │ │ 30273 │ t │ │ f │ t │ f │ f | |
│ │ │ (0,5) │ NORMAL │ 8072 │ (0,4) │ 30272 │ t │ 30273 │ t │ f │ f │ t | |
(5 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,1) │ 2 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00 | |
(0,2) │ 3 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 4 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00 | |
(0,4) │ 1 │ 01 00 00 00 00 00 00 00 │ 4 │ 0f 57 4f 4d 42 4c 45 00 | |
(4 rows) | |
******************************************************************************** | |
* Update indexed column to contain same value | |
* - even though indexed column is mentioned in update, this makes a heap-only change | |
* - current version is now (0,6) but indices still indicate (0,4) | |
UPDATE 1 | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼─────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
│ │ │ (0,1) │ REDIRECT │ 5 │ │ │ │ │ │ │ │ | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
│ │ │ (0,4) │ NORMAL │ 8024 │ (0,6) │ 30273 │ t │ 30274 │ t │ f │ t │ f | |
│ │ │ (0,5) │ NORMAL │ 8072 │ (0,4) │ 30272 │ t │ 30273 │ t │ f │ f │ t | |
1 │ WOMBLE │ womble2 │ (0,6) │ NORMAL │ 7976 │ │ 30274 │ t │ │ f │ t │ f │ t | |
(6 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,1) │ 2 │ 01 00 00 00 00 00 00 00 │ 2 │ 09 46 4f 4f 00 00 00 00 | |
(0,2) │ 3 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 4 │ 03 00 00 00 00 00 00 00 │ 3 │ 0b 51 55 55 58 00 00 00 | |
(0,4) │ 1 │ 01 00 00 00 00 00 00 00 │ 4 │ 0f 57 4f 4d 42 4c 45 00 | |
(4 rows) | |
******************************************************************************** | |
* Vacuum table | |
* - ctid (0,1) now reclaimed, index entries pointing to it removed | |
* - ctid (0,5) reclaimed too, it never had index entries pointing to it | |
VACUUM | |
t1_id │ reference │ value │ ctid │ lp_flags │ lp_off │ t_ctid │ t_xmin │ xmin_visible │ t_xmax │ xmax_visible │ visible │ t_hot_updated │ t_heap_only | |
───────┼───────────┼─────────┼───────┼──────────┼────────┼────────┼────────┼──────────────┼────────┼──────────────┼─────────┼───────────────┼───────────── | |
│ │ │ (0,1) │ UNUSED │ 0 │ │ │ │ │ │ │ │ | |
2 │ BAR │ bar │ (0,2) │ NORMAL │ 8152 │ │ 30268 │ t │ │ f │ t │ f │ f | |
3 │ QUUX │ quux │ (0,3) │ NORMAL │ 8112 │ │ 30268 │ t │ │ f │ t │ f │ f | |
│ │ │ (0,4) │ REDIRECT │ 6 │ │ │ │ │ │ │ │ | |
│ │ │ (0,5) │ UNUSED │ 0 │ │ │ │ │ │ │ │ | |
1 │ WOMBLE │ womble2 │ (0,6) │ NORMAL │ 8064 │ │ 30274 │ t │ │ f │ t │ f │ t | |
(6 rows) | |
ctid │ pkey_itemoffset │ pkey_data │ auxkey_itemoffset │ auxkey_data | |
───────┼─────────────────┼─────────────────────────┼───────────────────┼───────────────────────── | |
(0,2) │ 2 │ 02 00 00 00 00 00 00 00 │ 1 │ 09 42 41 52 00 00 00 00 | |
(0,3) │ 3 │ 03 00 00 00 00 00 00 00 │ 2 │ 0b 51 55 55 58 00 00 00 | |
(0,4) │ 1 │ 01 00 00 00 00 00 00 00 │ 3 │ 0f 57 4f 4d 42 4c 45 00 | |
(3 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment