Re: String versus integer joins?

Actually, right now I'm trying to avoid the weeds, and all the other
factors except this one (since I have other sources for those other
factors).

Back in the distant past, on a non-PostgreSQL high-performance
database, there used to be very simple guidance: "don't use
character-based fields as a unique tuple identifier, because
performance will suck. Use integer identifiers."

But the years have passed, and I keep hearing vague statements that
things are much better now, and maybe it isn't true these days, and
anyway, that wasn't PostgreSQL; but then maybe it might make a
difference after all.

What I was hoping for was some more definitive statement or someone's
actual experience.

If there's a statement on this specific topic in the High Performance
section I'd be very interested to hear what it is,
Veronika

On Tuesday, October 23, 2012 12:56:57 PM, Darrell Fuhriman wrote:
> 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
>
>