PostgreSQL on IRC

irc://irc.freenode.net/#postgresql has to be the single most informative source of information via IRC that I've ever seen.

And the main character in there, RhodiumToad, is brilliant. Simply, absolutely brilliant. I'm envious of his knowledge and feel like an idiot whenever I spend time in that channel.

Oh, and this is all happening after 10pm (22:00) Vancouver time on a Friday night (I have a boring life, yay?)...

Here's a snippet - look at the level of SQL he threw out there to support someone's pastebin'd query (see the section preceded and followed by a blank line):


[22:18] RhodiumToad, so about that query...
[22:19] davidfetter: the position stuff is dubious
[22:20] RhodiumToad, well, i had tried with arrays, but it turns out that [1,2] <@ [2,1,3] is true :(
[22:20] consider what happens when comparing id 1 with id 10
[22:20] *** variable is now known as constant.
[22:20] oh
[22:22] the "right" (but possibly not fastest) way would be something like
[22:26] * davidfetter starting to wonder what it would be like


*** THIS PART BELOW ***

[22:26] (select coalesce(min(case when rank_id=p.id2 then rank end),4) < min(case when rank_id=p.id1 then rank end) from unnest(ranking) with ordinality as u(rank_id,rank))

*** THIS PART ABOVE ***

[22:27] actually,
[22:27] (select coalesce(min(rank) filter (where rank_id=p.id2),4) < min(rank) filter (where rank_id=p.id1) from unnest(ranking) with ordinality as u(rank_id,rank))
[22:28] er, > not <
[22:28] that's the WHERE clause?
[22:28] that would then return true if candidate id1 beat id2
[22:29] in place of your position stuff, yes - but putting a subquery in the filter of a cross join is major bad news for performance
[22:29] so I'd restructure the query a bit instead
[22:30] how?
[22:30] how many voters and candidates are we looking at here?
[22:31] tens of candidates, 1/2 million voters
[22:31] (ish)
[22:31] so the cross join is going to be on the order of half a billion or so rows
[22:32] yes
[22:32] um, wait. on the order of tens of millions, or did i do that math wrong?
[22:32] candidates * candidates * voters
[22:32] oh
[22:32] sorry
[22:33] 30 candidates = 1000 pairs, times half a million voters
[22:33] yeah, it's nP2
[22:33] for candidate pairs