From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: int id's helpful for indexing, or just use text names?
Date: Tue, 15 Oct 2002 15:16:47 -0400
Message-ID: <20021015151647.65521b8d.gry@ll.mit.edu> (raw)
[linux, postgresql 7.2, 500MHz * 4 xeon cpu's, 1GB ram, hardware raid]
My current db has serveral instances of something like:
table foos(fooid int2, fooname text, foouser text, foobar int2 references bars(barid))
table bars(barid int2, barname text, barcolor text, primary key(barid) )
etc, where foonames and barnames are known to be, say <20 characters long.
And the fooid's and barid's are arbitrary ints only known inside the db.
The original reason for these numeric id's, (in another db system long long ago),
was to conserve space(now irrelvant with 120G disks) and to make searching and
index usage more efficient. Recently, there is increasing call for new apps and
even ad-hoc queries.(Thank goodness people are finally interested in this data!)
The artificial numeric id's make it a lot harder for naive users to understand
the data structure, and sometimes actually requires an extra order of joins.
The question is: would I be better off losing all those integer ids and just using
the text names as primary indices? Is there much performance lost comparing
text strings for every index operation?
My db is not huge: longest table has 100k tuples, biggest table has 1k pages, total
pg_dump output is 51 Mbytes. Typical activity: ~6000 updates and inserts/day,
30,000 selects/day.
--
I cannot think why the whole bed of the ocean is
not one solid mass of oysters, so prolific they seem. Ah,
I am wandering! Strange how the brain controls the brain!
-- Sherlock Holmes in "The Dying Detective"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gry@ll.mit.edu
Subject: Re: int id's helpful for indexing, or just use text names?
In-Reply-To: <20021015151647.65521b8d.gry@ll.mit.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox