[00:04] wgrant: I'm not certain that the info query in _getDirectAndDuplicateSubscriptions is to blame. The slow query is using ORDER BY and DISTINCT [00:07] wgrant: It's pretty much the same query, though [00:20] wgrant: Yeah, the long query is from IBug.getSubscribersForPerson [00:21] And just took 4 minutes on DF [00:22] :D [00:22] So, explanalyze it [00:22] That was what took 4 minutes [00:22] What's the plan? [00:24] wgrant: http://pastebin.ubuntu.com/1274106/ and http://pastebin.ubuntu.com/1274107/ [00:38] wgrant: Still distracted? [01:01] StevenK: Sorry, food was required. [01:01] Let's see [01:02] StevenK: So, do you see in the plan what I described on the call this morning? [01:03] That's okay, I amused myself by destroying Bug{Security,Visibility}Change. [01:03] Great [01:03] y% bzr di | diffstat -s [01:03] 7 files changed, 36 insertions(+), 236 deletions(-) [01:04] :) [01:04] wgrant: So I can recall you said there are two ways postgres can look up the information, either by the bug or the team [01:04] And it's obviously going via the bug due to: [01:04] -> Index Scan using bug_pkey on bug (cost=0.00..0.62 rows=1 width=4) (actual time=1.729..1.729 rows=0 loops=147850) [01:04] Index Cond: (id = bugsubscription.bug) [01:05] Filter: ((id = 429322) OR (duplicateof = 429322)) [01:05] StevenK: Not quite. [01:05] The plan is largely executed from inside to out, top to bottom [01:05] So it starts with the innermost topmost thing [01:05] In this case, the teamparticipation index scan [01:06] So it's finding all teamparticipations for the person in question [01:06] And if you look at the right, you'll see "rows=27" [01:06] So it finds 27 teamparticipation rows [01:06] for that person [01:10] wgrant: Right [01:10] Then it you go up to the parent node [01:10] wgrant: And that feeds into the bugsubscription index with loops=27 [01:10] And see it's a nested loop [01:10] A nested loop has two nodes: the second is executed once for every row in the first node [01:10] So for each teamparticipation record, it finds the relevant bugsubscriptions [01:10] And there are 27 rows, so loops=27 [01:11] Ah, right [01:11] The row count there is an average [01:11] So, now we exit the first nested loop [01:11] And find ourselves in a second one [01:11] With 150000 rows to process [01:12] *Now* we query the bug for each of those 150000 bugsubscriptions [01:12] And check whether it matches our filter [01:12] So that's roughly 150000 random seeks [01:12] (although in reality it'll be cached, it's still not good) [01:12] Right [01:12] Once we find the bugs that match, we do one final nested loop to join the people so we can return Person.name [01:12] So, what's the problem here? [01:13] wgrant: That bug number is the worst of the lot with 1.1k dupes [01:13] Right [01:13] Is there an alternate strategy that postgres could use to get the same result? [01:13] wgrant: I guess we'd ideally like to answer the bugsubscriptions for the bug and the person in one shot using an index [01:13] Not really [01:14] The problem here is that it has to do 150000 index lookups [01:14] If we can minimise that step, we win [01:14] It wouldn't be that many [01:14] Since it filter the index based on the information we want [01:15] An alternate strategy is to first ask for all the duplicate bugs, then for each bug look for the subscriptions, and compare them to a list of teams that we participate in [01:15] That works out to roughly one index scan and a hash lookup for each bug [01:15] wgrant: Using a JOIN, or what are you thinking? [01:16] Well, once we've devised a plan, we need to trick postgres into using it [01:16] We can't tell it directly. === matsubara is now known as matsubara-afk [01:18] wgrant: And we do that by tweaking the query? [01:18] Right [01:18] It requires a bit of experimentation [01:19] Often you can force it to do your bidding using subqueries or CTEs [01:20] eg. a common thing is to convince it to build a hash of the teams we're looking for [01:20] By using " [01:20] WITH teams AS (SELECT team FROM teamparticipation WHERE person = 1191103)" [01:22] wgrant: http://pastebin.ubuntu.com/1274170/ [01:22] That's a different query, isn't it? [01:22] Although it is quite related [01:22] And a similar plan [01:22] Yeah, it's different [01:22] Bug.id IN (SELECT id FROM BUG WHERE id = 429322 OR duplicateof = 429322) [01:23] Right [01:23] Versus Bug.id = 429322 OR Bug.duplicateof = 429322 [01:23] Note also in both plans where postgres goes wrong [01:23] After the nested loop against bug: [01:23] Yeah, in the same place [01:23] -> Nested Loop (cost=0.00..1172.79 rows=139 width=16) (actual time=0.063..2313.480 rows=147850 loops=1) [01:23] The first parenthesised bit is the estimate [01:23] The second is the actual [01:23] So it expected it'd get 139 bugs [01:23] But actually got 147850 [01:23] It estimated ~150 rows and got back ~150k ? [01:24] Right [01:24] Its plan was a good one given the estimates it had [01:25] wgrant: How does the teamparticipation CTE look? [01:26] StevenK: Monumentally unsuccessful [01:26] Maybe a bug CTE? [01:28] StevenK: That might be worth a try [01:28] It could convince it to use a hash for the bug lookups [01:28] And the query in IPersonSubscriptionInfo._getDirectAndDuplicatesSubscriptions has the same estimate versus actual factor difference, so the same change will probably make both queries performant [01:28] Right :) [01:29] WITH bugs AS (SELECT id FROM bug WHERE id = 429322 OR duplicateof = 429322) [01:29] Right [01:29] Where does that go in the query, though? [01:29] CTEs always go at the start [01:30] eg [01:30] WITH teams AS (SELECT team FROM teamparticipation WHERE person = 1191103), bugs AS (SELECT id FROM bug WHERE duplicateof = 429322) [01:30] SELECT BugSubscription.id, BugSubscription.bug [01:30] [...] [01:30] and then WHERE Bug.id IN bugs ? [01:30] Bug.id IN (SELECT id FROM bugs) [01:30] It appears as a table [01:30] Not an expression itself [01:31] (also note that the CTE I quoted ignores the bug.id check, just to simplify this initial experimentation) [01:31] So it only counts duplicates, not the bug itself [01:31] http://pastebin.ubuntu.com/1274176/ [01:32] It still sucks as estimating [01:32] It's never going to estimate well [01:32] Because this is an utterly extraordinary case [01:32] We have to force its hand [01:33] Let me try the two CTEs like you suggest [01:33] That sounds like a good plan [01:38] wgrant: http://pastebin.ubuntu.com/1274179/ [01:40] StevenK: You have a pointless bug join in your main query [01:40] StevenK: You can just say bugsubscription.bug IN (SELECT id FROM bugs) [01:48] StevenK: Any luck? [01:53] wgrant: Sorry http://pastebin.ubuntu.com/1274200/ [01:54] StevenK: Oh [01:55] StevenK: You have this person join there that's unconstrained [01:55] wgrant: I don't need Person.id IN ... ? [01:55] I wasn't really sure about that bit [01:55] You perhaps want "AND Person.id = BugSubscription.person" instead [01:55] So Person is actually joined against BugSubscription [01:58] wgrant: http://pastebin.ubuntu.com/1274207/ [01:58] That one came back quicker than I was expecting. :-) [01:59] Indeed, that's more like it [01:59] Now, index-only scans would make a pretty nice difference here [01:59] Sadly we don't have them yet [02:00] StevenK: It may be helpful to see what's going on by using EXPLAIN (ANALYZE ON, VERBOSE ON) instead of EXPLAIN ANALYZE [02:00] THen it'll show the columns it grabs from each result node [02:01] wgrant: http://pastebin.ubuntu.com/1274209/ [02:02] So we can now see exactly what it's doing [02:02] StevenK: It's still trying to go TeamParticipation->BugSubscription->Bug, as you can see [02:02] But that's faster now since it's just a hash lookup on a thousand bugs [02:02] Not an index lookup [02:03] It may be worth trying to convince it to start at the bugs, then go to the bugsubscriptions [02:03] So you want faster still? [02:03] Perhaps by creating a CTE of bugsubscriptions [02:03] StevenK: Right [02:03] StevenK: This is still quite slow [02:03] Fast enough to work [02:03] But it'd be nice if we could get it a bit faster [02:03] Nod [02:04] wgrant: Can you have a play, I need to get lunch? [02:04] Sure [02:04] Well [02:04] I already have had a play [02:05] wgrant: Oh? [02:06] might be better to nab the subscriptions and do a constrained cross product with person [02:07] move the person manipulation out of the inner loops [02:08] what you have there is 8 seconds on prod [02:10] mm, you are already. [02:10] * lifeless fiddles [02:10] btw - http://paste.ubuntu.com/1274216/ [02:10] I have a reasonable solution myself, I'm trying to walk StevenK to it :) [02:11] -> Nested Loop (cost=0.07..857.65 rows=4 width=19) (actual time=0.447..622.478 rows=147850 loops=1) is the issue, cold cache effects will hurt you [02:11] lifeless: Hm, which query is that from? [02:11] Yes [02:11] wgrant: http://pastebin.ubuntu.com/1274209/ [02:11] That's what we're trying to eliminate [02:11] Oh, that one, right [02:12] * lifeless tries w/out person === Ursinha is now known as Ursinha-afk [02:13] that distinct you have is distinctly odd [02:13] why do you want order by person.name ? [02:13] I haven't looked at that particular query, so I don't know [02:13] I was looking at the other one [02:13] if its the portlet [02:13] The core loop is similar [02:13] then we're not slicing [02:14] so bring them all back and sort in appserver. [02:14] 1200ms [02:14] 300ms hot [02:15] Right, about 1.5x faster than DF, as usual [02:15] the person including one comes down to 500ms hot [02:15] 229ms [02:15] 344ms [02:15] new one is 285,193, 400 - significantly faste [02:16] he says from probably too few trials [02:17] 5000 lookups on bugsubscription_person_idx [02:17] 11ms there, 35ms on bug hash lookups [02:18] still 159K rows scanning bugsubscriptions [02:18] union time perhaps [02:19] http://paste.ubuntu.com/1274226/ is what I just played with for ref [02:20] Huh [02:20] That's a bit slow [02:20] That sort of thing was like 500ms on DF an hour ago [02:20] Or is it subtly different somehow... [02:21] union is faster at the innermost layer [02:22] * lifeless tries again [02:23] nah, needs bigger hammer [02:25] Yeah [02:25] I have a 15ms hammer [02:25] I'm pretty sure you can't do much better than that [02:26] gnar hash join diaf [02:26] :D [02:26] Then you disable hashjoin [02:27] And it does mergejoin instead :) [02:29] Ah [02:29] And this thing is even <100ms on the worst bug in existence [02:30] wait, wtf is this doing [02:30] its expanding all the teams? [02:30] I mean, if its the portlet, *why are we expanding teams* ? === Ursinha-afk is now known as Ursinha [02:31] or is it 'teams I am a member of that are subscribed' [02:31] that must be it [02:31] That's the one [02:32] http://pastebin.ubuntu.com/1274107/ is similarly misbehaved, but without the insane distinct/order [02:32] The core misbehaviour is identical [02:32] It endeavours to identify those duplicate bugs to which the user or their teams has a bugsubscription [02:33] wheee, seq scan on bugsub [02:33] diaf again. Srs for reals. [02:40] Ah [02:40] And with another tweak it's down to 20ms for the worst bug ever [02:41] you have become very good at this [02:41] you should blog about it [02:41] Hah [02:41] seriously [02:41] I'm down to 13ms [02:41] whats the worst bug ever ? [02:42] or is that bug, person combo I've been using the worst case already ? [02:42] On DF, 429322 and 419488 are probably at the top of their respective badness dimensions [02:43] 7ms [02:43] for the pair I've been testing [02:43] which is bug 42933 and person 1191103 [02:43] <_mup_> Bug #42933: Kubuntu Installer crashed trying to manually edit partitions table -- Reproduceable < https://launchpad.net/bugs/42933 > [02:44] wgrant: http://paste.ubuntu.com/1274251/ [02:45] person 419488 gives me 0.168ms [02:45] I mean bug 419488 [02:45] One has the most dupes, the other has the most dupe subscribers [02:45] ah, stay with person 1.1M ? [02:45] Right [02:46] 14ms [02:46] wgrant: I'm curious what you came up with [02:46] wgrant: and serious about bloggin :) [02:49] sec [02:49] just trying with another bug [02:49] 492322 is very strange [02:50] Er, 429322 [02:50] It has 1107 duplicates [02:51] But only 35 dupe subscribers, apparently [02:51] 659438 has around 1000 of each [02:52] lifeless: http://paste.ubuntu.com/1274256/ is what I had [02:52] 333 cold, 18 hot [02:52] On 659438 the performance is basically identical to yours [02:52] 26-27ms [02:52] for 659438 [02:52] I can probably inline two of those CTEs [02:52] as low as 11 [02:52] I haven't tried since createing the bugsub CTE [02:52] Which is the critical bit [02:53] Ah [02:53] So yours is basically the same [02:53] I think you'll trigger hash joins [02:53] Just unformatted and with joins instead [02:53] I threw a union in there [02:53] CAn you try mine on prod/staging with 429322, 419488 and 659438? [02:54] 52,12,20 [02:54] :( [02:54] 19,11,12 [02:55] 39,16,13 [02:55] Oh [02:55] Those are three runs from each? [02:55] So mine's pretty competitive [02:55] yes [02:55] Yay [02:55] yah, and a bit simpler [02:55] but returning different data [02:56] Is it? :/ [02:56] Oh, just the missing decorations like person and distincts and such [02:56] I'm returning the person [02:56] I ripped them out since they weren't relevant to optimising the core [02:56] you're returning the subscription id and the bug [02:56] Yeah [02:57] 42, 29, 36 === Ursinha is now known as Ursinha-afk [02:57] I was worried that this technique would be terrible for people with few teams on bugs with tonnes of dupe subs [02:57] doing 6* with changed return column to bugsubscription.person [02:57] But it seems to be pretty fast [02:57] Hm [02:57] Mysterious [02:58] I get 66,11,25 doing three of mine on it [02:58] more variance [02:58] 10,27,27 repeating [02:59] Anyway, a bit better than what we had before [02:59] induitable [02:59] Though I think we might have scared StevenK away [02:59] y [02:59] I just got back from lunch [02:59] Since it's raining cats and dogs, I had to drive [03:00] teamparticipation_person_idx + bug_duplicateof_idx + bugsubscription_bug_idx and a hash on the directsubscribers result. [03:00] lifeless: I had this 20ms solution before you started investigating, FWIW :P [03:00] Right, that's basically the optimal plan, AFAICT [03:00] Excellent, so now you share :-P [03:00] for you, teamparticipation_person_idx a BitmapOr on bug_duplicateof_idx, and - here is the difference - a HashAggregate on bugs + bugsubscription_bug_idx scan. [03:01] and then you also on top o fthat do a hash for the teamwork [03:01] wgrant: I know, but you were teasing StevenK with a solution you had. [03:02] wgrant: which btw is slightly cruel. [03:03] I hadn't revealed I had a good solution yet! [03:04] I was hoping to walk StevenK through optimising that query, but I had to know I was going the right direction first :) [03:07] I didn't think he was teasing [03:08] He was waiting for me to catch up [03:08] Admitedly, he could have landed and QA'd his solution before I got there [03:08] lifeless: Inlining my bugs/teams CTEs as joins gets removes the excess hashes. [03:08] mentoring is good [03:08] wgrant: /query me the new one [03:08] wgrant: Ah ha, so we want Joins [03:09] StevenK: Well, yes and no. The key is a CTE [03:09] Oh, so BOTH. A CTE and we join on it? [03:09] wgrant: interestingly I have that union *because* without it I got big dirty great hashes on bugs [03:10] lifeless: Interesting [03:10] 258ms [03:10] lifeless: I've run into that before, but it doesn't end up as a problem here [03:10] Huh [03:10] 15ms [03:10] That very query [03:10] Ah :) [03:10] 14ms [03:10] It's 24ms on DF [03:10] fwiw mine is still strictly faster :P [03:11] fuuuu [03:12] Where's the time? [03:12] Might be able to save a couple of ms with a slightly different TP join [03:13] http://paste.ubuntu.com/1274276/ [03:14] both in one paste [03:14] best of the runs I just did for each. [03:14] Yours is actually 1.5ms slower on DF [03:14] Intriguing, thanks [03:14] inconsequential diff on teams [03:14] Yeah [03:15] I'm not sure how your union wins [03:15] But it does [03:15] directsubscribers is 3ms faster [03:15] unions are good [03:15] Right, but it should be inconsequential here [03:16] It's probably the extra heap scan on bug [03:16] Because the bitmapor needs a recheck [03:16] Yeah, that likely explains it [03:16] It's trivial in just about every case [03:17] it is inconsequential, its like 20% on a query with 5000% variance [03:17] But we are competing over trivial differences here, exactly :) [03:17] whose competing :) [03:17] <- ego. [03:17] Heh [03:18] right, time to mail silbs [03:18] :( [03:19] Indeed :-( [03:20] Hm [03:20] You might be getting away without the recheck because of your limited select list [03:21] Yep [03:21] I think [03:22] ... or not [03:22] lifeless: Your query has the heap scan when run on DF [03:22] Around the bug_duplicateof_idx scan [03:22] Same as mine... [03:23] And that indeed explains the extra 2ms [03:25] wgrant: combine both and get a win ? [03:25] Hah, this might mean that we can destroy self._subscriber_cache in IBug [03:26] If the query in getSubscribersForPerson is performant, we can just check [03:26] StevenK: long as you don't destroy they 'and folk unsubscribing, unassigning etc still get notified hack that that cache serves' [03:27] lifeless: The only use of _subscriber_cache was in another method [03:31] lifeless: So I don't think that cache serves that function [03:31] I think that cache serves that function because the query was a pig [03:31] StevenK: It'll be fast, but still not as fast as looking at a local cache [03:31] We don't want to run this query more than once a request [03:31] Aww [03:31] It's still not superfast, and probably cannot be [03:31] 20ms is quite a while [03:31] Or 14ms or whatever the latest result was [03:32] And, in general, you don't want to be repeating queries at all [03:33] Right [03:33] So I'll leave it alone, sadly [03:33] INNER JOIN is the same as LEFT JOIN, isn't it? [03:34] No [03:34] LEFT JOIN is shorthand for LEFT OUTER JOIN [03:34] A plain JOIN is an INNER JOIN [03:34] Right [03:34] No, a RIGHT JOIN is also an outer join :) [03:35] Bleh [03:36] Blah, lifeless' query is too simple [03:36] Yeah, lifeless' doesn't give you enough to get the subs/bugs back [03:36] Just the teams [03:38] And I don't understand your query either :-( [03:42] Right [03:42] Now I have a query, I need to translate it to Storm [03:42] You just want the bug IDs, right? [03:43] Which is messy since you're using the CTE as the main table in FROM [03:43] I forget the original query [03:43] Right, that is a tad awkward [03:43] IBug.getSubscribersForPerson() wants (Person, BugSubscription) [03:43] Ah, I forgot about that method [03:43] Let's see what it actually does [03:43] Populates a cache using DRS and results the results [03:44] returns the results even [03:49] StevenK: Look what it does with the the BugSubscription, though [03:49] def cache_subscriber(row): [03:49] subscriber, subscription = row [03:49] if subscription.bug_id == self.id: [03:49] self._subscriber_cache.add(subscriber) [03:49] else: [03:49]