Files

45 lines
1.6 KiB
SQL

INSERT INTO users (id, username, display_name, password_hash)
VALUES ('00000000-0000-4000-8000-000000000001', 'perf_user_1', 'Perf One', 'synthetic'),
('00000000-0000-4000-8000-000000000002', 'perf_user_2', 'Perf Two', 'synthetic')
ON CONFLICT (id) DO NOTHING;
INSERT INTO chats (id, title)
VALUES ('00000000-0000-4000-8000-0000000000aa', 'Perf chat')
ON CONFLICT (id) DO NOTHING;
INSERT INTO chat_members (chat_id, user_id)
VALUES ('00000000-0000-4000-8000-0000000000aa', '00000000-0000-4000-8000-000000000001')
ON CONFLICT DO NOTHING;
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(),
'00000000-0000-4000-8000-0000000000aa',
'00000000-0000-4000-8000-000000000001',
'perf message ' || g,
now() - (g || ' seconds')::interval
FROM generate_series(1, 10000) AS g;
ANALYZE messages;
\echo === message count ===
SELECT count(*) AS messages FROM messages
WHERE chat_id = '00000000-0000-4000-8000-0000000000aa';
\echo === EXPLAIN (ANALYZE, BUFFERS): keyset page in the middle of history ===
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = '00000000-0000-4000-8000-0000000000aa'
AND (created_at, id) > (now() - interval '5000 seconds',
'00000000-0000-4000-8000-000000000000'::uuid)
ORDER BY created_at ASC, id ASC
LIMIT 50;
\echo === EXPLAIN (ANALYZE, BUFFERS): first page (no cursor) ===
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = '00000000-0000-4000-8000-0000000000aa'
ORDER BY created_at ASC, id ASC
LIMIT 50;