forked from mtusi2026/mtusi2026lab2
37 lines
2.0 KiB
Python
37 lines
2.0 KiB
Python
#!/usr/bin/env python3
|
|
"""One index, 10,000 messages; grade 4 evidence, not a grade 5 comparison."""
|
|
import json
|
|
import uuid
|
|
from messenger.core import Config, HISTORY_SQL, Store
|
|
|
|
store = Store(Config.from_env())
|
|
try:
|
|
actor = store.create_user(dict(username='plan_' + uuid.uuid4().hex[:20],
|
|
display_name='Plan fixture', password='synthetic-plan-password'))['id']
|
|
chat = store.create_chat(actor, dict(title='EXPLAIN 10000', member_ids=[actor]))
|
|
with store.pool.connection() as conn:
|
|
conn.execute("INSERT INTO messages(id,chat_id,sender_id,text,created_at) "
|
|
"SELECT md5(%s || i::text)::uuid, %s, %s, 'fixture ' || i, "
|
|
"timestamptz '2026-01-01 UTC' + i * interval '1 microsecond' "
|
|
"FROM generate_series(1,10000) i", (chat['id'], chat['id'], actor))
|
|
conn.execute('ANALYZE messages')
|
|
key = conn.execute('SELECT created_at,id FROM messages WHERE chat_id=%s '
|
|
'ORDER BY created_at,id LIMIT 1 OFFSET 8999', (chat['id'],)).fetchone()
|
|
plan = conn.execute('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + HISTORY_SQL,
|
|
(chat['id'], key['created_at'], key['id'], 51)).fetchone()['QUERY PLAN']
|
|
count = conn.execute('SELECT count(*) AS n FROM messages WHERE chat_id=%s', (chat['id'],)).fetchone()['n']
|
|
# OFFSET is used once to choose a fixture position, never in the API query.
|
|
assert count == 10000
|
|
nodes = []
|
|
def walk(node):
|
|
nodes.append(node)
|
|
for child in node.get('Plans', []):
|
|
walk(child)
|
|
walk(plan[0]['Plan'])
|
|
assert any(n.get('Index Name') == 'messages_history' for n in nodes)
|
|
assert all(n['Node Type'] != 'Seq Scan' for n in nodes)
|
|
print(json.dumps(dict(messages=count, cursor_position=9000, page_limit=50, fetched_with_lookahead=51,
|
|
sql=HISTORY_SQL, explain=plan), ensure_ascii=False, indent=2))
|
|
finally:
|
|
store.close()
|