benchmark_toast_detoast.sql

-- Quick benchmark for external TOAST detoast paths
-- Tests compressed (PGLZ) and uncompressed external storage

\timing off

-- PART 1: Compressed external (PGLZ)
DROP TABLE IF EXISTS bench_compressed;
CREATE TABLE bench_compressed (id serial, data text COMPRESSION pglz);

\echo 'Inserting 200 rows of semi-compressible data...'
\timing on
INSERT INTO bench_compressed (data)
SELECT string_agg(md5(g::text) || '_xx_' || repeat('a', 80), '')
FROM generate_series(1, 200) row_id,
     LATERAL (SELECT g FROM generate_series(1, 400) g) sub
GROUP BY row_id;
\timing off

CHECKPOINT;

-- Verify it's actually external
SELECT count(*) as rows,
       pg_column_compression(data) as compression,
       count(pg_column_toast_chunk_id(data)) as external
FROM bench_compressed
GROUP BY pg_column_compression(data);

\echo 'Read benchmark (compressed external):'
\timing on
SELECT count(*), sum(length(data)) FROM bench_compressed;
SELECT count(*), sum(length(data)) FROM bench_compressed;
SELECT count(*), sum(length(data)) FROM bench_compressed;
SELECT count(*), sum(length(data)) FROM bench_compressed;
SELECT count(*), sum(length(data)) FROM bench_compressed;
\timing off

\echo 'Slice benchmark (compressed external):'
\timing on
SELECT count(*), sum(length(substring(data from 1 for 1000))) FROM bench_compressed;
SELECT count(*), sum(length(substring(data from 1 for 1000))) FROM bench_compressed;
SELECT count(*), sum(length(substring(data from 1 for 1000))) FROM bench_compressed;
\timing off

-- PART 2: Uncompressed external
DROP TABLE IF EXISTS bench_uncompressed;
CREATE TABLE bench_uncompressed (id serial, data text);
ALTER TABLE bench_uncompressed ALTER COLUMN data SET STORAGE EXTERNAL;

\echo 'Inserting 200 rows uncompressed...'
\timing on
INSERT INTO bench_uncompressed (data)
SELECT repeat(chr(65 + (g % 26)), 50 * 1024) FROM generate_series(1, 200) g;
\timing off

CHECKPOINT;

SELECT count(*) as rows,
       coalesce(pg_column_compression(data), 'none') as compression,
       count(pg_column_toast_chunk_id(data)) as external
FROM bench_uncompressed
GROUP BY pg_column_compression(data);

\echo 'Read benchmark (uncompressed external):'
\timing on
SELECT count(*), sum(length(data)) FROM bench_uncompressed;
SELECT count(*), sum(length(data)) FROM bench_uncompressed;
SELECT count(*), sum(length(data)) FROM bench_uncompressed;
SELECT count(*), sum(length(data)) FROM bench_uncompressed;
SELECT count(*), sum(length(data)) FROM bench_uncompressed;
\timing off

\echo 'Slice benchmark (uncompressed external):'
\timing on
SELECT count(*), sum(length(substring(data from 1 for 1000))) FROM bench_uncompressed;
SELECT count(*), sum(length(substring(data from 1 for 1000))) FROM bench_uncompressed;
SELECT count(*), sum(length(substring(data from 1 for 1000))) FROM bench_uncompressed;
\timing off

-- Cleanup
DROP TABLE bench_compressed;
DROP TABLE bench_uncompressed;

\echo 'Done.'