From c33472811331a6134b796f1889d87f7decf4c831 Mon Sep 17 00:00:00 2001 From: Konstantin Ryabitsev Date: Mon, 18 Jun 2018 13:51:02 -0400 Subject: Tweak over.sqlite3 queries for sqlite < 3.8 The query planner in sqlite3 < 3.8 is not very clever, so when it sees num mentioned in the query filter, it decides not to use the fast idx_ts index and goes for the much slower autoindex. CentOS-7 still has sqlite-3.7, so loading the http landing page of a very large archive (LKML) was taking over 18 seconds, as oppposed to milliseconds on a system with sqlite-3.8 and above: $ time sqlite3 -line over.sqlite3 'SELECT ts,ds,ddd FROM over \ WHERE num > 0 ORDER BY ts DESC LIMIT 1000;' > /dev/null real 0m19.610s user 0m17.805s sys 0m1.805s $ sqlite3 -line over.sqlite3 'EXPLAIN QUERY PLAN SELECT ts,ds,ddd \ FROM over WHERE num > 0 ORDER BY ts DESC LIMIT 1000;' selectid = 0 order = 0 from = 0 detail = SEARCH TABLE over USING INDEX sqlite_autoindex_over_1 (num>?) (~250000 rows) However, if we slightly tweak the query per SQlite recommendations [1] by adding + to the num filter, we force it to use the correct index and see much faster performance: $ time sqlite3 -line over.sqlite3 'SELECT ts,ds,ddd FROM over \ WHERE +num > 0 ORDER BY ts DESC LIMIT 1000;' > /dev/null real 0m0.007s user 0m0.005s sys 0m0.002s $ sqlite3 -line over.sqlite3 'EXPLAIN QUERY PLAN SELECT ts,ds,ddd \ FROM over WHERE +num > 0 ORDER BY ts DESC LIMIT 1000;' selectid = 0 order = 0 from = 0 detail = SCAN TABLE over USING INDEX idx_ts (~1464303 rows) This appears to be the only place where this is needed in order to avoid running into this issue. As far as I can tell, this change has no impact on systems running newer sqlite3 (>= 3.8). .. [1] https://sqlite.org/optoverview.html#disqualifying_where_clause_terms_using_unary_ Signed-off-by: Konstantin Ryabitsev --- lib/PublicInbox/Over.pm | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'lib/PublicInbox') diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm index 30f2603f..b2f68835 100644 --- a/lib/PublicInbox/Over.pm +++ b/lib/PublicInbox/Over.pm @@ -120,18 +120,18 @@ sub recent { my ($s, @v); if (defined($before)) { if (defined($after)) { - $s = 'num > 0 AND ts >= ? AND ts <= ? ORDER BY ts DESC'; + $s = '+num > 0 AND ts >= ? AND ts <= ? ORDER BY ts DESC'; @v = ($after, $before); } else { - $s = 'num > 0 AND ts <= ? ORDER BY ts DESC'; + $s = '+num > 0 AND ts <= ? ORDER BY ts DESC'; @v = ($before); } } else { if (defined($after)) { - $s = 'num > 0 AND ts >= ? ORDER BY ts ASC'; + $s = '+num > 0 AND ts >= ? ORDER BY ts ASC'; @v = ($after); } else { - $s = 'num > 0 ORDER BY ts DESC'; + $s = '+num > 0 ORDER BY ts DESC'; } } my $msgs = do_get($self, <<"", $opts, @v); -- cgit v1.2.3-24-ge0c7