Re: String versus integer joins?

On Tue, Oct 23, 2012 at 9:40 AM, Veronika Megler <vmegler(at)cecs(dot)pdx(dot)edu> wrote:
> Hello all,
>
> I've heard persistent rumors that joining two table based on an integer id
> "must be" or "is" faster than joining on a string id.
>
> While I'd believe the "must be", does anyone have any pointers to (or
> evidence of) how much faster?
>
> I.e.; if the difference is 10 times, then it's worth us
> rewriting/testing/upgrading an application. If it's 10%, then it's not.

I have wondered how toasting affects things for the columns used in a
join if they were strings, specifically if there are actually 2x reads
done because of toasting varchars. But I believe as of 9.0 there was
work done so that varchars are not always toasted if the length is
restricted such that the table row fits in a page. It's been a long
time for me though, I might be spewing out nonsense that makes this
more confusing. If what I said is valid, I would assume the
performance would degrade significantly only if the dataset was large.

Regards,
Mark