Debian Codegraph @

ext/fts5/tool/fts5cost.tcl

raw
1 #
2 # 2026 March 20
3 #
4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
6 #
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
10 #
11 #--------------------------------------------------------------------------
12 #
13 # This script extracts the documentation for the API used by fts5 auxiliary
14 # functions from header file fts5.h. It outputs html text on stdout that
15 # is included in the documentation on the web.
16 #
17
18
19 sqlite3 db fts5cost.db
20
21 # Create an IPK table with 1,000,000 entries. Short records.
22 #
23 set res [list [catch { db eval {SELECT count(*) FROM t1} } msg] $msg]
24 if {$res!="0 1000000"} {
25 db eval {
26 PRAGMA mmap_size = 1000000000; -- 1GB
27 DROP TABLE IF EXISTS t1;
28 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
29 WITH s(i) AS (
30 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1_000_000
31 )
32 INSERT INTO t1 SELECT i, hex(randomblob(8)) FROM s;
33 }
34 }
35
36 # Create an FTS5 table with 1,000,000 entries. Each row contains a single
37 # column containing a document of 100 terms chosen pseudo-randomly from
38 # a vocabularly of 2000.
39 set res [list [catch { db eval {SELECT count(*) FROM f1} } msg] $msg]
40 if {$res!="0 1000000"} {
41 set nVocab 2000
42 set nTerm 100
43 db eval {
44 BEGIN;
45 DROP TABLE IF EXISTS vocab1;
46 CREATE TABLE vocab1(w);
47 }
48 for {set ii 0} {$ii<$nVocab} {incr ii} {
49 set word [format %06x [expr {int(abs(rand()) * 0xFFFFFF)}]]
50 db eval { INSERT INTO vocab1 VALUES($word) }
51 lappend lVocab $word
52 }
53 db func doc doc
54 proc doc {} {
55 for {set ii 0} {$ii<$::nTerm} {incr ii} {
56 lappend ret [lindex $::lVocab [expr int(abs(rand())*$::nVocab)]]
57 }
58 set ret
59 }
60 db eval {
61 DROP TABLE IF EXISTS f1;
62 CREATE VIRTUAL TABLE f1 USING fts5(x);
63 WITH s(i) AS (
64 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1_000_000
65 )
66 INSERT INTO f1(rowid, x) SELECT i, doc() FROM s;
67 COMMIT;
68 }
69 } else {
70 set lVocab [db eval { SELECT * FROM vocab1 }]
71 set nVocab [llength $lVocab]
72 }
73
74 proc rowid_query {n} {
75 set rowid 654
76 for {set ii 0} {$ii<$n} {incr ii} {
77 db eval { SELECT b FROM t1 WHERE a = $rowid }
78 set rowid [expr {($rowid + 7717) % 1000000}]
79 }
80 }
81
82 proc rowid_query_fts {n} {
83 set rowid 654
84 for {set ii 0} {$ii<$n} {incr ii} {
85 db eval { SELECT * FROM f1 WHERE rowid = $rowid }
86 set rowid [expr {($rowid + 7717) % 1000000}]
87 }
88 }
89
90 proc match_query_fts {n} {
91 set idx 654
92 for {set ii 0} {$ii<$n} {incr ii} {
93 set match [lrange $::lVocab $idx $idx+1]
94 db eval { SELECT * FROM f1($match) }
95 set idx [expr {($idx + 7717) % $::nVocab}]
96 }
97 }
98
99 proc prefix_query_fts {n} {
100 set idx 654
101 for {set ii 0} {$ii<$n} {incr ii} {
102 set match "[lindex $::lVocab $idx]*"
103 db eval { SELECT * FROM f1($match) }
104 set idx [expr {($idx + 7717) % $::nVocab}]
105 }
106 }
107
108 proc match_rowid_query_fts {n} {
109 set idx 654
110 for {set ii 0} {$ii<$n} {incr ii} {
111 set match "[lindex $::lVocab $idx]"
112 db eval { SELECT * FROM f1($match) WHERE rowid=500000 }
113 set idx [expr {($idx + 7717) % $::nVocab}]
114 }
115 }
116
117 proc prefix_rowid_query_fts {n} {
118 set idx 654
119 for {set ii 0} {$ii<$n} {incr ii} {
120 set match "[lindex $::lVocab $idx]*"
121 db eval { SELECT * FROM f1($match) WHERE rowid=500000 }
122 set idx [expr {($idx + 7717) % $::nVocab}]
123 }
124 }
125
126
127 proc mytime {cmd div} {
128 set tm [time $cmd]
129 expr {[lindex $tm 0] / $div}
130 }
131
132 #set us [mytime { match_rowid_query_fts 1000 } 1000]
133 #puts "1000 match/rowid queries on fts5 table: ${us} per query"
134
135 set us [mytime { prefix_rowid_query_fts 1000 } 1000]
136 puts "1000 prefix/rowid queries on fts5 table: ${us} per query"
137
138 set us [mytime { match_query_fts 10 } 10]
139 puts "10 match queries on fts5 table: ${us} per query"
140
141 set us [mytime { prefix_query_fts 10 } 10]
142 puts "10 prefix queries on fts5 table: ${us} per query"
143
144 set us [mytime { prefix_rowid_query_fts 1000 } 1000]
145 puts "1000 prefix/rowid queries on fts5 table: ${us} per query"
146
147 set us [mytime { rowid_query 10000 } 10000]
148 puts "10000 by-rowid queries on normal table: ${us} per query"
149
150 set us [mytime { rowid_query_fts 10000 } 10000]
151 puts "10000 by-rowid queries on fts5 table: ${us} per query"
152
153