-- Table de démo
\pset pager off
\pset footer off

SET track_io_timing TO on;

-- some uncompressible noise
CREATE OR REPLACE FUNCTION random_string_pavel(int) RETURNS text AS $$ 
  SELECT array_to_string(
    ARRAY (SELECT substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' FROM (random() *36)::int FOR 1) FROM generate_series(1, $1) ), '' ) 
$$ LANGUAGE sql;

\echo "8 MB test table"
DROP TABLE IF EXISTS noises ;
CREATE UNLOGGED TABLE noises AS SELECT random_string_pavel (2500) AS noise FROM generate_series (1, 3000) j ;
VACUUM noises ;

\dt+ noises

SELECT 'noises'::regclass::oid::text AS hoid \gset
SELECT reltoastrelid AS toid FROM pg_class WHERE oid=:hoid \gset
SELECT indexrelid::text AS txoid FROM pg_index WHERE indrelid=:toid \gset

\echo "** Reset stats **"
SELECT pg_sleep(1);  -- stats are not updated instantly
SELECT pg_stat_reset_single_table_counters (:hoid),pg_stat_reset_single_table_counters (:toid),pg_stat_reset_single_table_counters (:txoid);
SELECT pg_sleep(1);  -- stats are not updated instantly

\echo "**"
\echo "** Query : SELECT noises  : ~ about 10-20 ms **"
\echo "**"
\timing on
\o /dev/null
SELECT noise FROM noises ;
\timing off
\o

SELECT pg_sleep(1);
SELECT * FROM pg_statio_all_tables WHERE relid IN (:hoid, :toid);
\echo "** Reset stats **"
SELECT pg_stat_reset_single_table_counters (:hoid),pg_stat_reset_single_table_counters (:toid),pg_stat_reset_single_table_counters (:txoid);
SELECT pg_sleep(1);

\echo "**"
\echo "** Same query + EXPLAIN ANALYZE : < 1 ms , 20 hits **"
\echo "**"

EXPLAIN (ANALYZE,BUFFERS,VERBOSE, COSTS OFF)
SELECT noise FROM noises ;

SELECT pg_sleep(1);
SELECT * FROM pg_statio_all_tables WHERE relid IN (:hoid, :toid);
\echo "** Reset stats **"
SELECT pg_stat_reset_single_table_counters (:hoid),pg_stat_reset_single_table_counters (:toid),pg_stat_reset_single_table_counters (:txoid);
SELECT pg_sleep(1);

\echo "**"
\echo "** Force use of toast data + EXPLAIN ANALYZE : 10-20 ms, 9020 hits **"
\echo "**"

EXPLAIN (ANALYZE,BUFFERS,VERBOSE,COSTS OFF)
SELECT noise||'' FROM noises ;

SELECT pg_sleep(1);
SELECT * FROM pg_statio_all_tables WHERE relid IN (:hoid, :toid);

DROP TABLE noises ;