[01:24] 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] StevenK: Mmm [01:25] It's safe [01:25] Was just slightly concerned about the possibly late preloading [01:25] But this can't make it worse [01:25] r=me === jtv1 is now known as jtv [04:41] wgrant: Wasn't Curtis talking about patching zconfig for bug 481512? [04:41] <_mup_> Bug #481512: race condition when rotating logs

< https://launchpad.net/bugs/481512 > [04:45] StevenK: Yes, he was talking to upstream about it [04:45] Might have eventually given up [04:46] I can recall him mentioning just patching it into ours [04:46] Yup [04:46] But I don't recall the condition [04:46] That's what you do once you give up talking to upstream [04:48] 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] StevenK: A possible optimisation is to find sources where scheduleddeletiondate is null [04:49] Since that should include any that have published binaries [04:50] wgrant: I was pondering a new column in SPPH that links to published BPPHs, but that may result in you murdering me. [04:51] StevenK: How would that be a) stored, and b) kept up to date? [04:52] wgrant: a) as int[], b) probably via newPublication, c) I haven't considered how overrides fit in [04:52] It's just about impossible to keep that consistent without very big locks [04:52] And I like to keep my publishing tables consistent [05:16] wgrant: http://pastebin.ubuntu.com/5615641/ [05:20] StevenK: What's the query? [05:22] wgrant: http://pastebin.ubuntu.com/5615648/ [05:23] My index on DF didn't work :-( [05:23] -> 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] Filter: ((scheduleddeletiondate IS NULL) AND (archive = 32)) [05:25] CREATE INDEX temp_spph ON sourcepackagepublishinghistory(archive, scheduleddeletiondate); being the index I created [05:25] It probably decides that the uncondemned pubs are dense enough that it's not worth it [05:25] Though with those counts it seems unlikely... [05:26] I wonder if an (scheduleddeletiondate, archive) index will work [05:27] won't make a difference [05:27] archive WHERE scheduleddeletiondate IS NULL might [05:30] -> 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] Filter: ((scheduleddeletiondate IS NULL) AND (archive = 32)) [05:30] What's archive 32? [05:31] ~ubuntu-langpack/+archive/ppa [05:31] Only 126k total pubs [05:32] Yes, it's the pathological case [05:34] Anyway, the idea of the scheduleddeletiondate bit was to hopefully *replace* the binary check, not augment it [05:34] Oh [05:35] (SourcePackagePublishingHistory.scheduleddeletiondate IS NULL OR SourcePackagePublishingHistory.status IN (1, 2)) ? [05:37] You can probably drop the latter [05:37] Since a published or pending pub won't be condemned [05:38] wgrant: Sorry, I'm having trouble visualising the query with the published binaries clause killed [05:42] StevenK: Just drop the whole AND (EXISTS ... OR status IN (1, 2)) bit [05:42] scheduleddeletiondate handles both of those, if things are working correctly [05:43] Time: 414.243 ms [05:44] I can paste the EXPLAIN ANALYZE for that if you wish [05:45] Not particularly. If it's using the index there's probably not much improvement to be made [05:46] It's using Recheck Cond: (archive = 32) [05:46] Filter: (scheduleddeletiondate IS NULL) [05:46] -> 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] Index Cond: (archive = 32) [05:46] Blah [05:46] That doesn't make much sense [05:46] It's using an index, so it has no reason not to use the complete one [05:46] Neither of the indexes I tried didn't work [05:54] wgrant: Do we care for a full index? [05:54] postgres seems hell bend on not using one [05:54] I'm not sure why it won't use it, but it should be OK [05:54] wgrant: http://pastebin.ubuntu.com/5615680/ [05:56] I'm not sure I care enough to Storm-ify it, but should be much easier now [05:56] Very easy [05:58] wgrant: Means the prejoin crap dies too [05:58] Yes, just use load_related [06:00] wgrant: It uses SPR.version for ordering [06:00] Yes, but that's separate from the prejoin [06:00] You could use DRS to avoid the load_related [06:00] Given that you have to select SPN and SPR anyway [06:25] stub: o/ [06:25] lifeless: yo! [06:27] wgrant: Hmm, I think I'm bashing into sampledata stupidity === yofel_ is now known as yofel === noodles785 is now known as noodles775 === wedgwood_away is now known as wedgwood === wedgwood is now known as wedgwood_away === wedgwood_away is now known as wedgwood === Ursinha_ is now known as Ursinha === Ursinha-afk is now known as Ursinha === almaisan-away is now known as al-maisan === Ursinha-afk is now known as Ursinha === al-maisan is now known as almaisan-away === Ursinha-afk is now known as Ursinha === wedgwood is now known as wedgwood_away