Re: indexes - Mailing list pgsql-general

Yes, it does. So of course it depends on how you use it to know
what's going to be more efficient. For instance, if the rows in this
table contain strings of more than a few bytes, and more than a
couple tables reference this table with a foreign key, then you will
quickly start to save space by using a numeric primary key, even if
it is an artificial construct.

For the kind of work I find myself doing, it's rare that it would be
more efficient to not have the artificial construct. But that doesn't
mean one is always better than the other.

On Nov 24, 2006, at 11:14 AM, Ron Johnson wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> But that requires that you haul an artificial construct around.
>
> On 11/24/06 12:38, Ben wrote:
>> It depends how it's going to be used. If you are going to
>> reference this
>> table in other tables a lot and/or rarely care about what the name
>> actually is, then the two-column approach is going to be more
>> efficient.
>> Numbers are smaller and easier to compare than strings.
>>
>> On Nov 24, 2006, at 6:54 AM, Tom Allison wrote:
>>
>>> I notice a lot of places where people use the approach of
>>> creating an
>>> index and a unique key like:
>>>
>>> CREATE TABLE foo (
>>>   idx SERIAL PRIMARY KEY,
>>>   name varchar(32) UNIQUE NOT NULL
>>> )
>>>
>>> instead of
>>> CREATE TABLE foo (
>>>   name varchar(32) PRIMARY KEY
>>> )
>>>
>>> If the name is NEVER going to change, is there any advantage to
>>> doing
>>> this?
>>> If there are many-to-many reference tables (like name-to-friends) is
>>> this any different?
>>>
>>> I've seen this a lot, but I've always assumed that with the
>>> condition
>>> that 'name' would NEVER change, there was no advantage.
>
>
> - --
> Ron Johnson, Jr.
> Jefferson LA  USA
>
> Is "common sense" really valid?
> For example, it is "common sense" to white-power racists that
> whites are superior to blacks, and that those with brown skins
> are mud people.
> However, that "common sense" is obviously wrong.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
>
> iD8DBQFFZ0SIS9HxQb37XmcRAppYAJ9i5PpJ021FyQYQSgTo9Alv8CDNHgCg1Q4p
> nMmJ64MHVNfE91EZIsJNwts=
> =piIg
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly