Re: Using indexes and keys - Mailing list pgsql-novice

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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco