about summary refs log tree commit homepage
path: root/lib/PublicInbox/Over.pm
diff options
context:
space:
mode:
authorKonstantin Ryabitsev <konstantin@linuxfoundation.org>2018-06-18 13:51:02 -0400
committerEric Wong <e@80x24.org>2018-06-19 00:01:55 +0000
commitc33472811331a6134b796f1889d87f7decf4c831 (patch)
tree3f9df942aa270cffbe6f57720034412ee8cbf11b /lib/PublicInbox/Over.pm
parent5f91aae26b6b0e02c9fabcc5dcf9f4b3e9eedbfe (diff)
downloadpublic-inbox-c33472811331a6134b796f1889d87f7decf4c831.tar.gz
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 <konstantin@linuxfoundation.org>
Diffstat (limited to 'lib/PublicInbox/Over.pm')
-rw-r--r--lib/PublicInbox/Over.pm8
1 files changed, 4 insertions, 4 deletions
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);