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 1tLHDR-00DpKe-MM for pgsql-performance@arkaria.postgresql.org; Wed, 11 Dec 2024 07:33:02 +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 1tLHDP-00DhNq-6T for pgsql-performance@arkaria.postgresql.org; Wed, 11 Dec 2024 07:33:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tLHDO-00DhNb-O3 for pgsql-performance@lists.postgresql.org; Wed, 11 Dec 2024 07:33:00 +0000 Received: from mail-db8eur05on2116.outbound.protection.outlook.com ([40.107.20.116] helo=EUR05-DB8-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tLHDM-002DRk-3x for pgsql-performance@lists.postgresql.org; Wed, 11 Dec 2024 07:32:58 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=ENlQaug6+iyXNv3JF+FRFG/fSCMbf04LJtUDTtf4/WI3VcZg7Gx22KwHe4qeGxJScyGorjn8M2JOS/LIlOGHGNd7NiX7721uJoJz2c/MfYyPzZluDl9CFOVkVA583ct3WVQ2d+3xZLcz0FR4uZUV07ASjDiIu7ndvwkVlQyjlh3C7kP9h49hKhAzYABvZpbfsU87SUmUbFWDbSGXJWLQHwDVmHjEmF8WWp0y1uYGCK5sWldjimqferka20KApZ0Uc84NXJDUKY04T9uIHUbO6aMyJKSVOCFSXrzJ/kFptiwwzl1Puw1VWIwctA7Qq8Gr/k0b95qEdJqClQwPOVLwYQ== 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=FhD3wLqc8oYcXCqJAeJgJ2/9ATfzmcoWE3NGv/fl9Bo=; b=MTGwaeOAN0Aj9PHsuhhWIl5wIdZPNUKFfOBztxHDZuAfjVXu25W6DKOz0tVfe8Lp4bECv47enQ6kSMDA/BYEZ8pcnzpLlpDzhgB/nd6Uar369/sDfMyPC9nuDaKzU0yaD+qHrhjixERIxfCw4WcKDbTJudD4nL+dpnHaLdnO4ouDn4huwDGDz1XpjBiB8JlGkp4k4/TZFvCBqo1xmWAhMFnsQImcaXCdRt8KzBc23L7q+I8CnzMQWhEifYY8ynZc1aiqeCdfNxAgl8D1O/VpTKN8Vg0xDHJk/Ap/S0hrRTnhjBJOgbF4FThtiR562fzzpbKTEnD3wed2Eg29hf0M+Q== 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=FhD3wLqc8oYcXCqJAeJgJ2/9ATfzmcoWE3NGv/fl9Bo=; b=EHzQvqzrrysNHjWDAWt8fLUlzWPVIbcDKLUwQFtQz8k6l4U6UOwRPAV+uvsye0aQ2zaWea6gxltxT9LWCAvNr692AhTdV3udJjcgyXIHubPQjwCE+zXD6AolYQe5IVyy8cYw52GD9SywMCX7vCN20gLWQEl9bZEBMbvVYOwVMIs= Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:174::18) by AS4P189MB1847.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:4c3::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8230.18; Wed, 11 Dec 2024 07:32:52 +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 07:32:52 +0000 From: Lars Aksel Opsahl To: Hannu Krosing , 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+WAACjFgIAAUk2AgACodho= Date: Wed, 11 Dec 2024 07:32:52 +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_|AS4P189MB1847:EE_ x-ms-office365-filtering-correlation-id: c40618ca-7e00-4c12-d429-08dd19b605bd x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|41320700013|366016|7053199007|8096899003|38070700018; x-microsoft-antispam-message-info: =?iso-8859-1?Q?EUXiBje7Ftd4Dob43E3t82So8CK/nUsZBoOV3/W38B5cISIQbEAMHnfqrE?= =?iso-8859-1?Q?RL1PzRn3ZgnnAbkP31baP5Y3pE/CIpo3ShsvNGIiBXk8pFK+Yo4Q9UiPxq?= =?iso-8859-1?Q?tDMsNrY2CIBo0AVf5ICDY1fE45+0TBgtlGhhGvtFJq72kNzY2QsmB7habS?= =?iso-8859-1?Q?KsSAoQ9+lkARUMRbZnOn7pJQRjXaPwAauMhBrt61ruH/ZQun+q/9HUEfS1?= =?iso-8859-1?Q?SIEdMY+OGB8pwOl8rntjAguE31l7J91VgQwzgR+ROfhuCBYGa2tqrh12dA?= =?iso-8859-1?Q?qLA0EqnPAfRyyYVnMr8lAIlan7CjV2LFpJcW5CktZV9I2mmCwDATVsn0+j?= =?iso-8859-1?Q?zZrThUGldd0u91C570xDzspuqaTbOvZeukhhwPtC1mt7eK5ZdJvDPupEzc?= =?iso-8859-1?Q?c4kGb9wzTzmPvyCzr38/47WT2zhuPyV1XtC5niEIDbeCmnlEu1NPmdjOJn?= =?iso-8859-1?Q?rE/wx1Xv3N9oX4S39cZeQR8qRfEDE5P2sz8hxQKemLZb2KpPpTc6LPv4Z+?= =?iso-8859-1?Q?yLh98QqgTz3SJNdrvh4t41CtwJ+X4tF01drrOC6ClZ51zuSnBHVHujNt12?= =?iso-8859-1?Q?ineSliybObWO8tsGbTBK5YqVs7EONamvK5R9Bzpfs1bbCRVcwLXGLIUP1R?= =?iso-8859-1?Q?YtU194DP8luM0A0LPni2zjiJEVBIIgJdE07f584dbMEcM/j8C7tjDqd7D4?= =?iso-8859-1?Q?7Zte1CZkxsVvTcctwTwPTYOIw+X8ocKpEcMlu14yQRb+72kzlb9nXwictA?= =?iso-8859-1?Q?j53ah/wCp3anpwpVoReSAKXV0QRSGnkOn6+1tQaQEZ5S5MpmlArykpm1lL?= =?iso-8859-1?Q?EZ9j+zF+IBmNPs98LXUSdRhyzkg3El8LqcLBTAYR9aXlB1T1iTNi3fl64o?= =?iso-8859-1?Q?PAMvH7BvpYuWkQxE8r3wUk3KoaDHlFfQBbnoXIKYsmnlFeE72qsWIY6Aqj?= =?iso-8859-1?Q?86srPPOEUFB+sLkyW3LvubLLn3NBUrGtYSAx4GdCnUEKiZL5G/iECrHskQ?= =?iso-8859-1?Q?MCbSOeYBVDA8vbrxqJ56o2CWEkQAzLBhzCoT8mA6dvcWr2BX/h0Ig5pR8D?= =?iso-8859-1?Q?GQHhVnP5095NEjFGxro8/ofg3E+ijndbhhrT1pXtw/MhayYaodOv+tMIlA?= =?iso-8859-1?Q?xpkj3ahFwJDNJvFDWvenjCZe7MmhOo1aQQw64UrIkR29CYvtEhP+REhVQB?= =?iso-8859-1?Q?DkPiGGmPOv8dqROENU7GbX3ZKdmAdaEsndUJW3xUte7MBBmd9spiryjOxk?= =?iso-8859-1?Q?cEgsdpbfxJsdWk7KWKGLpbwkj2t1t8sZsx3llgmjkSRn79VbHJb0Lw7yix?= =?iso-8859-1?Q?sHjLavxog4BAQJ2AF3Mi2+LszgJ33203NQPhiQSgtjSr3Xrw9f12ha+Pec?= =?iso-8859-1?Q?fp5kQUsiRSBssZ+XXEQeOajtMQCHvplcZXyJCVv/GYfR0URYaAAGw=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)(376014)(1800799024)(41320700013)(366016)(7053199007)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?JKz6xzV7dTvrIxKUSYQ7mMoEEJRE2fYrHLLbM/pTAJN+6k+0vFCdeZHlar?= =?iso-8859-1?Q?9lfaPI2WNo8aIiR5de+zUY5vkT3+bs41S7lkp2nLNUGahAv/rFKiHSke7K?= =?iso-8859-1?Q?Eguo5dqpX+zRO1Oq/wwpjdjvS1RaWXFodCYeYkq1/VcuPZ7YF4T7ei9528?= =?iso-8859-1?Q?vTQQRMnJK2WXgsm1OZ9kgrdjuKHcT+sc7OAtGzZ/ivi5CTG3qMV51eQW/J?= =?iso-8859-1?Q?tfKFQ1fwFBbueiTKJqJbXdOvTw0JusSf1qEIMZZwoylDhH0/wkexMjvmEU?= =?iso-8859-1?Q?iZkj3FSJ9vVsQDh0u0uITTKpBQlU92i1x7a4InDwNV+/hNH1xWGn3NZg4v?= =?iso-8859-1?Q?b5EdORr6tzZJjrczZTG9ebcMARDB/chPI/hFdNJZydyklHRjLzuuLgMTal?= =?iso-8859-1?Q?xKOldHqr06dmoYEJlkKeOPM/4tswW3svccMKQ3AOy22eGsZJq+Mvf4wzsO?= =?iso-8859-1?Q?k2rWa0FlWp/QERq+ofQTlavub6YWgYh21fHmNL0kRd38wUpRTFgx5IFr0B?= =?iso-8859-1?Q?TsCmW1/TdDy7Da88sD1pYyPenfdeh/YGvz511icoeLT5q7X0196b6N0sYY?= =?iso-8859-1?Q?aMlzuaE0uDU/4ZkNTMm4KKOMbNLTudhXo7yVyLe74yFIwuHu29LCCORSxm?= =?iso-8859-1?Q?KZERTh+g7gJjLaNhZE7JH/6qL8As5SBpnkfdYLNIGdGKMSRtps4HrYbZjs?= =?iso-8859-1?Q?haF+Ml+FyvzhSKSznCeQAFVpJcRbMwTopBEZYhrXLPKy/yCMgzsEJwZmeA?= =?iso-8859-1?Q?4Esqzh8+BHVAEw5wpWK3AyJNrrOP0R+o/16Y7f+MYEjCwPP8Z304D3Pm62?= =?iso-8859-1?Q?oPSLmaa+xEXrdnXk+xp9+bxu7FOiMAWD9lG7wwXLa7f98j3OpN8jjWvuT2?= =?iso-8859-1?Q?MCF9VMtqHUS+XYZXA3yFnP8Y/lZFo+squ9s7LrCUKVZl5k5Xf1GSRQzN8y?= =?iso-8859-1?Q?R+0bZUdcuNP6BuJ2KmvrYRC3/INEzwa6Y5Vwbg2ztkdEh3IKtApSX4YQJ6?= =?iso-8859-1?Q?XrNtxNU2a9mIkNsvtGniCAEzJcOyDgW9N8e6mBgVmUMjqRMZPkI1VMMp2o?= =?iso-8859-1?Q?Z49cAuO1icqzg3HWk2PhbQogDCneGcsmAWd4iHBkypIenpyn7VqEUFIhLB?= =?iso-8859-1?Q?yvsl/K8I9Y/hcyCBJDs8Kp9KrgfqHXmWRBpwj/jseYc8GW3GtB3CS3VVrx?= =?iso-8859-1?Q?cjKNjsDS7n4m2iyRC1dIXI79YOwLROd+Ee1PXNiNo7KxJJAoHFIIRS5koZ?= =?iso-8859-1?Q?Zeee+8re0iQEWiBJNQBeeVOeGq/j00sTlc1IAStLxGp8cPJGdA97aMtuTg?= =?iso-8859-1?Q?dkoyvtx2TVbRCibs0DaswU3Pho1ZK8Nh9SWgXj4w+DAQc4B0EL94tpIH1F?= =?iso-8859-1?Q?zMNDk4qtmWlmqKI3NMbFrM929/oTN3cqWNE6mmFUrM0hnsqmzym9Tg/OHK?= =?iso-8859-1?Q?oBv1cqUy99Lx5wNg4+L/96EDLlNmdSXTB5ZiZFGmqaS8nXyZdgS+3QOJce?= =?iso-8859-1?Q?GBzLd4BVQVg56CoZy75sR8L2eoAMX/hLlxzmqw1vdJNqilghPx+Qd2AIxR?= =?iso-8859-1?Q?6NC0/bGbZpv5Ofvt8UxyPCe+1D/g8mFgWvH7DoIuYW9mItV+eZbyWwV+w6?= =?iso-8859-1?Q?ol3fKPpc6Rp10=3D?= Content-Type: multipart/alternative; boundary="_000_AM7P189MB10287F56FD5127E193D63E2C9D3E2AM7P189MB1028EURP_" 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: c40618ca-7e00-4c12-d429-08dd19b605bd X-MS-Exchange-CrossTenant-originalarrivaltime: 11 Dec 2024 07:32:52.7112 (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: QdCZpr8RNBxqo5lwRruAx2D80sARtv5DEzwuuTjX+YejgdGoOTrwyeaFt26RfWoIqso2QAln3vl792itWJ1mOA== X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS4P189MB1847 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM7P189MB10287F56FD5127E193D63E2C9D3E2AM7P189MB1028EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ________________________________ From: Hannu Krosing To: Greg Sabino Mullane Cc: Lars Aksel Opsahl ; Tom Lane ;= Christophe Pettus ; pgsql-performance@lists.postgresql.o= rg Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows If there are unremovable rows it usually also means that index-only scan de= grades to index-scan-with-visibility-checks-in-tables. I think the ask is to be able to remove the recently dead rows that are not= visible in any current snapshot and can never become visible to any future= snapshot, Something that Tom described as currently not easily doable above. Maybe we could figure out a way for long-running transactions to upload the= ir snapshot set to some meta-vacuum process which can use it to determine w= hich rows fall into that category. In this way the change would affect only= the long-running transactions and if we do it onl maybe once a minute it s= hould not be too heavy overhead even for these transactions. Hi This is tested but only works when the load is very low. To fix the problem with xmin we use this https://gitlab.com/nibioopensource= /postgres_execute_parallel to start the jobs, but the control is returned = to the caller at given intervals, and the caller also does a commit and get= s a new snapshot and xmin is updated. If all jobs done are done the caller = may start a new batch of jobs. One of the 750 jobs takes for hours to remove (https://postgis.net/docs/ST_= RemEdgeNewFace.html) almost 30.000 edges. I do this in a loop takes around= 500 edges in every loop. If the time used is to long I forced a commit in = this loop in addition to the analyze I do. Adding the commit really helps o= n execution time but only when running a very few jobs in parallel. With th= is new commit I could see that dead rows were removed. The issue with the new commit in the loop where I remove edges is that it b= ecomes problem when many treads run parallel related to subtransactions per= transaction like discussed here https://postgrespro.com/list/thread-id/2478202 and many= other threads. Almost like a catch 22, fixing the dead rows problem is cau= sing a new bigger problem. So to solve problems with subtransactions per transaction I can make new jo= blist for removing edges also, but that makes things more complicated. Thanks Lars. --_000_AM7P189MB10287F56FD5127E193D63E2C9D3E2AM7P189MB1028EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

From: Hannu Krosing <hannuk@google.com>
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; Tom Lane &l= t;tgl@sss.pgh.pa.us>; Christophe Pettus <xof@thebuild.com>; pgsql-= performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org= >
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead ro= ws
 
If there are unre= movable rows it usually also means that index-only scan degrades to index-s= can-with-visibility-checks-in-tables.

I think the ask is to be able to remove the recently dead rows that are not= visible in any current snapshot and can never become visible to any future= snapshot, 
Something that To= m described as currently not easily doable above.

Maybe we could figure out a way for long-running transactions to upload the= ir snapshot set to some meta-vacuum process which can use it to determine w= hich rows fall into that category. In this way the change would affect only= the long-running transactions and if we do it onl maybe once a minute it should not be too heavy overhead ev= en for these transactions.
Hi

This is = tested but only works when the load is very low.

To fix the problem with xmin we use this https://gitlab.com/nibioopensource/postgres_execute_parallel to st= art the jobs,  but the control is returned to the caller at given inte= rvals, and the caller also does a commit and gets a new snapshot and xmin i= s updated. If all jobs done are done the caller may start a new batch of jobs.

One of the 750 jobs takes for hours to remove (https://postgis.net/docs/ST_RemEdgeNewFace.h= tml) almost 30.000 edges.  I do this in a loop takes around 500 edges in every loop. If the time used is to lon= g I forced a commit in this loop in addition to the analyze I do. Adding th= e commit really helps on execution time but only when running a very few jo= bs in parallel. With this new commit I could see that dead rows were removed. 

The issue with the new commit in the loop where I remove edges is that it b= ecomes problem when many treads run parallel related to subtransactions per= transaction
like discussed here https://postgrespro.com/list/thread-id/2478202 and many other thre= ads. Almost like a catch 22, fixing the dead rows problem is causing a new = bigger problem.

So to solve problems with subtransactions per transaction I can make new jo= blist for removing edges also, but that makes things more complicated.

Thanks

Lars.

--_000_AM7P189MB10287F56FD5127E193D63E2C9D3E2AM7P189MB1028EURP_--