Files
2026-10-26 17:50:20 +03:00

129 lines
4.4 KiB
Python

#!/usr/bin/env python3
"""EXPLAIN (ANALYZE, BUFFERS) for keyset history. Not part of make test.
Seeds one chat until the default planner uses messages_history_idx, then
deletes that seed. The saved plan contains no tokens.
"""
import os
import subprocess
import sys
import uuid
from pathlib import Path
ROOT = Path(__file__).resolve().parents[1]
EVIDENCE = ROOT / 'evidence' / 'explain.txt'
SIZES = (10000, 50000, 200000)
def psql(sql, tuples_only=False):
command = [
'docker', 'compose', '--env-file', '.env', 'exec', '-T', 'postgres',
'psql', '-U', os.environ.get('POSTGRES_USER', 'messenger'),
'-d', os.environ.get('POSTGRES_DB', 'messenger'),
'-v', 'ON_ERROR_STOP=1',
]
if tuples_only:
command.append('-tA')
proc = subprocess.run(
command,
cwd=ROOT,
input=sql,
check=False,
capture_output=True,
text=True,
)
if proc.returncode != 0:
sys.stderr.write(proc.stderr)
raise SystemExit('psql failed')
return proc.stdout
def seed(user_id, chat_id, count):
psql(
"""
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT gen_random_uuid(),
'%(chat)s'::uuid,
'%(user)s'::uuid,
'm',
timestamptz '2024-01-01 00:00:00+00' + (g * interval '1 microsecond')
FROM generate_series(1, %(count)d) AS g;
ANALYZE messages;
""" % {'chat': chat_id, 'user': user_id, 'count': count}
)
def main():
user_id = str(uuid.uuid4())
chat_id = str(uuid.uuid4())
username = 'u_explain_' + uuid.uuid4().hex[:12]
psql(
"""
INSERT INTO users (id, username, display_name, password_hash, created_at)
VALUES ('%(user)s', '%(name)s', 'Explain', 'not-used-for-login', clock_timestamp());
INSERT INTO chats (id, title, created_at)
VALUES ('%(chat)s', 'explain-seed', clock_timestamp());
INSERT INTO chat_members (chat_id, user_id, position)
VALUES ('%(chat)s', '%(user)s', 0);
""" % {'user': user_id, 'chat': chat_id, 'name': username}
)
plan = ''
used = 0
try:
for count in SIZES:
seed(user_id, chat_id, count)
used += count
cursor = psql(
"""
SELECT to_char(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.US'), id::text
FROM messages
WHERE chat_id = '%s'::uuid
ORDER BY created_at, id
OFFSET 49
LIMIT 1;
""" % chat_id,
tuples_only=True,
).strip()
created_at, message_id = cursor.split('|')
plan = psql(
"""
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = '%(chat)s'::uuid
AND (created_at, id) > ('%(created)s+00'::timestamptz, '%(message)s'::uuid)
ORDER BY created_at, id
LIMIT 51;
""" % {'chat': chat_id, 'created': created_at, 'message': message_id}
)
if 'Index Scan using messages_history_idx' in plan or 'Index Only Scan using messages_history_idx' in plan:
break
sys.stderr.write('planner did not pick the history index at %d rows\n' % used)
else:
sys.stderr.write(plan)
raise SystemExit('history query still does not use messages_history_idx')
if 'Seq Scan' in plan.split('Index Scan', 1)[0] and 'messages_history_idx' not in plan.split('Index Scan', 1)[0]:
pass
EVIDENCE.parent.mkdir(exist_ok=True)
EVIDENCE.write_text(
'rows_in_chat: %d\nquery: keyset page after the 50th message, LIMIT 51\n\n%s' % (used, plan),
encoding='utf-8',
newline='\n',
)
print('PASS: history index used at %d messages; wrote %s' % (used, EVIDENCE.name))
finally:
psql(
"""
DELETE FROM messages WHERE chat_id = '%(chat)s';
DELETE FROM chat_members WHERE chat_id = '%(chat)s';
DELETE FROM chats WHERE id = '%(chat)s';
DELETE FROM users WHERE id = '%(user)s';
""" % {'chat': chat_id, 'user': user_id}
)
return 0
if __name__ == '__main__':
sys.exit(main())