Re: Does PostgreSQL ever create indexes on its own?

Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> Doiron, Daniel schrieb am 12.11.2015 um 23:21:
>> Im troubleshooting a schema and found this:
>>
>> Indexes:
>> "pk_patient_diagnoses" PRIMARY KEY, btree (id)
>> "index_4341548" UNIQUE, btree (id)
>> "idx_patient_diagnoses_deleted" btree (deleted)
>> "idx_patient_diagnoses_diagnosis_type_id" btree (diagnosis_type_id)
>> "idx_patient_diagnoses_icd10" btree (icd10)
>> "idx_patient_diagnoses_icd9" btree (diagnosis_code)
>> "idx_patient_diagnoses_is_unknown" btree (is_unknown)
>> "idx_patient_diagnoses_modified" btree (modified)
>> "idx_patient_diagnoses_patient_id" btree (patient_id)
>> "idx_patient_diagnoses_uuid" btree (uuid)
>> "index_325532921" btree (modified)
>> "index_4345603" btree (deleted)
>> "index_4349516" btree (diagnosis_type_id)
>> "index_4353417" btree (icd10)
>> "index_4384754" btree (diagnosis_code)
>> "index_4418849" btree (is_unknown)
>> "index_4424101" btree (patient_id)
>> "index_4428458" btree (uuid)

> So from the list above, only pk_patient_diagnose has (most probably) been created automatically. Everything else was created manually.

Also, *none* of those index names match what Postgres would choose of its
own accord. The built-in naming schemes can be exhibited thus:

regression=# create table foo (f1 int primary key, f2 int unique, f3 int);
CREATE TABLE
regression=# create index on foo(f3);
CREATE INDEX
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
f2 | integer |
f3 | integer |
Indexes:
"foo_pkey" PRIMARY KEY, btree (f1)
"foo_f2_key" UNIQUE CONSTRAINT, btree (f2)
"foo_f3_idx" btree (f3)

There's some additional rules for abbreviating very long derived index
names, and for dealing with index name collisions, but none of those would
have come into play here. The index names Daniel shows must all have been
specified in DDL commands, either as the name of a constraint or as the
name of an index.

regards, tom lane