On 4/6/07, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"Jonathan Ellis" <jonathan@xxxxxxxxxxxxxx> writes: > On 4/6/07, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> Yeah, it sure is the same plan, and 8.2 seems to be a tad faster right >> up to the hash join on user_id. Is user_id a textual datatype? > user_id is an int; they are both C locale. Really!? So much for that theory.
Yeah, this db goes back to 7.0 so I've been careful to keep the locale set to C to avoid surprises.
Is work_mem set similarly on both installations?
work_mem is 8MB on 8.2; work_mem is 1MB and sort_mem is 8MB on 8.1. (there's no disk io going on with the 8.2 installation either, so it's not swapping or anything like that.)
The only other thing I can think is that you've exposed some unfortunate corner case in the hash join logic. Would you be willing to send me (off-list) the lists of user_ids being joined? That would be the clan_members.user_id column and the user_id column from the join of parties and clan_participants.
I can do that... you don't think the fact I mentioned, that redefining the view to leave out the expensive function fixes the problem, is relevant?
