Re: Using indexes and keys

John,

> I'm a bit confused about the use of indexes with postgresql.
> I've a number of questions that are probably obvious when you know
> the answer, but I can't find in the documentation.

Actually, I like your questions. All of the below are obvious to
veteran DB programmers, but not to newbies. I'll put together a FAQ
based on your questions for Techdocs. Few of the answers below are
particular to Postgres; you will find them similar across all SQL
RDBMSs.

> 1) It seems that I can only have primary keys in postgresql,
> does this mean that keys are not the same as indexes ?

Correct, they are not the same. A "key" is a unique set of data that
allows you to uniquely identify a row. One of these Keys, chosen by
you, becomes a "Primary Key" by which the database system finds the
row for which you're looking. The other Keys, if any, are "candidate
keys." You may wish to put Constraints on these Keys to enforce their
uniqueness. Example:

Create Table staff (
staff_id SERIAL NOT NULL PRIMARY KEY,
staff_name VARCHAR (100) NOT NULL,
SSN VARCHAR (9) NOT NULL,
department VARCHAR (25) NOT NULL,
hire_date TIMESTAMP NOT NULL,
comments VARCHAR(200) NULL,
CONSTRAINT cs_SSN_unq UNIQUE(SSN),
CONSTRAINT cs_staff_combo_unq UNIQUE (staff_name, department,
hire_date)
);

In the table above, the integer index value staff_id is my Primary Key.
The SSN is a Candidate Key which I have chosen not to make my Primary
Key. Because of the uniqueness constraint, the combination
ofstaff_name, department, and hire_date is also a Candidate Key.

If I have two natural Candidate Keys, why did I choose to use a SERIAL
integer ID value? Convenience. SERIAL ID's are the easiest things to
work with as Primary Keys in most cases. Because the ID has no
purpose outside of providing a Primary Key, you don't have to worry
about it changing for an existing record, or getting re-used.

> 2) If I define a column as a primary key, does that mean that it will
> be
> indexed, so I don't nee to create an index as well ?

In Postgres and most other RDBMSs, yes. When you declare a Primary
Key, Postgres will tell you that it is creating both an index and a
uniqueness constraint on that column. While neither the index nor the
constraint are "part of" the key, Postgres (like most RDBMSs) needs
them to enforce the primary key.

> 2a) If so - If I define two or more columns as the primary key, will
> they
> be indexed seperately, or do I need to index the 2nd and 3rd
> columns
> seperately

They will be indexed together. So, if you need to do lookups on the
3rd column seperate from the 1st, you will need to build a seperate
index for that column alone.

CAUTION: From my 7 years of RDBMS programming experience, multi-column
primary keys are a huge headache and you will soon find yourself
re-building the table with an independant SERIAL primary key. If you
want to know why, just try doing a LEFT OUTER JOIN on two tables with
3-column primary keys. Bleah!

> 3) If I create an index on a column (it is not unique and so can't be
> a key),
> what do I need to query on - the index name or the column name ?

The column name. Your request (query) goes through a Query Planner,
which decides with good accuracy what the fastest way to find your
data is. Sometimes it uses your index, sometimes not, depending on
what you queried (indexes are not always faster). All you have to do
is run ANALYZE on your database periodically so that the Query Planner
has accurate current information to base its decisions on.

You would benefit from reading a few general RDBMS books. See the book
review page: http://techdocs.postgresql.org/bookreviews.php

Finally, the new version, 7.2, keeps statistics on the use your indexes
are seeing, which over time will help you improve your database by
dropping indexes that are not used and adding the ones you need.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco