Обсуждение: Does PostgreSQL ever create indexes on its own?

On 11/12/2015 02:21 PM, Doiron, Daniel wrote:
> 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)
>
> My questions is whether these “index_*” indexes could have been created
> by postgresql or whether I have an errant developer using some kinda
> third-party tool?

This is definitely a third party tool. The only time an index will be
implicitly created is:

1. You set a column as the PRIMARY KEY
2. You set a column UNIQUE

Lastly, postgresql would never use such a ridiculous naming scheme.

JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
New rule for social situations: "If you think to yourself not even
JD would say this..." Stop and shut your mouth. It's going to be bad.

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)
>
> My questions is whether these “index_*” indexes could have been created by postgresql or whether I have an errant
developerusing some kinda third-party tool? 

The only index that Postgres "automatically" creates is the unique index supporting a primary key or a unique
constraint.

But apart from that, Postgres never creates indexes on its own.

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

Thomas Kellerer wrote:
> 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)

> The only index that Postgres "automatically" creates is the unique index supporting a primary key or a unique
constraint.
>
> But apart from that, Postgres never creates indexes on its own.
>
> So from the list above, only pk_patient_diagnose has (most probably) been created automatically. Everything else was
createdmanually. 

As I recall, the naming convention is to append "_pkey", not to prepend
"pk_", so not even that one.  (Of course, you can tell it what name to
use when creating the constraint, which is what was done here.)

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Thomas Kellerer <spam_eater@gmx.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
createdmanually. 

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

Doiron, Daniel wrote:
> 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)
> 
> My questions is whether these “index_*” indexes could have been created by postgresql or whether I
> have an errant developer using some kinda third-party tool?

These indexes were *not* created by PostgreSQL.
We are not Oracle.

Yours,
Laurenz Albe

Albe Laurenz schrieb am 13.11.2015 um 11:23:
>> My questions is whether these “index_*” indexes could have been created by postgresql or whether I
>> have an errant developer using some kinda third-party tool?
>
> These indexes were *not* created by PostgreSQL.
> We are not Oracle.

Well, Oracle does not create indexes on its own either - it has the same strategy as Postgres:
Indexes are only created automatically for primary keys and unique constraints.

Thomas

On 13/11/15 10:49, Thomas Kellerer wrote:
>> These indexes were *not* created by PostgreSQL.
>> We are not Oracle.
>
> Well, Oracle does not create indexes on its own either - it has the same strategy as Postgres:
> Indexes are only created automatically for primary keys and unique constraints.

Given that indices are an implementation wart on the side of the
relational model, it'd be nice if RDBMS' did create them for one.
--
Cheers,
  Jeremy

Jeremy Harris wrote:
> On 13/11/15 10:49, Thomas Kellerer wrote:
>>> These indexes were *not* created by PostgreSQL.
>>> We are not Oracle.
>>
>> Well, Oracle does not create indexes on its own either - it has the same strategy as Postgres:
>> Indexes are only created automatically for primary keys and unique constraints.

I know - but I couldn't help commenting on the strange names
it chooses for these, like "SYS43243247".
Sorry for being unclear.

> Given that indices are an implementation wart on the side of the
> relational model, it'd be nice if RDBMS' did create them for one.

That cannot be done without knowing what the queries are going to be.

However, I recently learned that MySQL automatically creates indexes
on columns with a foreign key, and you cannot even drop those.

Maybe that would be a good thing, guessing from the number of cases
where people suffer from the lack of such indexes, but on the other
hand it feels like too much DWIM (there are cases where you do not
need such an index).

Yours,
Laurenz Albe

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@sss.pgh.pa.us> wrote:

>Thomas Kellerer <spam_eater@gmx.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@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general