Обсуждение: Index

Поиск
Список
Период
Сортировка

Index

От
Rajesh Kumar
Дата:
Hi ,

I have a table where size is increasing daily. In that table only inserts and deletes happens. This table is referencing 15 other tables. We have given delete cascade, so if a row is deleted in parent table,  the matching records will also be deleted in those child tables. 

It is taking lot of time to delete. So, I am planning to create index on those child tables. 

My question is, apart from inserts, I am only going to delete records from those tables. How index improves performance for deleting records? How index is affected or how to do index management in this case?

Re: Index

От
Laurenz Albe
Дата:
On Sun, 2024-03-10 at 16:49 +0530, Rajesh Kumar wrote:
> I have a table where size is increasing daily. In that table only inserts and
> deletes happens. This table is referencing 15 other tables. We have given
> delete cascade, so if a row is deleted in parent table,  the matching records
> will also be deleted in those child tables. 
>
> It is taking lot of time to delete. So, I am planning to create index on those child tables. 
>
> My question is, apart from inserts, I am only going to delete records from those
> tables. How index improves performance for deleting records? How index is affected
> or how to do index management in this case?

https://www.cybertec-postgresql.com/en/index-your-foreign-key/

Yours,
Laurenz Albe



Re: Index

От
Holger Jakobs
Дата:
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only 
> inserts and deletes happens. This table is referencing 15 other 
> tables. We have given delete cascade, so if a row is deleted in parent 
> table,  the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index 
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records 
> from those tables. How index improves performance for deleting 
> records? How index is affected or how to do index management in this case?

Hi,

Since every single row which is going to be deleted in the child tables 
has to be found, an index can be of great help here.

Actually, in most (not all!) cases an index on foreign key columns makes 
sense.

Regards,

Holger

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


Вложения

Re: Index

От
Rajesh Kumar
Дата:
What will happen if index is not managed in this case? 

On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only
> inserts and deletes happens. This table is referencing 15 other
> tables. We have given delete cascade, so if a row is deleted in parent
> table,  the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records
> from those tables. How index improves performance for deleting
> records? How index is affected or how to do index management in this case?

Hi,

Since every single row which is going to be deleted in the child tables
has to be found, an index can be of great help here.

Actually, in most (not all!) cases an index on foreign key columns makes
sense.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Index

От
Ron Johnson
Дата:
What does "if index is not managed" mean?

On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
What will happen if index is not managed in this case? 

On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only
> inserts and deletes happens. This table is referencing 15 other
> tables. We have given delete cascade, so if a row is deleted in parent
> table,  the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records
> from those tables. How index improves performance for deleting
> records? How index is affected or how to do index management in this case?

Hi,

Since every single row which is going to be deleted in the child tables
has to be found, an index can be of great help here.

Actually, in most (not all!) cases an index on foreign key columns makes
sense.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Index

От
Rajesh Kumar
Дата:
If we are deleting rows often.....I am talking ABT the possibility of index bloat. 

On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
What does "if index is not managed" mean?

On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
What will happen if index is not managed in this case? 

On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only
> inserts and deletes happens. This table is referencing 15 other
> tables. We have given delete cascade, so if a row is deleted in parent
> table,  the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records
> from those tables. How index improves performance for deleting
> records? How index is affected or how to do index management in this case?

Hi,

Since every single row which is going to be deleted in the child tables
has to be found, an index can be of great help here.

Actually, in most (not all!) cases an index on foreign key columns makes
sense.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Index

От
Ron Johnson
Дата:
Tune your autovacuum settings.

On Sun, Mar 10, 2024 at 2:21 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
If we are deleting rows often.....I am talking ABT the possibility of index bloat. 

On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
What does "if index is not managed" mean?

On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
What will happen if index is not managed in this case? 

On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only
> inserts and deletes happens. This table is referencing 15 other
> tables. We have given delete cascade, so if a row is deleted in parent
> table,  the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records
> from those tables. How index improves performance for deleting
> records? How index is affected or how to do index management in this case?

Hi,

Since every single row which is going to be deleted in the child tables
has to be found, an index can be of great help here.

Actually, in most (not all!) cases an index on foreign key columns makes
sense.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Index

От
Holger Jakobs
Дата:
Am 10.03.24 um 19:21 schrieb Rajesh Kumar:
If we are deleting rows often.....I am talking ABT the possibility of index bloat. 

On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
What does "if index is not managed" mean?

On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
What will happen if index is not managed in this case? 

On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only
> inserts and deletes happens. This table is referencing 15 other
> tables. We have given delete cascade, so if a row is deleted in parent
> table,  the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records
> from those tables. How index improves performance for deleting
> records? How index is affected or how to do index management in this case?

Hi,

Since every single row which is going to be deleted in the child tables
has to be found, an index can be of great help here.

Actually, in most (not all!) cases an index on foreign key columns makes
sense.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Are your deleting arbitrary rows - or are the data timeseries, where you delete all data of weeks or months?

If so, time-partition your tables and drop partitions when necessary.

If not, tune autovacuum or vacuum full your tables and indexes every now and then. Or re-index concurrently sometimes.

--

Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

chat with me on Signal messenger

Вложения