From 74b92712fa7a21fe504b9908edebcf11bb9dc170 Mon Sep 17 00:00:00 2001
From: "Eric Wong (Contractor, The Linux Foundation)" <e@80x24.org>
Date: Sat, 7 Apr 2018 03:41:54 +0000
Subject: msgmap: speed up minmax with separate queries

This significantly improves the performance of the NNTP GROUP
command with 2.7 million messages from over 250ms to 700us.
SQLite is weird about this, but at least there's a way to
optimize it.
---
 lib/PublicInbox/Msgmap.pm | 10 +++++++---
 t/perf-nntpd.t            | 13 +++++++++----
 2 files changed, 16 insertions(+), 7 deletions(-)

diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm
index f5f88431..feef8ba7 100644
--- a/lib/PublicInbox/Msgmap.pm
+++ b/lib/PublicInbox/Msgmap.pm
@@ -138,10 +138,14 @@ sub num_for {
 sub minmax {
 	my ($self) = @_;
 	my $dbh = $self->{dbh};
-	my $sth = $self->{num_minmax} ||=
-		$dbh->prepare('SELECT MIN(num),MAX(num) FROM msgmap');
+	# breaking MIN and MAX into separate queries speeds up from 250ms
+	# to around 700us with 2.7million messages.
+	my $sth = $dbh->prepare_cached('SELECT MIN(num) FROM msgmap', undef, 1);
 	$sth->execute;
-        $sth->fetchrow_array;
+	my $min = $sth->fetchrow_array;
+	$sth = $dbh->prepare_cached('SELECT MAX(num) FROM msgmap', undef, 1);
+	$sth->execute;
+	($min, $sth->fetchrow_array);
 }
 
 sub mid_prefixes {
diff --git a/t/perf-nntpd.t b/t/perf-nntpd.t
index 4987f981..e5021532 100644
--- a/t/perf-nntpd.t
+++ b/t/perf-nntpd.t
@@ -3,7 +3,7 @@
 use strict;
 use warnings;
 use Test::More;
-use Benchmark qw(:all);
+use Benchmark qw(:all :hireswallclock);
 use PublicInbox::Inbox;
 use File::Temp qw/tempdir/;
 use POSIX qw(dup2);
@@ -79,8 +79,13 @@ $s = IO::Socket::INET->new(%opts);
 $s->autoflush(1);
 my $buf = $s->getline;
 is($buf, "201 server ready - post via email\r\n", 'got greeting');
-ok($s->print("GROUP $group\r\n"), 'changed group');
-$buf = $s->getline;
+
+my $t = timeit(10, sub {
+	ok($s->print("GROUP $group\r\n"), 'changed group');
+	$buf = $s->getline;
+});
+diag 'GROUP took: ' . timestr($t);
+
 my ($tot, $min, $max) = ($buf =~ /\A211 (\d+) (\d+) (\d+) /);
 ok($tot && $min && $max, 'got GROUP response');
 my $nr = $max - $min;
@@ -100,7 +105,7 @@ sub read_until_dot ($) {
 	$n;
 }
 
-my $t = timeit(1, sub {
+$t = timeit(1, sub {
 	$s->print("XOVER $spec\r\n");
 	$n = read_until_dot($s);
 });
-- 
cgit v1.2.3-24-ge0c7