forked from mtusi2026/mtusi2026lab2
40 lines
1.2 KiB
PL/PgSQL
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;
|