Re: Index and primary key

Gaetan GUYODO wrote:
>
> Hi,
> I have one question :
> I have a table, with 12 fields, and 70000 records (not more, that's a
> thesaurus)
> I will have to make a "select" only on one or two fields.
> I have no index on this table.
> And primary key is a field containing a five digit number (unique, not null),
> which is an important information of the record (I made this field "primary
> key" because I'm sure that it will be unique).
>
> Do you think this conception is a good one ?
> Maybe, I have to use one of the fields on which I will make a select as
> primary key ?
> Maybe I have to add an index field ? (if I understand, that's better-faster
> to extract a record, but isn't my "primary key" 5 digit code similar ?

If you intend to get one, or few, records back as a result of your select, you
should probably have an index on the most selective value you can that will be used
in the 'where' clause.

Given the (apparently) low frequency of updates, I would tend to say index as many
fields as you like.  Indexes are a performance negative if you are doing lots of
inserts and updates, although if you add _lots_ of indexes no doubt they impact on
the planning time to some degree.

If you intend to process the whole table at each select, then an index will offer
little value, and PostgreSQL will usually ignore it in any case.

Once you have populated your table, added your indexes and run a "VACUUM ANALYZE"
then PostgreSQL will make reasonable attempts to plan your query to use the most
efficient mix of index access vs scan access using the statistics it got from the
analysis.

Regards,
                    Andrew.
--
_____________________________________________________________________
        Andrew McMillan, e-mail: Andrew @ catalyst . net . nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709