https://bz.apache.org/SpamAssassin/show_bug.cgi?id=7990
Bug ID: 7990
Summary: SQLBasedAddrList missing index on txrep, result may be
extreme slow
Product: Spamassassin
Version: SVN Trunk (Latest Devel Version)
Hardware: PC
OS: Linux
Status: NEW
Severity: major
Priority: P2
Component: spamassassin
Assignee: dev@spamassassin.apache.org
Reporter: g...@grin.hu
Target Milestone: Undefined
(This one was using PostgreSQL, namely v11.)
One specific email with 100+ recipients was stuck in the system. It turned out
that Txrep have tried to remove the whitelisted addresses, one by one, from the
table by using:
my $sql = "DELETE FROM $self->{tablename} WHERE username = ? AND email = ?";
The table has only index on last_hit, so this requires a sequential scan. It is
a definite problem on a table with about 5 million entries: hundreds of
sequential scans for one mail caused about 6 minutes of runtime on that
particular and not very large server. Since it's a seq scan it happens again
and again since the DB has no way to tell that the entries aren't there in the
first place.
CREATE INDEX i_txrep_username_email ON txrep (username,email);
reduced the runtime a little bit, to about 3-5 seconds.
(Also I have the eerie feeling that I have already reported that in the last
decade, but search come up with nothing familiar.)
--
You are receiving this mail because:
You are the assignee for the bug.
