forked from mtusi2026/mtusi2026lab2
27 lines
1.1 KiB
PL/PgSQL
27 lines
1.1 KiB
PL/PgSQL
-- Isolated database: actual application schema and a deliberately skewed history.
|
|
\set ON_ERROR_STOP on
|
|
\timing on
|
|
SELECT version();
|
|
INSERT INTO users(id,username,display_name,password_hash)
|
|
VALUES('11111111-1111-4111-8111-111111111111','research','Synthetic','not-a-login-hash');
|
|
INSERT INTO chats(id,title)
|
|
SELECT md5(n::text)::uuid,'Synthetic '||n FROM generate_series(1,3) n;
|
|
INSERT INTO chat_members(chat_id,user_id)
|
|
SELECT id,'11111111-1111-4111-8111-111111111111' FROM chats;
|
|
INSERT INTO messages(id,chat_id,sender_id,text,created_at)
|
|
SELECT md5(n::text)::uuid,
|
|
md5((CASE WHEN n<=90000 THEN 1 WHEN n<=99000 THEN 2 ELSE 3 END)::text)::uuid,
|
|
'11111111-1111-4111-8111-111111111111','synthetic-'||n,
|
|
'2026-01-01Z'::timestamptz+n*interval '1 microsecond'
|
|
FROM generate_series(1,100000) AS n;
|
|
DROP INDEX messages_history;
|
|
CREATE INDEX candidate ON messages(chat_id);
|
|
\echo 'VARIANT: chat_only'
|
|
ANALYZE messages;
|
|
\i /tmp/plans.sql
|
|
DROP INDEX candidate;
|
|
CREATE INDEX candidate ON messages(chat_id,created_at,id);
|
|
\echo 'VARIANT: history_keyset'
|
|
ANALYZE messages;
|
|
\i /tmp/plans.sql
|