On 4/6/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Jonathan Ellis" <jonathan@utahpython.org> writes:
> > On 4/6/07, Tom Lane <tgl@sss.pgh.pa.us> 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?