Something went wrong. Try again.
GET /xrpc/tech.waow.typeahead.searchActors typeahead.waow.tech
Something went wrong. Try again.
12345678910111213141516171819202122232425262728293031323334353637383940"""Offline SQLite plan/latency/storage experiment. Does not access production."""import json, sqlite3, statistics, tempfile, timefrom pathlib import Path
TOP = 'SELECT did,score,op FROM (SELECT did,score,op FROM prefix_overlay WHERE key=? AND op=1 ORDER BY score DESC,did ASC LIMIT 50)'SQL = TOP + ' UNION ALL SELECT did,score,op FROM prefix_overlay WHERE key=? AND did IN ('+','.join('?' for _ in range(300))+')'ARGS=['d:bs','d:bs']+[f'did:{i:08}' for i in range(300)]
def measure(c,sql,args): samples=[] for _ in range(7): start=time.perf_counter(); rows=c.execute(sql,args).fetchall(); samples.append((time.perf_counter()-start)*1000) return {'median_ms':round(statistics.median(samples),3),'rows':len(rows),'plan':[r[3] for r in c.execute('EXPLAIN QUERY PLAN '+sql,args)]}
def updates(c): start=time.perf_counter() with c: c.executemany('UPDATE prefix_overlay SET score=? WHERE key=? AND did=?',[(i%71,'d:bs',f'did:{i:08}') for i in range(2000)]) return round((time.perf_counter()-start)*1000,3)
with tempfile.TemporaryDirectory() as tmp: c=sqlite3.connect(str(Path(tmp)/'bench.db')) c.executescript('PRAGMA journal_mode=WAL; CREATE TABLE prefix_overlay(key TEXT,did TEXT,score REAL,op INTEGER,updated_at INTEGER,source INTEGER,PRIMARY KEY(key,did)) WITHOUT ROWID; CREATE INDEX updated ON prefix_overlay(updated_at);') with c: c.executemany('INSERT INTO prefix_overlay VALUES(?,?,?,?,1,0)',(('d:bs',f'did:{i:08}',float(i%997),int(i%11!=0)) for i in range(333000))) result={'sqlite':sqlite3.sqlite_version,'rows_in_fixture':333000} result['full']=measure(c,'SELECT did,score,op FROM prefix_overlay WHERE key=?',['d:bs']) result['bounded']=measure(c,SQL,ARGS) before=c.execute('PRAGMA page_count').fetchone()[0]*c.execute('PRAGMA page_size').fetchone()[0] result['update_2000_without_index_ms']=updates(c) with c: c.executemany('UPDATE prefix_overlay SET score=? WHERE key=? AND did=?',[(i%997,'d:bs',f'did:{i:08}') for i in range(2000)]) start=time.perf_counter();c.execute('CREATE INDEX ranked ON prefix_overlay(key,score DESC,did ASC) WHERE op=1');c.commit() result['index_build_ms']=round((time.perf_counter()-start)*1000,3) result['indexed']=measure(c,SQL,ARGS) result['update_2000_with_index_ms']=updates(c) after=c.execute('PRAGMA page_count').fetchone()[0]*c.execute('PRAGMA page_size').fetchone()[0] result['additional_index_bytes']=after-before print(json.dumps(result,indent=2))