1. Does an indexed column on a table have to be a potential primary
key?
Nope, create as many index as you need/must/should.
I've been working with a couple of rather large tables where a common
select is on a foreign key called 'cntrct_id' (Varchar(9) in format).
However, the same 'cntrct_id' can appear on multiple records in the
tables I'm trying to work with now; the tables themselves record events
associated with the given 'cntrct_id' record and can store many events
for one 'cntrct_id' value. I'd thought that creating an index on the
table.cntrct_id field for the event tables would allow me to speed up
the transations some, but comparisons of time before and after the
indexing lead me to wonder if I was mistaken in this. The times were
almost identical in the following areas: Before Indexing, after Indexing
but before Analyzing, and after Analyzing.
2. Another common sort on these fields uses part, not all, of the
'cntrct_id' value to search for things; the first character marks
original location in an internal framework we're using, for example, and
the third character marks the month of the year that the original
'cntrct_id' record was set up. Sorts on either of those are fairly
common as well; would indexing on the cntrct_id as a whole be able to
speed up a sort on a portion of it?
Nope.
This looks like suboptimal schema design...
If you had an indexed date column, you would be able to make fast indexed
queries with BETWEEN, >=, <=, etc.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
