> Why do I have to tell the DBMS which fields to index?
>
> The DBMS can manage this by monitoring the accesses to attributes,

maintain

> this information and build an index itself.

Quite true, but you don't there are a few things to consider. First, you
don't want this to happen when the database is on production; if you already
know how you will access your database, it's ok to tell the database in
advance - rather than having the database figure it out by itself after 500
slow queries.

Second, telling the database you don't need to keep the indexes up-to-date
when importing a lot of data (by deactivating them, which is something I
usually do), is another thing which is only useful if known *before* the
actual operations happen - I wouldn't want the database to maintain them
while I import data, then deactivate them after the data was imported
because if figured it was expensive to maintain and I wasn't using them
anyway.

Third, UNIQUE indexes also enforce a constraint on the database. Ok, ok, so
you can specify this with a check, but it's more simple to do this with a
unique index. Besides, this gives the optimizer a piece of information about
the selectivity of the index which would be too expensive to find out on the
fly (exactly how many records can have a given value on a given field).

However, I do think that a utility to examine a given workload and suggest
indexes would be a useful thing to have in my toolkit.

Marcelo Lopez Ruiz