Re: Does PostgreSQL ever create indexes on its own?

Thanks for all your quick replies!

Turns out these are created by pg_repack and left in the database if
pg_repack encounters a fatal error.

In this case, the error was a hyphen in the database name which pg_repack
can¹t handle.

On 11/12/15, 5:38 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
>> Doiron, Daniel schrieb am 12.11.2015 um 23:21:
>>> I¹m 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
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general