user/dev discussion of public-inbox itself
 help / Atom feed
* [PATCH] Tweak over.sqlite3 queries for sqlite < 3.8
@ 2018-06-18 17:51 Konstantin Ryabitsev
  2018-06-19  0:03 ` Eric Wong
  0 siblings, 1 reply; 2+ messages in thread
From: Konstantin Ryabitsev @ 2018-06-18 17:51 UTC (permalink / raw)
  To: meta

[-- Attachment #1: Type: text/plain, Size: 2748 bytes --]

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>
---
 lib/PublicInbox/Over.pm | 8 ++++----
 1 file changed, 4 insertions(+), 4 deletions(-)

diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index 30f2603..b2f6883 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);
-- 
2.17.1


[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 228 bytes --]

^ permalink raw reply	[flat|nested] 2+ messages in thread

* Re: [PATCH] Tweak over.sqlite3 queries for sqlite < 3.8
  2018-06-18 17:51 [PATCH] Tweak over.sqlite3 queries for sqlite < 3.8 Konstantin Ryabitsev
@ 2018-06-19  0:03 ` Eric Wong
  0 siblings, 0 replies; 2+ messages in thread
From: Eric Wong @ 2018-06-19  0:03 UTC (permalink / raw)
  To: Konstantin Ryabitsev; +Cc: meta

Thanks, applied.

^ permalink raw reply	[flat|nested] 2+ messages in thread

end of thread, back to index

Thread overview: 2+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-06-18 17:51 [PATCH] Tweak over.sqlite3 queries for sqlite < 3.8 Konstantin Ryabitsev
2018-06-19  0:03 ` Eric Wong

user/dev discussion of public-inbox itself

Archives are clonable:
	git clone --mirror https://public-inbox.org/meta
	git clone --mirror http://czquwvybam4bgbro.onion/meta
	git clone --mirror http://hjrcffqmbrq6wope.onion/meta
	git clone --mirror http://ou63pmih66umazou.onion/meta

Newsgroups are available over NNTP:
	nntp://news.public-inbox.org/inbox.comp.mail.public-inbox.meta
	nntp://ou63pmih66umazou.onion/inbox.comp.mail.public-inbox.meta
	nntp://czquwvybam4bgbro.onion/inbox.comp.mail.public-inbox.meta
	nntp://hjrcffqmbrq6wope.onion/inbox.comp.mail.public-inbox.meta
	nntp://news.gmane.org/gmane.mail.public-inbox.general

 note: .onion URLs require Tor: https://www.torproject.org/
       or Tor2web: https://www.tor2web.org/

AGPL code for this site: git clone https://public-inbox.org/ public-inbox