[02:49] <wgrant> lifeless: Around?
[02:59] <lifeless> yes
[02:59] <lifeless> wassup
[02:59] <lifeless> wgrant: ^
[03:01] <wgrant> Was wondering if you could try https://pastebin.canonical.com/59988/ on qastaging. It takes >100ms in OOPS, but 10ms when run in psql on DF.
[03:01] <wgrant> I wonder if person descriptions are big.
[03:01] <wgrant> Monday's OK too :)
[03:02] <wgrant> lifeless: ^^
[03:03] <lifeless> 218.988ms on wildcherry
[03:04] <wgrant> :/
[03:04] <wgrant> Thanks
[03:04] <lifeless> 100ms in explain analyze
[03:04] <lifeless> but still ~200 when rerun as a normal query
[03:04] <lifeless>  Total runtime: 4.484 ms
[03:04] <lifeless> (23 rows)
[03:04] <lifeless> Time: 78.842 ms
[03:04] <wgrant> Heh
[03:04] <lifeless> note the startup vs exec difference - we've seen this before
[03:04] <wgrant> Planning overhead?
[03:05] <lifeless> IIRC it was stats access stuff
[03:05] <wgrant> Yeah
[03:05] <wgrant> We ran into this when we increased the stat sizes.
[03:06] <lifeless> should be a UNION ALL
[03:06] <wgrant> No, it shouldn't exist at all :)
[03:07] <lifeless> yeah, 77ms to do the explain
[03:07] <lifeless> (not explain analyze - just explain)
[03:07] <wgrant> Right.
[03:08] <wgrant> (this is person.administrated_teams, as called by the structural subscriptions JS to populate a form field on most structural subscription context pages)
[03:08] <lifeless> what is this checking (my brain is to whacked to reverse engineer the meaning)
[03:08] <lifeless> too
[03:08] <lifeless> so, its checking tp + membership to find the admins ?
[03:09] <lifeless> the second clause looks odd to me
[03:09] <lifeless> ah, owned by any team the person is in
[03:09] <wgrant> It's checking for membership in an admin or an owner
[03:10] <lifeless> so first clause is 'in the team as an admin' and the second is '...
[03:11] <lifeless> the sort shouldn't be there
[03:11] <wgrant> Well
[03:12] <wgrant> It wants a sorted list.
[03:12] <wgrant> The issue is probably more that the query shouldn't be there.
[03:12] <wgrant> Because I suspect that form is used on <0.1% of requests
[03:13] <lifeless> ah, for subscribe a team ?
[03:13] <lifeless> sure, I buy that too
[03:13] <wgrant> And of the times that form is used, about 1% are probably for teams.
[03:13] <wgrant> And of those, about 20% are probably not misguided :)
[03:14] <lifeless> its the first clause that is an issue - the one with tm
[03:14] <lifeless> 188K rows, so its now exactly huge
[03:16] <wgrant> Should be an index scan down TP, into a 200-row read from TM, filtering down to a few rows, which then are read from person
[03:17] <lifeless> it runs in 2.6ms
[03:17] <lifeless> it plans in 70->140ms
[03:17] <wgrant> Hah
[03:17] <lifeless>  Nested Loop  (cost=0.00..753.27 rows=2 width=633)
[03:17] <lifeless>    ->  Nested Loop  (cost=0.00..748.78 rows=2 width=4)
[03:17] <lifeless>          ->  Index Scan using teamparticipation_person_idx on teamparticipation  (cost=0.00..147.56 rows=114 width=4)
[03:18] <wgrant> That's a bit of worry
[03:18] <lifeless>                Index Cond: (person = 21997)
[03:18] <lifeless>          ->  Index Scan using membership_person_key on teammembership  (cost=0.00..5.26 rows=1 width=8)
[03:18] <wgrant> As it touches like 4 tables.
[03:18] <lifeless>                Index Cond: (teammembership.person = teamparticipation.team)
[03:18] <lifeless>                Filter: (teammembership.status = 3)
[03:18] <lifeless>    ->  Index Scan using person_pkey on person  (cost=0.00..2.23 rows=1 width=633)
[03:18] <lifeless>          Index Cond: (person.id = teammembership.team)
[03:18] <lifeless>          Filter: (person.merged IS NULL)
[03:18] <lifeless> (10 rows)
[03:18] <lifeless> 3
[03:18] <wgrant> Right, that's what I would have done :)
[03:19] <lifeless> I want 'explain explain'
[03:19] <wgrant> Heh
[03:19] <wgrant> Also, I want to talk to you next week about weaning SSO.
[03:20] <wgrant> If you have time.
[03:20] <lifeless> that would be wuverly
[03:20] <lifeless> gotta get it on solids first
[03:21] <wgrant> I got it mostly working on the way back from Budapest, but there are availability and performance concerns.
[03:21] <lifeless> right
[03:21] <lifeless> so lets talk early in the week
[03:21] <lifeless> get me across the parameters
[03:22] <wgrant> Yep
[03:22] <lifeless> and I will either talk with, or setup a conf with, the sso folk
[03:22] <lifeless> depending on $stuff, $timing etc
[03:23] <wgrant> Thanks.
[03:25] <lifeless> de nada :)
[05:01] <wgrant> lifeless: Can you see any reason that person validity checks should require a search for a preferredemail, rather than having a constraint that account_status == ACTIVE => there is a preferredemail?
[05:01] <wgrant> Trying to kill off validpersoncache and all these stupid repeated emailaddress.status == 4 queries.
[06:00] <lifeless> uhm
[06:01] <lifeless> do we change account_status -> inactive if the last email address gets invalidated ?
[06:01] <lifeless> wgrant: I suspect the question needs to be put to the list; or we need to do some re-analysis; I don't have an easy answer.
[06:06] <wgrant> lifeless: The only thing that can now cause the last email to be invalidated is deactivation/suspension.
[06:06] <wgrant> lifeless: And those two only do it because the lack of a preferredemail is how we determine that a person is invalid.
[06:15] <lifeless> wgrant: I think we'd want some safeguard to ensure consistent data
[06:15] <lifeless> but I see no reason why we shouldn't trust the status field if we do that
[06:15] <wgrant> "having a constraint that account_status == ACTIVE => there is a preferredemail"
[06:15] <lifeless> something like that yes
[06:15] <lifeless> a preferred validated mail
[06:16] <lifeless> blah, youknowwhatImean
[06:16] <wgrant> preferred => validated, but yeah
[12:08] <nigelb> StevenK: watched cricket? :D
[12:23] <StevenK> nigelb: No. But I saw the result.
[12:30] <nigelb> :D :D
[23:08] <lifeless> (ht spm: http://blog.markwshead.com/1148/design-problem/)
[23:13] <wgrant> Yay, sinzui did most of my QA
[23:17] <lifeless> not what I expected https://launchpad.net/lca
[23:45] <wallyworld_> lifeless: there's a branch of python-oops-amqp ready to be merged into trunk make release 0.0.6 (i haven't done the merge yet). but i don't have pypi permissions to create a release. is that something you can do?
[23:46] <StevenK> wallyworld_: Can haz some QA?
[23:46] <wallyworld_> sure, just getting a couple of things done
[23:47] <lifeless> wallyworld_: whats your pypi userid ?
[23:48] <wallyworld_> lifeless: wallyworld
[23:48] <lifeless> wallyworld_: also, you need to actually merge, else trunk shows all the intermediate work.
[23:48] <wallyworld_> ok
[23:49] <lifeless> wallyworld_: added you as maintainer; should let you do it
[23:49] <wallyworld_> lifeless: thank you muchly
[23:49] <lifeless> de nada
[23:49] <lifeless> would love a team-plugin for pypi