Files
mtusi2026lab2/scripts/benchmark_history.sql
2026-11-10 23:47:45 +03:00

40 lines
1.2 KiB
PL/PgSQL

BEGIN;
SELECT gen_random_uuid()::text AS bench_user_id,
gen_random_uuid()::text AS bench_chat_id,
'bench_' || substr(replace(gen_random_uuid()::text, '-', ''), 1, 24) AS bench_username
\gset
INSERT INTO users (id, username, display_name, password_hash)
VALUES (:'bench_user_id'::uuid, :'bench_username', 'Benchmark fixture', 'not-used');
INSERT INTO chats (id, title)
VALUES (:'bench_chat_id'::uuid, 'Rollback-only history benchmark');
INSERT INTO chat_members (chat_id, user_id)
VALUES (:'bench_chat_id'::uuid, :'bench_user_id'::uuid);
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(),
:'bench_chat_id'::uuid,
:'bench_user_id'::uuid,
'benchmark message ' || message_number,
clock_timestamp() + message_number * interval '1 microsecond'
FROM generate_series(1, 10000) AS message_number;
ANALYZE messages;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = :'bench_chat_id'::uuid
AND (created_at, id) > ('-infinity'::timestamptz, '00000000-0000-0000-0000-000000000000'::uuid)
ORDER BY created_at, id
LIMIT 51;
SELECT count(*) AS benchmark_messages
FROM messages
WHERE chat_id = :'bench_chat_id'::uuid;
ROLLBACK;