TOAST_table_test.sql

application/sql
Filename: TOAST_table_test.sql
Type: application/sql
Part: 0
-- TOAST patch Testing:
-- Function "toast_chunks_cnt_func" use to count the Number of chunks.
CREATE OR REPLACE FUNCTION toast_chunks_cnt_func(p1 IN text)
  RETURNS int AS $$
DECLARE 
 chunks_cnt int; 
 v_tbl text;
BEGIN
  SELECT reltoastrelid::regclass INTO v_tbl FROM pg_class WHERE RELNAME = p1;
  EXECUTE 'SELECT count(*) FROM ' || v_tbl::regclass INTO chunks_cnt;
  RETURN chunks_cnt;
END; $$ LANGUAGE PLPGSQL;

-- ------------------------------------------------------------------------
-- 1. single column is compressed:
-- ------------------------------------------------------------------------
CREATE TABLE toast_tab (c1 text);
\d+ toast_tab
-- ALTER table column c1 for storage as "MAIN" to make sure that the column value is COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE MAIN;
\d+ toast_tab
CHECKPOINT;
\timing
INSERT INTO toast_tab
( select repeat('a', 700000)
  from generate_series(1,40000) x);
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';

SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT  pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT  pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
-- ------------------------------------------------------------------------
-- 2. where multiple columns are compressed
-- ------------------------------------------------------------------------
CREATE TABLE toast_tab (c1 text, c2 character varying(150000));
\d+ toast_tab
-- ALTER table column c1, c2 for storage as "MAIN" to make sure that the column values are COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE MAIN;
ALTER TABLE toast_tab ALTER COLUMN c2 SET STORAGE MAIN;
\d+ toast_tab
CHECKPOINT;
\timing
INSERT INTO toast_tab
( select repeat('a', 100000), repeat('a', 100000)
  from generate_series(1,40000) x);
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10), SUBSTR(c2, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1), c2 = UPPER(c2);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10), SUBSTR(c2, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
-- ------------------------------------------------------------------------
-- 3. where a single column is pushed to the TOAST table but not compressed
-- ------------------------------------------------------------------------
CREATE TABLE toast_tab (c1 text);
\d+ toast_tab
-- ALTER table column c1 for storage as "EXTERNAL" to make sure that the column value is pushed to the TOAST table but not COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE EXTERNAL;
\d+ toast_tab
CHECKPOINT;
\timing
INSERT INTO toast_tab
( select repeat('a', 169000) 
  from generate_series(1,40000) x); 
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
-- ------------------------------------------------------------------------
-- 4. where multiple columns are pushed to the TOAST table but not compressed
-- ------------------------------------------------------------------------
CREATE TABLE toast_tab (c1 text, c2 character varying(150000));
\d+ toast_tab
-- ALTER table column c1, c2 for storage as "EXTERNAL" to make sure that the column valuesare pushed to the TOAST table but not COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE EXTERNAL;
ALTER TABLE toast_tab ALTER COLUMN c2 SET STORAGE EXTERNAL;
\d+ toast_tab
CHECKPOINT;
\timing
INSERT INTO toast_tab
( select repeat('a', 100000), repeat('a', 100000)
  from generate_series(1,40000) x);
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10), SUBSTR(c2, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1), c2 = UPPER(c2);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10), SUBSTR(c2, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
-- ------------------------------------------------------------------------
-- 5. where a single column is pushed to the TOAST table and also compressed
-- ------------------------------------------------------------------------
CREATE TABLE toast_tab (c1 text);
\d+ toast_tab
-- ALTER table column c1 for storage as "EXTENDED" to make sure that the column value is pushed to the TOAST table and also COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE EXTENDED;
\d+ toast_tab
CHECKPOINT;
\timing
INSERT INTO toast_tab
( select repeat('a', 200000)
  from generate_series(1,40000) x);
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
-- ------------------------------------------------------------------------
-- 6. where multiple columns are pushed to the TOAST table and also compressed
-- ------------------------------------------------------------------------
CREATE TABLE toast_tab (c1 text, c2 character varying(150000));
\d+ toast_tab
-- ALTER table column c1 for storage as "EXTENDED" to make sure that the column value is pushed to the TOAST table and also COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE EXTENDED;
ALTER TABLE toast_tab ALTER COLUMN c2 SET STORAGE EXTENDED;
\d+ toast_tab
CHECKPOINT;
\timing
INSERT INTO toast_tab
( select repeat('a', 100000), repeat('a', 100000)
  from generate_series(1,40000) x);
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10), SUBSTR(c2, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1), c2 = UPPER(c2);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10), SUBSTR(c2, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
DROP FUNCTION toast_chunks_cnt_func;