From the page you linked
The following fields are indexed by default: primary keys (Id, Name
and Owner fields), foreign keys (lookup or master-detail relationship
fields), audit dates (such as LastModifiedDate), and custom fields
marked as External ID or Unique.
That line answers both the "by default" and "under what conditions custom fields" aspects of your question. That's it.
Some additional information about which fields are automatically indexed and exceptions for those which cannot be indexed is available in the Best Practices for Deployments with Large Data Volumes (PDF) architecture documentation. (In addition to a ton of other great stuff.)
The platform automatically maintains indexes on the following fields for most objects.
- Systemmodstamp (LastModifiedDate)
- Email (for contacts and leads)
- Foreign key relationships (lookups and master-detail)
- The unique Salesforce record ID, which is the primary key for each object
Salesforce also supports custom indexes on custom fields, with the exception of multi-select picklists, text area (long), text area (rich), non-deterministic formula fields, and encrypted text fields.
External IDs cause an index to be created on that field, which is then considered by the Force.com query optimizer.
External IDs can be created on only the following fields.
- Auto Number
To create custom indexes for other field types, including standard
fields, contact salesforce.com Customer Support