Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Thomas Kellerer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Jeremy Harris]

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




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Albe Laurenz]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Albe Laurenz]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Doiron, Daniel]

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"  wrote:

>Thomas Kellerer  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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Guillaume Lelarge]

2015-11-12 23:21 GMT+01:00 Doiron, Daniel :

> 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?
>

PostgreSQL doesn't create indexes on its own, except for primary keys,
unique constraints, and exclusion constraints.

So, that must be something (or someone) else.


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Thomas Kellerer]

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 developer using 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 created manually.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Tom Lane]

Thomas Kellerer  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

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Alvaro Herrera]

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 created manually.

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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

[Joshua D. Drake]

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.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general