yeah; I'd looked into that but I couldn't figure out how to fix that one
2013-01-19 01945, 2013
navap
:(
2013-01-19 01909, 2013
ianmcorvidae
we may need to kick up the statistics target for some columns of the edit table, I dunno
2013-01-19 01924, 2013
nikki
it's really weird though, because plenty of people with plenty more edits work fine
2013-01-19 01929, 2013
nikki
and plenty of people with plenty less edits also work fine
2013-01-19 01933, 2013
ianmcorvidae
it's choosing a query plan that's sort of silly
2013-01-19 01934, 2013
ianmcorvidae
so
2013-01-19 01941, 2013
ianmcorvidae
basically the thing is
2013-01-19 01945, 2013
nikki
it's stupid? :P
2013-01-19 01949, 2013
navap
It's out to get me
2013-01-19 01950, 2013
ianmcorvidae
above about 20% of the table being returned a sequential scan is best
2013-01-19 01953, 2013
navap
ANd jeroenbot
2013-01-19 01956, 2013
ianmcorvidae
below that an index scan usually is
2013-01-19 01911, 2013
ianmcorvidae
however right around that boundary it's often sort of a toss-up
2013-01-19 01916, 2013
ianmcorvidae
whihc is where navap and jeroenbot are
2013-01-19 01928, 2013
ianmcorvidae
it's choosing a sequential scan but probably should be doing an index in those cases
2013-01-19 01933, 2013
ianmcorvidae
but I'm not sure how to convince it of that :)
2013-01-19 01953, 2013
navap
I don't understand what you mean by 20% of the table is being returned with a sequential scan
2013-01-19 01959, 2013
navap
Can you elaborate?
2013-01-19 01912, 2013
nikki
20% of 20 million is 4 million edits...
2013-01-19 01922, 2013
ianmcorvidae
er, maybe it's not 20%
2013-01-19 01956, 2013
ianmcorvidae
looking at the ticket it's a frequency of about .0025 or higher that it's choosing a sequential scan instead of using edit_idx_editor
2013-01-19 01923, 2013
navap
Isn't that 10,000? I've got 40,000+ edits
2013-01-19 01928, 2013
ianmcorvidae
this applies to people with more edits, e.g. nikki/jesus as well, but for them it's the correct choice
2013-01-19 01955, 2013
nikki
it loads in about 5 seconds for me
2013-01-19 01958, 2013
ianmcorvidae
yeah, you're near the upper end of the people having trouble
2013-01-19 01959, 2013
navap
jeroenbot has 70,000
2013-01-19 01924, 2013
ianmcorvidae
basically the issue is, sequential reads are easier than random reads
2013-01-19 01926, 2013
ianmcorvidae
indexes are random reads
2013-01-19 01958, 2013
ianmcorvidae
so it'll chose a sequential scan if it thinks it'll be touching enough of the table that the benefits of sequential over random reads will be better
2013-01-19 01916, 2013
ianmcorvidae
it's wrong about where that boundary is though
2013-01-19 01922, 2013
ianmcorvidae
and I don't know how to make it smarter about that
2013-01-19 01947, 2013
ianmcorvidae
it may have something to do with the recency of edits, too -- the plan it's choosing is a backwards sequential scan
2013-01-19 01903, 2013
ianmcorvidae
databases are hard :P
2013-01-19 01923, 2013
ianmcorvidae
or perhaps more simply: I got lucky yesterday figuring out how to fix the /edit/open thing :)
2013-01-19 01937, 2013
navap
heh
2013-01-19 01954, 2013
navap
I notice that rika doesn't have a problem
2013-01-19 01942, 2013
ianmcorvidae
hm, true
2013-01-19 01952, 2013
ianmcorvidae pokes at that again
2013-01-19 01919, 2013
ianmcorvidae
(I think I'd looked at that before and decided why, but I forget, so)
2013-01-19 01926, 2013
ijabz joined the channel
2013-01-19 01926, 2013
navap
jeroenbot takes quite long on rika though
2013-01-19 01933, 2013
navap
ALthough it does eventually load
2013-01-19 01904, 2013
ianmcorvidae
the query is SELECT edit.id, edit.editor, edit.open_time, edit.expire_time, edit.close_time, edit.data, edit.language, edit.type, edit.yes_votes, edit.no_votes, edit.autoedit, edit.status, edit.quality FROM edit WHERE (editor = ?) ORDER BY id DESC OFFSET ? LIMIT 500 btw
2013-01-19 01957, 2013
ianmcorvidae
and yeah, it's fast on my system, but it's using the index scan
2013-01-19 01934, 2013
navap shrugs
2013-01-19 01902, 2013
ianmcorvidae
hm
2013-01-19 01906, 2013
navap
Thankfully (unfortunately) I don't edit much these days so I don't have to refer to my edits as much
2013-01-19 01924, 2013
ianmcorvidae
what's in common here is that rika and my thing both have the default default_statistics_target (of 100), while totoro has 300
2013-01-19 01928, 2013
ianmcorvidae
so I guess that's probably why
2013-01-19 01938, 2013
navap
But today I introduced someone to MB and to give them an example of how people edit I tried to show them my edit queue. They weren't impressed :p
2013-01-19 01946, 2013
ianmcorvidae
haha
2013-01-19 01923, 2013
navap
I also showed them the IA today and tried to relate it by talking about the wayback machine.
2013-01-19 01943, 2013
navap
They tried to view an old google page and kept getting 50x ater 50x
2013-01-19 01948, 2013
navap
Not a good demo
2013-01-19 01955, 2013
ianmcorvidae
heh
2013-01-19 01915, 2013
navap
It all started from the 10PB sticker on my laptop :)
2013-01-19 01956, 2013
Freso
Anyone know if there's a way to attach a branch on GitHub to an issue?
2013-01-19 01934, 2013
ianmcorvidae
it's annoying, as I recall
2013-01-19 01936, 2013
ianmcorvidae
but possible
2013-01-19 01955, 2013
ianmcorvidae
I don't remember specifics other than you have to use curl to their API, you can't do it through their web interface
My editing numbers went down drastically around the time NGS released
2013-01-19 01939, 2013
ianmcorvidae
(by contrast: nikki's is from the third of january :P)
2013-01-19 01959, 2013
nikki
:P
2013-01-19 01908, 2013
ianmcorvidae checks jeroenbot
2013-01-19 01928, 2013
navap
It hasn't edited since 2010
2013-01-19 01934, 2013
ianmcorvidae
ah
2013-01-19 01946, 2013
ianmcorvidae
yeah; the 'bad' plan it's choosing is doing an index scan backwards on edit_pkey
2013-01-19 01954, 2013
ianmcorvidae
i.e., it's looking at every single edit in descending order by ID
2013-01-19 01955, 2013
Ben\Sput has left the channel
2013-01-19 01943, 2013
ianmcorvidae
of course I still have no idea why it's choosing that plan :/
2013-01-19 01949, 2013
navap
There must be several users who have the same problem as mine, I wonder why no one else has said anything
2013-01-19 01903, 2013
ianmcorvidae
I guess it seems like this is most likely to affect people who used to edit a lot but don't edit as much now -- which is a perfect category for not noticing it's broken, I guess, if people are on a downswing in participation
2013-01-19 01921, 2013
Freso
ianmcorvidae: If it's any help, CatCat's edit page always 50x's on me.
2013-01-19 01958, 2013
Freso
(To give you another data point to compare with, if needed, etc.)
2013-01-19 01903, 2013
ianmcorvidae
well, it certainly gets the same query plan
2013-01-19 01915, 2013
ianmcorvidae
could you comment on the ticket to add that note though? easy to forget the list :)
But, hey, even if you don't know *how* to fix it, it seems you've found *what* needs fixing.
2013-01-19 01926, 2013
Freso
That's a big step.
2013-01-19 01934, 2013
ianmcorvidae
well, not really
2013-01-19 01955, 2013
ianmcorvidae
I know the way to solve it for right now given the data we have
2013-01-19 01909, 2013
ianmcorvidae
(other than actually how to make postgres do it, but you know what I mean)
2013-01-19 01935, 2013
ianmcorvidae
but I don't really know what needs fixing to make it not just break again when we get another million edits, or something
2013-01-19 01925, 2013
ianmcorvidae
and yeah, catcat's edits are getting the same problematic query plan, which results in postgres having to scan through more than a million edits before it gets all of the most recent 500 :/
2013-01-19 01934, 2013
zas
anyone knows who is "mwiencek" (on github) here ? i'm still trying to match MB/github/irc nicks ;)
2013-01-19 01943, 2013
ianmcorvidae
zas: that's bitmap
2013-01-19 01910, 2013
zas
ty ;)
2013-01-19 01911, 2013
ianmcorvidae
'lalinsky' on github is 'luks' here, and I forget what Mineo's github name is
2013-01-19 01924, 2013
Freso is "Freso" on GitHub. And MB. But on IRC, I'm "Freso".
2013-01-19 01924, 2013
zas
i'm zas, everywhere.
2013-01-19 01936, 2013
zas
:)
2013-01-19 01939, 2013
ianmcorvidae
ah, mineo is mineo on github
2013-01-19 01941, 2013
ianmcorvidae
that's good
2013-01-19 01901, 2013
ianmcorvidae
I've likewise chosen the completely different name 'ianmcorvidae' for github ;)
2013-01-19 01915, 2013
Freso
caller#6 isn't "caller#6" anywhere, because most places don't allow hash in the username. :|
2013-01-19 01934, 2013
ianmcorvidae
yeah, he's usually callerNo6 or something
2013-01-19 01938, 2013
ianmcorvidae
but that's understandable enough
2013-01-19 01928, 2013
Freso
ianmcorvidae: You should do something to really mindfuck with people and call yourself ianncorvidae or iamcorvidae etc.
2013-01-19 01934, 2013
ianmcorvidae
hah
2013-01-19 01947, 2013
Freso
ianmcorvidae: People would recognise it being you, but they'd never be able to spell it right. >:D
2013-01-19 01951, 2013
ianmcorvidae
haha
2013-01-19 01908, 2013
navap
hrglgrgl comes to mind
2013-01-19 01916, 2013
ianmcorvidae
hrglgrmpf, yes :P
2013-01-19 01925, 2013
zas
btw, i'm trying to work on Picard, but it seems it takes ages to have anything get in, or even few reviews on PRs. It just doesn't work. Not that my PRs are good or anything, but it is very hard to have feedback or help to improve them.
2013-01-19 01927, 2013
navap
:)
2013-01-19 01939, 2013
ianmcorvidae
ianmcørvıdæ clearly
2013-01-19 01955, 2013
Freso
navap: Only people already cannot spell his name.
2013-01-19 01903, 2013
ianmcorvidae
yeah, we don't have many people working on picard :/
2013-01-19 01923, 2013
Freso
navap: Ian's name is easy enough to spell, so add or remove or change a single character, and it'll mess with people.
2013-01-19 01941, 2013
ianmcorvidae
ianmcovridae
2013-01-19 01946, 2013
ianmcorvidae
that one would be fun :P
2013-01-19 01947, 2013
navap
heh
2013-01-19 01952, 2013
zas
about it (Picard), it doesn't support https, but it seems MB will require it soon
2013-01-19 01905, 2013
Freso
zas: Poke Mineo and bitmap and luks on IRC until they give sound. Wait a day or two. Then repeat.
2013-01-19 01913, 2013
Freso
ianmcorvidae: :D
2013-01-19 01917, 2013
ianmcorvidae
nah, requiring HTTPS will be a ways off
2013-01-19 01926, 2013
zas
Freso: then i need a bot ;)
2013-01-19 01936, 2013
navap
ianmcorvidae: What about user authentication?
2013-01-19 01940, 2013
ianmcorvidae
it's required for oauth, but considering that luks is the one who wrote MB's (pending) oauth support in the first place...
2013-01-19 01950, 2013
ianmcorvidae
navap: for the /login page, sure, but that's all
2013-01-19 01956, 2013
ianmcorvidae
for the webservice digest auth is still totally fine
2013-01-19 01907, 2013
Freso
zas: I doubt it'll be hard to add HTTPS support though.
And OAuth still isn't merged into beta, let alone test or anywhere.
2013-01-19 01939, 2013
navap
So no immediate plans to move to digest+ssl? Or is that not really required?
2013-01-19 01941, 2013
zas
Freso: i agree, but since no one seems to work on this software.... it may be takes longer ;)
2013-01-19 01918, 2013
Freso
But Python supports HTTPS easily enough, and I'm sure the Qt stack that I've read several people praise wouldn't make the built-in support worse.
2013-01-19 01949, 2013
Freso
zas: Like ianmcorvidae said, luks is the principal maintainer of Picard, and he's also the one who wrote the OAuth patch - partially becuase he wanted OAuth for Picard.
2013-01-19 01906, 2013
zas
well, what i meant is that Picard wasn't designed with https in mind, looking at hardcoded "http://..." string concat in it
2013-01-19 01918, 2013
Freso
zas: I don't think a Picard patch for OAuth support is far off once the patch actually moves closer to beta/master of musicbrainz-server.
