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 1tLMmV-00EMFg-Pu for pgsql-performance@arkaria.postgresql.org; Wed, 11 Dec 2024 13:29:36 +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 1tLMmT-00GOhh-2y for pgsql-performance@arkaria.postgresql.org; Wed, 11 Dec 2024 13:29:34 +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 1tLMmS-00GOhZ-EY for pgsql-performance@lists.postgresql.org; Wed, 11 Dec 2024 13:29:33 +0000 Received: from mail-db8eur05on2070b.outbound.protection.outlook.com ([2a01:111:f403:2614::70b] helo=EUR05-DB8-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tLMmO-002GWJ-MG for pgsql-performance@lists.postgresql.org; Wed, 11 Dec 2024 13:29:31 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=BbPKxZvk9sEGE7ILRwdPT7BP+A5boAZgLqJLGP704/rvG+XpKnFw1VD7gJjIlLHcTyp/fo4JfZUMsCqK7LgeTAMhMpm0rBsqmUxNYmMUNH0WxiswuHFfyMgGKvvTmuE+h+cyEEesCWKWM1+GV9/pbd49UxAplYQcTAres1LZFHtQf1aULfUAqu2tEHWd1Frzt7L4i+zAYszECF7ZwvIXLGuwjn/HF6buTtO2mI9OKvVq0SqO2cr/xvZDe78hGKLoe1+gqHRGxeFmIguhCYphTJOOOt8V7mSjsj0mp4dqleDsDS13YQ2Kt98MDDKGrulGjwxs9oBVC3kYN2NgK2ginA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=hIFWf2SxTtVO5w103yQWZeEbE0YmsJIFSX1011Otwkg=; b=yyCLh1C9Fj/YI/3TxI2bG+SxhJBCrecSdpItfvq0+Z1D/aq4S0reQTDvVWIKe6lu4N/KB0RYuMpU8CssT2mdhD96723uK3kYbtG50K0MqrjdNuG8HMY1w7pZvZWePF35HqsTa5K3fDbHkBlvRNaIkuEmKlG1w9tj4PXv5WctXuViKXHLATGxC8U1zLOSpQfgkIRQL04OKrhHQ+hGB+rydWAu3AcZRvTY4cmDt2MBcyrKvXRZBhKCLv6+Dvx9PsqoGwSvIIwgjTcCTPwzy3euyBvG8TqSRl7LFAqtSJ1ghjEyuAo4XmbfhvzjFq+Eo306nJNEAOpcfxWaDePFMi/8CA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=nibio.no; dmarc=pass action=none header.from=nibio.no; dkim=pass header.d=nibio.no; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=nibio.onmicrosoft.com; s=selector2-nibio-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=hIFWf2SxTtVO5w103yQWZeEbE0YmsJIFSX1011Otwkg=; b=BAV82Co2BkOCBN3spH5h89ofJODO4F57br6r15YM56AaZk4jZ2iHsnl0Mvmd/03DXag8xUvTsI84i3jga6+eREfdzTD9CTtF3vCp8i+tztH2sD/egZkSCO410R5F5aTfQDDbd0nWeXKKrU7EZ7qvUQY9pCXKZp+wJLPrwSNwCMo= Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:174::18) by DB8P189MB1141.EURP189.PROD.OUTLOOK.COM (2603:10a6:10:163::15) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8251.14; Wed, 11 Dec 2024 13:29:23 +0000 Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM ([fe80::7021:938f:7ff2:6406]) by AM7P189MB1028.EURP189.PROD.OUTLOOK.COM ([fe80::7021:938f:7ff2:6406%6]) with mapi id 15.20.8251.008; Wed, 11 Dec 2024 13:29:23 +0000 From: Lars Aksel Opsahl To: Greg Sabino Mullane CC: Tom Lane , Christophe Pettus , "pgsql-performance@lists.postgresql.org" Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Thread-Topic: PostgreSQL and a Catch-22 Issue related to dead rows Thread-Index: AQHbSiR8fAE8+rgvhkWYwOHzJhBte7Ld+jSAgAAaboCAARHFqIAATR8AgAAAn+WAACjFgIABWxEe Date: Wed, 11 Dec 2024 13:29:23 +0000 Message-ID: References: <5EE4CC0C-E624-4E82-A2E8-B2EBA44D3521@thebuild.com> <3908773.1733760455@sss.pgh.pa.us> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=nibio.no; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: AM7P189MB1028:EE_|DB8P189MB1141:EE_ x-ms-office365-filtering-correlation-id: cba7bb43-6921-417f-55e3-08dd19e7d39e x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|41320700013|366016|1800799024|376014|8096899003|38070700018; x-microsoft-antispam-message-info: =?iso-8859-1?Q?mxwoRnN/W/Ttw/P/1I8HrTCGwI2Ug0vXFAdv64RxuppLYhs7/Ff8HuJYvu?= =?iso-8859-1?Q?9ccdxdpckwgLMULa6EBHM+S8DgaFPhb5GAFjVe6Z9+eTujJH99H64XZj0u?= =?iso-8859-1?Q?H+tKUT5kg92s8eEG+Cey5y8CYGBx/ZdbtU5fDK2Pw2pvZ9oT3EftmXGyVE?= =?iso-8859-1?Q?BMXfBrAN8Kw6dLa7S4km7680XQ9S4ouEw010/aGKVQJa8KNJXUlaBMBzZk?= =?iso-8859-1?Q?KsKr0r8gZZmJeRsG/Qn6MEVLKJbTjWKNsKU0Ox+5A/U41zLDV+2QYz690+?= =?iso-8859-1?Q?y3oRx7o7Hg3DCszcamR1RU3Dh7cSIJ3fOIjOsqRe4kG+k0Vv2uMi9Iv0py?= =?iso-8859-1?Q?gzLSNhuYDdaPyGVCvSOpFrIzjI46N0F1+ZN+CmLP45LrIwtHSP+OxDiYPg?= =?iso-8859-1?Q?ZR8gu/h7hoQs/iF+t/o/Rr0za+1KtqEnHMAJQ3l2c2B1wQXocS4PSpgFsv?= =?iso-8859-1?Q?JpOFqjqMnXhi48tcrKEnAlayQuIx7sXE10vii5mrgjX/ft83CPaoHEK/yA?= =?iso-8859-1?Q?pYudpFNQEWZaSozb/ZVCdCX4KpswWO3sEOEHii2UqgqJhF+BdTV6gVLVbc?= =?iso-8859-1?Q?b/+UPObr1o4N6NfWl1Y0WjM9rNwdEQXlw13cPvLNRfmVZReCu4M3mVZlX+?= =?iso-8859-1?Q?SEhQpBISwZoB/8qLjXmkYbBUifuxU8SIa268HGogzLhPxyZE/PGhcvROj7?= =?iso-8859-1?Q?1aXvGoDLNxSSEod1Cb46gWav2Dm6k7gfLn16J7wYQF+ABPCn+ryhceQDzy?= =?iso-8859-1?Q?HHabaXyAaulPgI187moJTaJQnEC3lv1Pc0GDwxki9hqh080RAAjRGRHr3m?= =?iso-8859-1?Q?cpp4INVnn9DTDVPy6aDr8Oo6uYHg4N2YKtX37PUngEKNzD4DK49pKlTrFr?= =?iso-8859-1?Q?SJtfabbNcMAUEf7OCGIf5JmNSIfD3jHAp+uL/vUZhrOlEWI8FpPfNrSwTa?= =?iso-8859-1?Q?DQaPp55eYoLtNdlvIayaHlgmkk584snkGeMN4xZtGzD/Bl0D3qxgdxVV3K?= =?iso-8859-1?Q?rk1KRHQ1iUDAO7vBLF/JRmWdRKTuSGHMVgdEtoNtyx1UL0ZB4ZllISMQ7G?= =?iso-8859-1?Q?zgRUZsgneLyFaUBoJrZgtbw99+Dp3QEiro5SB9r7hclx8c3V7KCesO/Sx5?= =?iso-8859-1?Q?eEheFEqew0d1rrMvXf/Sv9+c2Zrarok4aEVVcyN7/+aOyiEW95xIgkAUGL?= =?iso-8859-1?Q?u821Xi7aM3Kxe45jIGg77077NJJKOmiVT1qHeL1LoyDuJ+wuuU2RCj8BJF?= =?iso-8859-1?Q?DEMtKpBQwHSgnitFU8uTQV7hyWSYdYGewOH1BjOLOfCzN2diJuZlI5+lLd?= =?iso-8859-1?Q?VwNcwY4Vw+rv0irs5PWsFe/MDo9c7XkuBtJDDeTgX6sbAxOsnc1jotCus9?= =?iso-8859-1?Q?eNuXySBUEePYeDIe6STK2yBd2MMUTIfqxIL1b+G17Br+9J1ORHpF5VOBJj?= =?iso-8859-1?Q?fssE71j/v4dY0AdWY4JfvR4Gp4FAyv6bU81Uug=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AM7P189MB1028.EURP189.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(41320700013)(366016)(1800799024)(376014)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?QimXz9072EamN/3RGHIWdC1X8wjUzbi223Wxtj/fXn+Iq1mu7TbGi2ZD2M?= =?iso-8859-1?Q?DFThnZpwHCNgDpbAIXpDDteAMhlZ+y4irZ1tNw427XJhML0AxugRtsWWdN?= =?iso-8859-1?Q?fFnSDsKuUwRyfSg5/J8XCtvG1e8WLrJBxXwjBMaGpPvvEt+5Xx0mSNmBth?= =?iso-8859-1?Q?OSvGQQUCyiF51sk5cUTPjifUor7f2yY9f66pZkMNiVKjVqzZm+p/1u9YaS?= =?iso-8859-1?Q?bn5jmgp9maca5LZJWxwP5028k3tmip5Nd0amQypukCzpj24KIDaXp/WRFU?= =?iso-8859-1?Q?w+PSc4ZPmRqsiZ2o2GFc+RnwhYIJUOjzT6MD/JpTrbu1p8HIKJ3Mygqsu1?= =?iso-8859-1?Q?mMNrBbuiBxTjUODJAFxsoBNtfumgfvynJxnWNLnWKmSE4Bv8fjffrt7V7F?= =?iso-8859-1?Q?BEv6ESjTh54dNoNvnx6rQfPrWCv9LT2QgEa4Y2/bAJqJKiv7PO3U7FYNjw?= =?iso-8859-1?Q?qagfqAZcHIHo8jjMECxN3HWLVVdkXvhNT6y3s1MVD33sZ7Kvbk7a1jfTw1?= =?iso-8859-1?Q?HX5af+Vh65d5W2943NU5/VzjrLvcDkRVUojZkGCBWgVLS55Ey2mCWltM6i?= =?iso-8859-1?Q?hg6X0a7bHDFrSkYS0k2VeEKgutW5xEzAeu3BdkS1oN3+hJTqL1/uNr7p13?= =?iso-8859-1?Q?uXpX/MGhmXkEAYKUWKRMClm2f8HYqC8KOMwT2jjdwnkSzRmcwJEu966ugq?= =?iso-8859-1?Q?UmZdAMKnUh/4nO1ZGRTUGdC8z26C+TysaSJ9Ti/fKJPglNgVhtNOXfHzoP?= =?iso-8859-1?Q?NFGYZcOIF7zg6ThvssirYb9xqvURrf4mKZH4lLS7SUETK68wVFC2OvQ1oX?= =?iso-8859-1?Q?HC5FJT5P2977Z/9KhwJ+GKw5a7BMCMCv5LQPp0MJOmk9eQkJ7BT2RStvKI?= =?iso-8859-1?Q?RPIfh0EKoowpR7t/1q1IGG+kcgz2i4o4qGflWbTxsvUPhMhRnwlSaLVM6D?= =?iso-8859-1?Q?ZNCnSJbpArnt++WcswDJNCAeLTxHZueKcZoaAdR9E+CIaCWL8BpVraskIW?= =?iso-8859-1?Q?+hgcKUyZiuD46Y9gCuS8KqSw9QBdrOshG+tMSY99Pu8y3hqseAEKoMnvOh?= =?iso-8859-1?Q?up/znLcrYX3rMwF3KhqP5BCuD9k2jrIvqyNajXoB2J3wZCSfLS+S/zS4NF?= =?iso-8859-1?Q?r85O0OEoBMgImcwknFViltpKs8nN0BbpGlTacDWAWBpt90rWl/RTiS6fBJ?= =?iso-8859-1?Q?NBZb67Vil7OUxbEd0rCkaSsUF0kEOWbyQYzM3VRT5JIG531LfXYe6JzWBk?= =?iso-8859-1?Q?annlRsKf+nY1U2AKDYBCKGd1i72N7g4dvgjzZVozeF7niUuNVHG/vzAunD?= =?iso-8859-1?Q?S7UGyH0sTW9JGm/5nJuI6tgP5UQl3qu4tdBVnZDBI9egEGt9f3VkRsAU71?= =?iso-8859-1?Q?SKOZfSmGvesYBLDJFc1aIWADluoolN9992jQHQexrqVJShNvohMF9J2Umv?= =?iso-8859-1?Q?cOD5r+M7W9pkbWDwEFae6x3TAMMr4fzrmSrFXWTBNDhT4y1nqkx5c+IUQH?= =?iso-8859-1?Q?6D65WN1p0lRcPnc9j/EA/0rP8rlNN20VDuTR9MNm55A6e5zQGuEM+jEzXO?= =?iso-8859-1?Q?cKz8kOm7FBHWAXInBz55Ndq4tYdzfzUE5wls0HJQ/cLsEhULGcAiVtxtlg?= =?iso-8859-1?Q?PDwWOVELSKItfoFfci1KKEQBsGKvYs/aPV?= Content-Type: multipart/alternative; boundary="_000_AM7P189MB1028C4BB972E87F92D516B8A9D3E2AM7P189MB1028EURP_" MIME-Version: 1.0 X-OriginatorOrg: Nibio.no X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: AM7P189MB1028.EURP189.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: cba7bb43-6921-417f-55e3-08dd19e7d39e X-MS-Exchange-CrossTenant-originalarrivaltime: 11 Dec 2024 13:29:23.4397 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 6df9a8ea-1ed0-462c-85a8-831200d5f7c5 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: imCXJdCgIHgl5I+qSQUyQqoggJqXVWttR9l4eoKyD4kZXz6s1jCn3MPLj1Bcq7FVXmz8MQChouRgM0f/UUYxFA== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB8P189MB1141 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM7P189MB1028C4BB972E87F92D516B8A9D3E2AM7P189MB1028EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ________________________________ Sent: Tuesday, December 10, 2024 4:31 PM To: Lars Aksel Opsahl Cc: Tom Lane ; Christophe Pettus ; pgs= ql-performance@lists.postgresql.org Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Thanks for that link; seeing actual queries is a big help. One thing to try= is to get some index-only scans to run. Regular indexes need to consult th= e heap (main table) for visibility information, and a bloated table can mak= e that consultation expensive. For example, an index like this should work to trigger an index-only scan: create index gregtest on node(geom) include(node_id) where containing_face= =3D0; For those not following that link, the query is: SELECT node_id, geom FROM node WHERE containing_face IN (0) AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE= 7636A3C625404E468D0D23E84D40'::geometry Or if containing_face is not always 0, a more generic variant: create index gregtest on node(geom, containing_face) include (node_id); What is the nature of the updates that are causing that many dead rows in t= he first place? Cheers, Greg Hi Thank's I did not know about the include option, I will have this mind when= working on this. I have done some simple testing with it now, but I have not seen any big ch= anges yet, but issue in case 97 is not the same as in issue 67. What probl= ems cases we end up with, will vary depending on the simple feature layers = we run overlay between and for instance how many surfaces we need remove fr= om the topology layer before we produce the final result. In this case based on pg_stat_statements in seems like a lot off the time i= s used on updates when removing edges. Why we get all this dead rows are related Postgis Topology database structu= re and input data. In some cases we get input data with verry many almost p= arallel lines which are close and that will cause a lot off edges be remove= d. Lars --_000_AM7P189MB1028C4BB972E87F92D516B8A9D3E2AM7P189MB1028EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Sent: Tuesday, December 10, 2024 4:31 PM
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christophe Pettus <x= of@thebuild.com>; pgsql-performance@lists.postgresql.org <pgsql-perfo= rmance@lists.postgresql.org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead ro= ws
 
Thanks for that l= ink; seeing actual queries is a big help. One thing to try is to get some i= ndex-only scans to run. Regular indexes need to consult the heap (main tabl= e) for visibility information, and a bloated table can make that consultation expensive.

For example, an i= ndex like this should work to trigger an index-only scan:

create index greg= test on node(geom) include(node_id) where containing_face=3D0;

For those not fol= lowing that link, the query is:

SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE8= 4D4017EE7636A3C625404E468D0D23E84D40'::geometry

Or if containing_= face is not always 0, a more generic variant:

create index greg= test on node(geom, containing_face) include (node_id);

What is the natur= e of the updates that are causing that many dead rows in the first place?

Cheers,
Greg

Hi

Thank's I did not know about the include option, I will have this mind= when working on this.

I have done some simple testing with it now, but I have not seen any big ch= anges yet, but issue in case 97 is not the same as in issue  67. What = problems cases we end up with, will vary depending on the simple feature la= yers we run overlay between and for instance how many surfaces we need remove from the topology layer before we produce= the final result.

In this case based on pg_stat_statements in seems like a lot off the time i= s used on updates when removing edges.

Why we get all this dead rows are related Postgis Topology database structu= re and input data. In some cases we get input data with verry many almost p= arallel lines which are close and that will cause a lot off edges be remove= d.

Lars

--_000_AM7P189MB1028C4BB972E87F92D516B8A9D3E2AM7P189MB1028EURP_--