Re: String versus integer joins?

It's going to depend on a number of factors. There's a whole section of "PostgreSQL 9 High Performance" on the various aspects of join performance. Well worth owning, if you're wanting to go this far into the weeds.

d.

On Oct 23, 2012, at 12:12 PM, Mark Wong <markwkm(at)gmail(dot)com> wrote:

> 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
>
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug