Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjNy6-005Dmd-29 for pgsql-admin@arkaria.postgresql.org; Sun, 10 Mar 2024 18:32:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rjNy4-005kJJ-7x for pgsql-admin@arkaria.postgresql.org; Sun, 10 Mar 2024 18:32:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjNy3-005kJB-PS for pgsql-admin@lists.postgresql.org; Sun, 10 Mar 2024 18:32:16 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjNxz-003p6d-Nt for pgsql-admin@lists.postgresql.org; Sun, 10 Mar 2024 18:32:14 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 4A8A2380A44; Sun, 10 Mar 2024 19:32:10 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id sUzh1B2QqTaq; Sun, 10 Mar 2024 19:32:09 +0100 (CET) Received: from [192.168.178.86] (xdsl-78-35-58-153.nc.de [78.35.58.153]) by rs.plausibolo.de (Postfix) with ESMTPSA id AE43838065A; Sun, 10 Mar 2024 19:32:09 +0100 (CET) Message-ID: Date: Sun, 10 Mar 2024 19:32:08 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: Index Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------dkh6PvBAicpIIkGdLnpDoRxc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------dkh6PvBAicpIIkGdLnpDoRxc Content-Type: multipart/mixed; boundary="------------t4fhKI62xQAGIYgNpv7k0OxU"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: Index References: In-Reply-To: --------------t4fhKI62xQAGIYgNpv7k0OxU Content-Type: multipart/alternative; boundary="------------MsRQ2NKVO1tK2OmxxjXmcOXY" --------------MsRQ2NKVO1tK2OmxxjXmcOXY Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMTAuMDMuMjQgdW0gMTk6MjEgc2NocmllYiBSYWplc2ggS3VtYXI6DQo+IElmIHdlIGFy ZSBkZWxldGluZyByb3dzIG9mdGVuLi4uLi5JIGFtIHRhbGtpbmcgQUJUIHRoZSBwb3NzaWJp bGl0eSBvZiANCj4gaW5kZXggYmxvYXQuDQo+DQo+IE9uIFN1biwgMTAgTWFyIDIwMjQsIDIz OjQwIFJvbiBKb2huc29uLCA8cm9ubGpvaG5zb25qckBnbWFpbC5jb20+IHdyb3RlOg0KPg0K PiAgICAgV2hhdCBkb2VzICJpZiBpbmRleCBpcyBub3QgbWFuYWdlZCIgbWVhbj8NCj4NCj4g ICAgIE9uIFN1biwgTWFyIDEwLCAyMDI0IGF0IDk6MTHigK9BTSBSYWplc2ggS3VtYXINCj4g ICAgIDxyYWplc2hrdW1hci5kYmEwOUBnbWFpbC5jb20+IHdyb3RlOg0KPg0KPiAgICAgICAg IFdoYXQgd2lsbCBoYXBwZW4gaWYgaW5kZXggaXMgbm90IG1hbmFnZWQgaW4gdGhpcyBjYXNl Pw0KPg0KPiAgICAgICAgIE9uIFN1biwgMTAgTWFyIDIwMjQsIDE3OjIwIEhvbGdlciBKYWtv YnMsIDxob2xnZXJAamFrb2JzLmNvbT4NCj4gICAgICAgICB3cm90ZToNCj4NCj4gICAgICAg ICAgICAgQW0gMTAuMDMuMjQgdW0gMTI6MTkgc2NocmllYiBSYWplc2ggS3VtYXI6DQo+ICAg ICAgICAgICAgID4gSGkgLA0KPiAgICAgICAgICAgICA+DQo+ICAgICAgICAgICAgID4gSSBo YXZlIGEgdGFibGUgd2hlcmUgc2l6ZSBpcyBpbmNyZWFzaW5nIGRhaWx5LiBJbiB0aGF0DQo+ ICAgICAgICAgICAgIHRhYmxlIG9ubHkNCj4gICAgICAgICAgICAgPiBpbnNlcnRzIGFuZCBk ZWxldGVzIGhhcHBlbnMuIFRoaXMgdGFibGUgaXMgcmVmZXJlbmNpbmcNCj4gICAgICAgICAg ICAgMTUgb3RoZXINCj4gICAgICAgICAgICAgPiB0YWJsZXMuIFdlIGhhdmUgZ2l2ZW4gZGVs ZXRlIGNhc2NhZGUsIHNvIGlmIGEgcm93IGlzDQo+ICAgICAgICAgICAgIGRlbGV0ZWQgaW4g cGFyZW50DQo+ICAgICAgICAgICAgID4gdGFibGUswqAgdGhlIG1hdGNoaW5nIHJlY29yZHMg d2lsbCBhbHNvIGJlIGRlbGV0ZWQgaW4NCj4gICAgICAgICAgICAgdGhvc2UgY2hpbGQgdGFi bGVzLg0KPiAgICAgICAgICAgICA+DQo+ICAgICAgICAgICAgID4gSXQgaXMgdGFraW5nIGxv dCBvZiB0aW1lIHRvIGRlbGV0ZS4gU28sIEkgYW0gcGxhbm5pbmcgdG8NCj4gICAgICAgICAg ICAgY3JlYXRlIGluZGV4DQo+ICAgICAgICAgICAgID4gb24gdGhvc2UgY2hpbGQgdGFibGVz Lg0KPiAgICAgICAgICAgICA+DQo+ICAgICAgICAgICAgID4gTXkgcXVlc3Rpb24gaXMsIGFw YXJ0IGZyb20gaW5zZXJ0cywgSSBhbSBvbmx5IGdvaW5nIHRvDQo+ICAgICAgICAgICAgIGRl bGV0ZSByZWNvcmRzDQo+ICAgICAgICAgICAgID4gZnJvbSB0aG9zZSB0YWJsZXMuIEhvdyBp bmRleCBpbXByb3ZlcyBwZXJmb3JtYW5jZSBmb3INCj4gICAgICAgICAgICAgZGVsZXRpbmcN Cj4gICAgICAgICAgICAgPiByZWNvcmRzPyBIb3cgaW5kZXggaXMgYWZmZWN0ZWQgb3IgaG93 IHRvIGRvIGluZGV4DQo+ICAgICAgICAgICAgIG1hbmFnZW1lbnQgaW4gdGhpcyBjYXNlPw0K Pg0KPiAgICAgICAgICAgICBIaSwNCj4NCj4gICAgICAgICAgICAgU2luY2UgZXZlcnkgc2lu Z2xlIHJvdyB3aGljaCBpcyBnb2luZyB0byBiZSBkZWxldGVkIGluIHRoZQ0KPiAgICAgICAg ICAgICBjaGlsZCB0YWJsZXMNCj4gICAgICAgICAgICAgaGFzIHRvIGJlIGZvdW5kLCBhbiBp bmRleCBjYW4gYmUgb2YgZ3JlYXQgaGVscCBoZXJlLg0KPg0KPiAgICAgICAgICAgICBBY3R1 YWxseSwgaW4gbW9zdCAobm90IGFsbCEpIGNhc2VzIGFuIGluZGV4IG9uIGZvcmVpZ24ga2V5 DQo+ICAgICAgICAgICAgIGNvbHVtbnMgbWFrZXMNCj4gICAgICAgICAgICAgc2Vuc2UuDQo+ DQo+ICAgICAgICAgICAgIFJlZ2FyZHMsDQo+DQo+ICAgICAgICAgICAgIEhvbGdlcg0KPg0K PiAgICAgICAgICAgICAtLSANCj4gICAgICAgICAgICAgSG9sZ2VyIEpha29icywgQmVyZ2lz Y2ggR2xhZGJhY2gsIFRlbC4gKzQ5LTE3OC05NzU5MDEyDQo+DQpBcmUgeW91ciBkZWxldGlu ZyBhcmJpdHJhcnkgcm93cyAtIG9yIGFyZSB0aGUgZGF0YSB0aW1lc2VyaWVzLCB3aGVyZSB5 b3UgDQpkZWxldGUgYWxsIGRhdGEgb2Ygd2Vla3Mgb3IgbW9udGhzPw0KDQpJZiBzbywgdGlt ZS1wYXJ0aXRpb24geW91ciB0YWJsZXMgYW5kIGRyb3AgcGFydGl0aW9ucyB3aGVuIG5lY2Vz c2FyeS4NCg0KSWYgbm90LCB0dW5lIGF1dG92YWN1dW0gb3IgdmFjdXVtIGZ1bGwgeW91ciB0 YWJsZXMgYW5kIGluZGV4ZXMgZXZlcnkgbm93IA0KYW5kIHRoZW4uIE9yIHJlLWluZGV4IGNv bmN1cnJlbnRseSBzb21ldGltZXMuDQoNCi0tIA0KDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNj aCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkwMTINCg0KY2hhdCB3aXRoIG1lIG9uIFNp Z25hbCBtZXNzZW5nZXIgDQo8aHR0cHM6Ly9zaWduYWwubWUvI2V1L0dwSVAyNTliQm5lX0pp QllKRnZiWkNMWUpFTGhCLTkybkpmbmNBQ0wxTFVlQk9waGZYRkZnTFlZVlh2X0FHaGc+DQoN Cg== --------------MsRQ2NKVO1tK2OmxxjXmcOXY Content-Type: multipart/related; boundary="------------8q1pdZrsLvPoiQl16XqDbrau" --------------8q1pdZrsLvPoiQl16XqDbrau Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 10.03.24 um 19:21 schrieb Rajesh Kumar:
If we are deleting rows often.....I am talking AB= T the possibility of index bloat.=C2=A0

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

On Sun, Mar 10, 2024 = at 9:11=E2=80=AFAM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
What will happen if index is not manage= d in this case?=C2=A0

On Sun, 10 Mar 20= 24, 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,=C2=A0 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.<= br>
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
3D""
chat with me on Signal messenger

--------------8q1pdZrsLvPoiQl16XqDbrau Content-Type: image/png; name="yN6KW349IqGU3o59.png" Content-Disposition: inline; filename="yN6KW349IqGU3o59.png" Content-Id: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIcAAACHAQMAAAAGKdhJAAAABlBMVEUAAAD///+l2Z/dAAAA AnRSTlP//8i138cAAAAJcEhZcwAACxIAAAsSAdLdfvwAAAG5SURBVEiJ3da7bcMwEAbgM1iw kxYQwDXYcSVpAT0WkFdixzUIcAGqY0Hw8suJHSCNzkCKIIKg4isI3tMm/vnQP5FMtIQ4lbr4 SmSEcnCdHa/W7FQXFosfcEjvK76jfkNmp+6Bm35XqNOxs+8II8ahsxEXeEVxKcjPFIbP95Wx S8FzFN5dJP1dnUvJmm5sVocviiIV5pStyna4FcRrhJItTQUpqrMeehaLVk3TaIlcHZ1Umq2o wsQ0OvrMqkQOhEn1Fs5I+yCVRnEJidHSIZKVChdeiXer2nkTFko7a2cOTluJX1EIJGtzLwqz s7q6BKngnNtjEJp+RiGQrNN61kLt1qDZhMIlzrb2xdwx5l/5uZaMWmOHWLOFODupsI/kUnao hWpWKkdJ94L+xM2xTFgojQY0f3Np47gUIxT2iYvKp6RdS+Xw1PthdI9ZIKk0m45AHSHM550F chSaPGqhDsYgSAVPxuwU9BjqboRy7jpO6OfshtFKBXt1tKh+ajiHxXImxzCrHeuuvCEzqc2r VfP22JBCwa7DT+GIIj7PuRY+V1ZHmAU0AAvlzA9qwQYLeXpl9Up+61/B35IPLEvZkYbCln8A AAAASUVORK5CYII= --------------8q1pdZrsLvPoiQl16XqDbrau-- --------------MsRQ2NKVO1tK2OmxxjXmcOXY-- --------------t4fhKI62xQAGIYgNpv7k0OxU-- --------------dkh6PvBAicpIIkGdLnpDoRxc Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZe38qAUDAAAAAAAKCRCKV5yTsxlhuvzT AJ4tZy9nETfFMDfuNb8ZbSqepwhBFACgmgwRigEwYgTE4VK/B60gZ0cvGm0= =+2JH -----END PGP SIGNATURE----- --------------dkh6PvBAicpIIkGdLnpDoRxc--