[01:24] <StevenK> wgrant: So, are you nervous enough about my change in https://code.launchpad.net/~stevenk/launchpad/productseries-preload-for-merges/+merge/153052 ? I spent most of yesterday fighting with strange LocationErrors
[01:25] <wgrant> StevenK: Mmm
[01:25] <wgrant> It's safe
[01:25] <wgrant> Was just slightly concerned about the possibly late preloading
[01:25] <wgrant> But this can't make it worse
[01:25] <wgrant> r=me
[04:41] <StevenK> wgrant: Wasn't Curtis talking about patching zconfig for bug 481512?
[04:41] <_mup_> Bug #481512: race condition when rotating logs <canonical-losa-lp> <lp-foundations> <qa-untestable> <webapp-infrastructure> <Launchpad itself:Triaged> <ZConfig:Fix Released by fdrake> < https://launchpad.net/bugs/481512 >
[04:45] <wgrant> StevenK: Yes, he was talking to upstream about it
[04:45] <wgrant> Might have eventually given up
[04:46] <StevenK> I can recall him mentioning just patching it into ours
[04:46] <wgrant> Yup
[04:46] <StevenK> But I don't recall the condition
[04:46] <wgrant> That's what you do once you give up talking to upstream
[04:48] <StevenK> wgrant: So, back to our argument about Archive:+delete-packages, you mentioned a change to the query of doom, but I can't recall it
[04:49] <wgrant> StevenK: A possible optimisation is to find sources where scheduleddeletiondate is null
[04:49] <wgrant> Since that should include any that have published binaries
[04:50] <StevenK> wgrant: I was pondering a new column in SPPH that links to published BPPHs, but that may result in you murdering me.
[04:51] <wgrant> StevenK: How would that be a) stored, and b) kept up to date?
[04:52] <StevenK> wgrant: a) as int[], b) probably via newPublication, c) I haven't considered how overrides fit in
[04:52] <wgrant> It's just about impossible to keep that consistent without very big locks
[04:52] <wgrant> And I like to keep my publishing tables consistent
[05:16] <StevenK> wgrant: http://pastebin.ubuntu.com/5615641/
[05:20] <wgrant> StevenK: What's the query?
[05:22] <StevenK> wgrant: http://pastebin.ubuntu.com/5615648/
[05:23] <StevenK> My index on DF didn't work :-(
[05:23] <StevenK>                            ->  Seq Scan on sourcepackagepublishinghistory  (cost=0.00..67337.11 rows=49673 width=135) (actual time=16.419..2005.405 rows=2893 loops=1)
[05:23] <StevenK>                                  Filter: ((scheduleddeletiondate IS NULL) AND (archive = 32))
[05:25] <StevenK> CREATE INDEX temp_spph ON sourcepackagepublishinghistory(archive, scheduleddeletiondate); being the index I created
[05:25] <wgrant> It probably decides that the uncondemned pubs are dense enough that it's not worth it
[05:25] <wgrant> Though with those counts it seems unlikely...
[05:26] <StevenK> I wonder if an (scheduleddeletiondate, archive) index will work
[05:27] <wgrant> won't make a difference
[05:27] <wgrant> archive WHERE scheduleddeletiondate IS NULL might
[05:30] <StevenK>                            ->  Seq Scan on sourcepackagepublishinghistory  (cost=0.00..67337.11 rows=49673 width=135) (actual time=16.185..1321.371 rows=2893 loops=1)
[05:30] <StevenK>                                  Filter: ((scheduleddeletiondate IS NULL) AND (archive = 32))
[05:30] <wgrant> What's archive 32?
[05:31] <StevenK> ~ubuntu-langpack/+archive/ppa
[05:31] <wgrant> Only 126k total pubs
[05:32] <StevenK> Yes, it's the pathological case
[05:34] <wgrant> Anyway, the idea of the scheduleddeletiondate bit was to hopefully *replace* the binary check, not augment it
[05:34] <StevenK> Oh
[05:35] <StevenK> (SourcePackagePublishingHistory.scheduleddeletiondate IS NULL OR SourcePackagePublishingHistory.status IN (1, 2)) ?
[05:37] <wgrant> You can probably drop the latter
[05:37] <wgrant> Since a published or pending pub won't be condemned
[05:38] <StevenK> wgrant: Sorry, I'm having trouble visualising the query with the published binaries clause killed
[05:42] <wgrant> StevenK: Just drop the whole AND (EXISTS ... OR status IN (1, 2)) bit
[05:42] <wgrant> scheduleddeletiondate handles both of those, if things are working correctly
[05:43] <StevenK> Time: 414.243 ms
[05:44] <StevenK> I can paste the EXPLAIN ANALYZE for that if you wish
[05:45] <wgrant> Not particularly. If it's using the index there's probably not much improvement to be made
[05:46] <StevenK> It's using                                 Recheck Cond: (archive = 32)
[05:46] <StevenK>                                  Filter: (scheduleddeletiondate IS NULL)
[05:46] <StevenK>                                  ->  Bitmap Index Scan on securesourcepackagepublishinghistory__archive__status__idx  (cost=0.00..2395.95 rows=128988 width=0) (actual time=32.526..32.526 rows=126534 loops=1)
[05:46] <StevenK>                                        Index Cond: (archive = 32)
[05:46] <wgrant> Blah
[05:46] <wgrant> That doesn't make much sense
[05:46] <wgrant> It's using an index, so it has no reason not to use the complete one
[05:46] <StevenK> Neither of the indexes I tried didn't work
[05:54] <StevenK> wgrant: Do we care for a full index?
[05:54] <StevenK> postgres seems hell bend on not using one
[05:54] <wgrant> I'm not sure why it won't use it, but it should be OK
[05:54] <StevenK> wgrant: http://pastebin.ubuntu.com/5615680/
[05:56] <StevenK> I'm not sure I care enough to Storm-ify it, but should be much easier now
[05:56] <wgrant> Very easy
[05:58] <StevenK> wgrant: Means the prejoin crap dies too
[05:58] <wgrant> Yes, just use load_related
[06:00] <StevenK> wgrant: It uses SPR.version for ordering
[06:00] <wgrant> Yes, but that's separate from the prejoin
[06:00] <wgrant> You could use DRS to avoid the load_related
[06:00] <wgrant> Given that you have to select SPN and SPR anyway
[06:25] <lifeless> stub: o/
[06:25] <stub> lifeless: yo!
[06:27] <StevenK> wgrant: Hmm, I think I'm bashing into sampledata stupidity