forked from mtusi2026/mtusi2026lab2
18 lines
1.2 KiB
PL/PgSQL
18 lines
1.2 KiB
PL/PgSQL
-- All synthetic rows are rolled back. EXPLAIN ANALYZE really runs SELECT.
|
|
BEGIN;
|
|
INSERT INTO users(id,username,display_name,password_hash) VALUES('10000000-0000-0000-0000-000000000001','plan_'||substr(md5(random()::text),1,20),'Plan fixture','not-a-login-hash');
|
|
INSERT INTO chats(id,title) VALUES('20000000-0000-0000-0000-000000000001','EXPLAIN temporary fixture');
|
|
INSERT INTO chat_members VALUES('20000000-0000-0000-0000-000000000001','10000000-0000-0000-0000-000000000001');
|
|
INSERT INTO messages(id,chat_id,sender_id,text,created_at)
|
|
SELECT md5('lab2-plan-'||g)::uuid,'20000000-0000-0000-0000-000000000001','10000000-0000-0000-0000-000000000001','fixture '||g,
|
|
timestamptz '2026-01-01 00:00:00+00'+g*interval '1 millisecond'
|
|
FROM generate_series(1,10000) AS g;
|
|
ANALYZE messages;
|
|
SELECT 'fixture_rows='||count(*) FROM messages WHERE chat_id='20000000-0000-0000-0000-000000000001';
|
|
EXPLAIN (ANALYZE, BUFFERS)
|
|
SELECT id,chat_id,sender_id,text,created_at FROM messages
|
|
WHERE chat_id='20000000-0000-0000-0000-000000000001'
|
|
AND (created_at,id)>(timestamptz '2026-01-01 00:00:09+00','00000000-0000-0000-0000-000000000000'::uuid)
|
|
ORDER BY created_at,id LIMIT 51;
|
|
ROLLBACK;
|