@TimStone Oh sorry, wrong chatroom it seems. Anyway someone told me that cs-pls isn't necessary on meta due to our high activity :/
2 hours later…
Anonymous
8:26 AM
It would be nice to be able to save queries even if they time out. If I can't figure out how to make it behave well yet, I need to save it elsewhere.
Anonymous
The query planner doesn't seem to be able to optimize the post and vote type names to perform as well as specifying their Ids directly.
Anonymous
SELECT p.Id as [Post Link],
p.Id as [Post Id],
pt.Name as [Post Type],
vt.Name as [Vote Type],
v.UserId as [User Link],
v.CreationDate as [Vote Date],
p.Body
FROM Votes v
JOIN Posts p
ON v.PostId = p.Id
JOIN PostTypes pt
ON p.PostTypeId = pt.Id
JOIN VoteTypes vt
ON v.VoteTypeId = vt.Id
WHERE pt.Name NOT IN ('Question', 'Answer')
AND vt.Name NOT IN ('Deletion', 'ApproveEditSuggestion')
@TimSiteisaForum Hmm...Yeah. Will think about that.
I don't think that those helper tables have a unique index that would allow it to know there's a one-to-one relationship between Name and Id, so it may be doing the join first (since it doesn't know which step will be more expensive), then filtering. That's just early morning speculation though, would have to look at the query plans.
Anonymous
11:45 AM
Hmm, for that same reason it's not possible to see the query plans for these troublesome queries. I don't know how it works, but it would be cool if the query plans could be loaded before the data, or at least once the query itself as timed out.
@TimSiteisaForum The query plan is returned as part of the execution result (it's the actual plan, not the estimated one), so if the query times out there's no results to get it from I'm afraid.
Which reminds me, there are also some minor display issues (likely due to me changing things around a bit) with the query plans...will add that to my to-do list for this weekend.
With regard to the performance, I don't think you were wrong, but obviously more expensive queries run the risk of butting heads with whatever other queries happen to be executing at the time.
Anonymous
@TimStone Right-o. I can't help feeling slightly abusive and guilty when I run these over-the-top queries, so I'm sure not complaining. :P
Yeah, I've had a few cases where I've run something on the actual site and somehow forgot some key where condition or something that caused the query to do a lot more work than I intended.
Sometimes, in fear of that, I cheat and run things locally first just to make sure they won't take forever. :D
