* [PATCH 2/3] lei: avoid SQLite COUNT() for dedupe
2021-07-23 10:56 7% [PATCH 0/3] lei rm-watch, some error handling stuff Eric Wong
@ 2021-07-23 10:56 7% ` Eric Wong
0 siblings, 0 replies; 2+ results
From: Eric Wong @ 2021-07-23 10:56 UTC (permalink / raw)
To: meta
SQLite COUNT() is a slow operation that does a full table scan
with no conditions. There's no need for it, since lei dedupe
only needs to know if it's empty or not to decide between
new/ and cur/ for Maildir outputs.
---
lib/PublicInbox/LeiDedupe.pm | 5 ++---
lib/PublicInbox/LeiSavedSearch.pm | 8 ++++----
lib/PublicInbox/LeiToMail.pm | 4 ++--
lib/PublicInbox/LeiXSearch.pm | 2 +-
lib/PublicInbox/SharedKV.pm | 7 +++++++
5 files changed, 16 insertions(+), 10 deletions(-)
diff --git a/lib/PublicInbox/LeiDedupe.pm b/lib/PublicInbox/LeiDedupe.pm
index ed52e417..32f99cd0 100644
--- a/lib/PublicInbox/LeiDedupe.pm
+++ b/lib/PublicInbox/LeiDedupe.pm
@@ -127,10 +127,9 @@ sub pause_dedupe {
delete($skv->{dbh}) if $skv;
}
-sub dedupe_nr {
+sub has_entries {
my $skv = $_[0]->[0] or return undef;
- my @n = $skv->count;
- $n[0];
+ $skv->has_entries;
}
1;
diff --git a/lib/PublicInbox/LeiSavedSearch.pm b/lib/PublicInbox/LeiSavedSearch.pm
index 929380ed..cfbf68c3 100644
--- a/lib/PublicInbox/LeiSavedSearch.pm
+++ b/lib/PublicInbox/LeiSavedSearch.pm
@@ -315,11 +315,11 @@ E: rename($dir_old, $dir_new) error: $!
EOM
}
-# cf. LeiDedupe->dedupe_nr
-sub dedupe_nr {
+# cf. LeiDedupe->has_entries
+sub has_entries {
my $oidx = $_[0]->{oidx} // die 'BUG: no {oidx}';
- my @n = $oidx->{dbh}->selectrow_array('SELECT COUNT(*) FROM over');
- $n[0];
+ my @n = $oidx->{dbh}->selectrow_array('SELECT num FROM over LIMIT 1');
+ scalar(@n) ? 1 : undef;
}
no warnings 'once';
diff --git a/lib/PublicInbox/LeiToMail.pm b/lib/PublicInbox/LeiToMail.pm
index b9405c0c..d782d4c7 100644
--- a/lib/PublicInbox/LeiToMail.pm
+++ b/lib/PublicInbox/LeiToMail.pm
@@ -198,7 +198,7 @@ sub _mbox_write_cb ($$) {
my $dedupe = $lei->{dedupe};
$dedupe->prepare_dedupe;
my $lse = $lei->{lse}; # may be undef
- my $set_recent = $dedupe->dedupe_nr;
+ my $set_recent = $dedupe->has_entries;
sub { # for git_to_mail
my ($buf, $smsg, $eml) = @_;
$eml //= PublicInbox::Eml->new($buf);
@@ -293,7 +293,7 @@ sub _maildir_write_cb ($$) {
# Favor cur/ and only write to new/ when augmenting. This
# saves MUAs from having to do a mass rename when the initial
# search result set is huge.
- my $dir = $dedupe && $dedupe->dedupe_nr ? 'new/' : 'cur/';
+ my $dir = $dedupe && $dedupe->has_entries ? 'new/' : 'cur/';
sub { # for git_to_mail
my ($bref, $smsg, $eml) = @_;
$dst // return $lei->fail; # dst may be undef-ed in last run
diff --git a/lib/PublicInbox/LeiXSearch.pm b/lib/PublicInbox/LeiXSearch.pm
index cac7fb7d..3414e87d 100644
--- a/lib/PublicInbox/LeiXSearch.pm
+++ b/lib/PublicInbox/LeiXSearch.pm
@@ -504,7 +504,7 @@ sub do_query {
my $F_SETPIPE_SZ = $^O eq 'linux' ? 1031 : undef;
if ($l2m->{-wq_nr_workers} > 1 &&
$l2m->{base_type} =~ /\A(?:maildir|mbox)\z/) {
- # setup two barriers to coordinate dedupe_nr
+ # setup two barriers to coordinate ->has_entries
# between l2m workers
pipe(my ($a_r, $a_w)) or die "pipe: $!";
fcntl($a_r, $F_SETPIPE_SZ, 4096) if $F_SETPIPE_SZ;
diff --git a/lib/PublicInbox/SharedKV.pm b/lib/PublicInbox/SharedKV.pm
index 8347b195..3487e820 100644
--- a/lib/PublicInbox/SharedKV.pm
+++ b/lib/PublicInbox/SharedKV.pm
@@ -154,6 +154,13 @@ SELECT COUNT(k) FROM kv
$sth->fetchrow_array;
}
+# faster than ->count due to how SQLite works
+sub has_entries {
+ my ($self) = @_;
+ my @n = $self->{dbh}->selectrow_array('SELECT k FROM kv LIMIT 1');
+ scalar(@n) ? 1 : undef;
+}
+
sub dbh_release {
my ($self, $lock) = @_;
my $dbh = delete $self->{dbh} or return;
^ permalink raw reply related [relevance 7%]
* [PATCH 0/3] lei rm-watch, some error handling stuff
@ 2021-07-23 10:56 7% Eric Wong
2021-07-23 10:56 7% ` [PATCH 2/3] lei: avoid SQLite COUNT() for dedupe Eric Wong
0 siblings, 1 reply; 2+ results
From: Eric Wong @ 2021-07-23 10:56 UTC (permalink / raw)
To: meta
I'm considering making "tag-ro" the default watch state
and introducing the idea of implicit watches for all
"lei q" output directories.
Eric Wong (3):
t/lei*: check error messages on failures
lei: avoid SQLite COUNT() for dedupe
lei rm-watch: new command to support removing watches
MANIFEST | 1 +
lib/PublicInbox/LEI.pm | 4 +++-
lib/PublicInbox/LeiDedupe.pm | 5 ++---
lib/PublicInbox/LeiInput.pm | 8 ++++++++
lib/PublicInbox/LeiRmWatch.pm | 31 +++++++++++++++++++++++++++++++
lib/PublicInbox/LeiSavedSearch.pm | 8 ++++----
lib/PublicInbox/LeiToMail.pm | 4 ++--
lib/PublicInbox/LeiXSearch.pm | 2 +-
lib/PublicInbox/SharedKV.pm | 7 +++++++
t/lei-externals.t | 4 ++++
t/lei-import-http.t | 2 ++
t/lei-import-imap.t | 1 +
t/lei-mirror.t | 7 ++++++-
t/lei-p2q.t | 1 +
t/lei-q-kw.t | 1 +
t/lei-q-remote-import.t | 1 +
t/lei-q-save.t | 4 ++++
t/lei-tag.t | 8 +++++++-
t/lei-watch.t | 18 ++++++++++++++----
t/solver_git.t | 7 +++++++
20 files changed, 107 insertions(+), 17 deletions(-)
create mode 100644 lib/PublicInbox/LeiRmWatch.pm
^ permalink raw reply [relevance 7%]
Results 1-2 of 2 | reverse | options above
-- pct% links below jump to the message on this page, permalinks otherwise --
2021-07-23 10:56 7% [PATCH 0/3] lei rm-watch, some error handling stuff Eric Wong
2021-07-23 10:56 7% ` [PATCH 2/3] lei: avoid SQLite COUNT() for dedupe Eric Wong
Code repositories for project(s) associated with this public inbox
https://80x24.org/public-inbox.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).