Jeroen T. Vermeulen has proposed merging
lp:~jtv/launchpad/bug-662552-defer-potmsgset-filter into lp:launchpad/devel.
Requested reviews:
Launchpad code reviewers (launchpad-reviewers): code
= Bug 662552: Query Plan Tweak =
This is another tweak for bug 662552: timeouts on POFile:+translate.
Our big time sink there is searching for global suggestions—available
translations and suggestions for other POTMsgSets with the same msgid as the
one we're looking at. As you'd expect, the query first looks for POTMsgSets
with the same msgid but excepting the POTMsgSet we're looking at (the "inner
query"). Then it looks for TranslationMessages for that POTMsgSet in the
language we're looking at (the "outer query"). This gets very slow for some
msgids.
In this branch I change the query to look first for any POTMsgSet with the same
msgid (including the one we're already looking at), and then when searching for
TranslationMessages, filter out ones for the same POTMsgSet we're already
looking at. So I moved that filter from the inner query to the outer query.
You'd think that that would be slower because it leaves the "outer" query with
some rows that the inner query could already recognize as unwanted, but it's
actually 2×—4× faster.
Here's what seems to happen: the inner query uses a bitmap heap scan on
POTMsgSet to look for ones with the right msgid, excluding the one we're
already looking at. I moved the latter check condition out of there, combining
it with the outer join condition that's already there.
What is a bitmap scan? It's a trick developed by UPC in Barcelona. Instead of
scanning through an index or table and gathering up all rows that match, it
first does a separate pass to find matching rows. It keeps track of those in a
long array of bits; for every matching row, the corresponding bit is set to 1.
Then a second pass (shown in the query plan as the parent of the first pass)
collects only the rows that have their bit in the array set.
Bitmap scans are an efficient way of dealing with intermediate selectivity: you
don't expect to match so many rows that a sequential scan is the best you can
do, but neither will there be so few that an index scan is optimal. Every
matching row needs to be looked up in the actual table even when all relevant
data is also in the index you're using (at least until index-only scans are
implemented, but so far this has proven harder than it seems because of tuple
visibility issues). There is generally no connection between index order and
row storage order, so it's much more efficient to list all matching rows first
and then read them all in storage order, than it is to seek randomly around the
disk for matching rows as they come in from the index.
The bitmap scan is still in the plan after my tweak, just with one condition
(msgid match) instead of two (msgid match and id filter). Why is it so much
faster? I'm vague on the details, but as far as I can make out the id filter
requires data that is not in the index, and so sabotages any opportunity to
elide or defer the reading of actual rows from the table.
Query plan before the tweak: https://pastebin.canonical.com/39118/
Query plan after the tweak: https://pastebin.canonical.com/39120/
You'll notice that the speedup is hard to explain from the subplan timings.
There may have been some cold-cache effects from running test queries on
different database instances. The 2×—4× speedup estimate is based on
warm-cache figures.
Jeroen
--
https://code.launchpad.net/~jtv/launchpad/bug-662552-defer-potmsgset-filter/+merge/39426
Your team Launchpad code reviewers is requested to review the proposed merge of
lp:~jtv/launchpad/bug-662552-defer-potmsgset-filter into lp:launchpad/devel.
=== modified file 'lib/lp/translations/model/potmsgset.py'
--- lib/lp/translations/model/potmsgset.py 2010-10-26 10:31:37 +0000
+++ lib/lp/translations/model/potmsgset.py 2010-10-27 09:28:46 +0000
@@ -360,6 +360,7 @@
else:
query = ["(NOT %s)" % in_use_clause]
query.append('TranslationMessage.language = %s' % sqlvalues(language))
+ query.append('TranslationMessage.potmsgset <> %s' % sqlvalues(self))
query.append('''
potmsgset IN (
@@ -370,10 +371,8 @@
JOIN SuggestivePOTemplate ON
TranslationTemplateItem.potemplate =
SuggestivePOTemplate.potemplate
- WHERE
- POTMsgSet.id <> %s AND
- msgid_singular = %s
- )''' % sqlvalues(self, self.msgid_singular))
+ WHERE msgid_singular = %s
+ )''' % sqlvalues(self.msgid_singular))
# Subquery to find the ids of TranslationMessages that are
# matching suggestions.
_______________________________________________
Mailing list: https://launchpad.net/~launchpad-reviewers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~launchpad-reviewers
More help : https://help.launchpad.net/ListHelp
