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 1v6aTK-006sbg-VT for pgsql-bugs@arkaria.postgresql.org; Wed, 08 Oct 2025 20:09:15 +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 1v6aTI-00CrFH-E1 for pgsql-bugs@arkaria.postgresql.org; Wed, 08 Oct 2025 20:09:13 +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 1v6aTH-00CrF7-UC for pgsql-bugs@lists.postgresql.org; Wed, 08 Oct 2025 20:09:13 +0000 Received: from ewsoutbound.kpnmail.nl ([195.121.94.169]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1v6aTD-000j7r-1o for pgsql-bugs@lists.postgresql.org; Wed, 08 Oct 2025 20:09:11 +0000 X-KPN-MessageId: ac42a6cb-a482-11f0-84a0-005056abad63 Received: from smtp.kpnmail.nl (unknown [10.31.155.37]) by ewsoutbound.so.kpn.org (Halon) with ESMTPS id ac42a6cb-a482-11f0-84a0-005056abad63; Wed, 08 Oct 2025 22:09:19 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=demon.nl; s=demon01; h=from:to:subject:mime-version:date:message-id:content-type; bh=DTVJA3vnJjMYSG6xJ6MXo9xn/Govg7dKF1UtaH+3pUY=; b=dWxkNYNW98mRu0MHnt5vInzJpFMMcl5x6XdaEv+Arb+yu9ql3zKI6llHC7XqD1qpxq98G69gp4wbq KedZ4HiUBkB5sLHX3VsVDo5n97Zd9O8qhJgO86rXkixo7f4JMFFLAQG/cjT4Z8WtsVMqPHqmycyq3v 6O0DcPY+t5EEdDiNAmWso92AUhmvrGPqUT1EptcHB3hJWk866//HbvF1QwnsFo+fMr9UtKEQZ3sd2w BpQ/uiq+00N9J36MlHL2VrrTeBDhR8NiniC0iYsC7ayqF8Jd0kHw9FFGO90q7JhTYVBFM2E1GsyNfu rGJ6pSl1KQari0DJjzl3/s0Lq2MzEJA== X-KPN-MID: 33|oF36eQUXzRmo/rkQ7zFYPsqjWnVb0iwtzMssQaQlCPBJ50sxytN9a/7zfeY0Blt Q9e2w3pb5+6zSqd1WgH++d5uoBUI/IPSyIaalr4JQda0= X-KPN-VerifiedSender: Yes X-CMASSUN: 33|OtY4VfferyJQEKS+HzmUXX5F72VHFDeuaHBGz8dO3ASAanfbV6owiJ4JbPJ3Rmg YeCEiJ/TY4owOHWYympGh1Q== X-Originating-IP: 77.174.174.39 Received: from [192.168.1.35] (77-174-174-39.fixed.kpn.net [77.174.174.39]) by smtp.xs4all.nl (Halon) with ESMTPSA id a38ad47c-a482-11f0-bd99-005056ab1411; Wed, 08 Oct 2025 22:09:04 +0200 (CEST) Content-Type: multipart/alternative; boundary="------------qnQNdPQdM88cV7AwJPQcD6f7" Message-ID: <6f4d0f00-27e8-44ea-bb2f-70aa636c6d09@boeringa.demon.nl> Date: Wed, 8 Oct 2025 22:09:04 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Potential "AIO / io workers" inter-worker locking issue in PG18? To: Andres Freund Cc: pgsql-bugs@lists.postgresql.org, Thom Brown References: <6ro55q35wiahf7mpsbnhyneatiqkz4qrjcfgtg6zo2bvtjwrxy@5fzxrbymhop3> <53b44572-0ceb-4149-b361-07da2da91032@boeringa.demon.nl> <9d2027e4-ff80-4da9-90d6-4a7af58a1d9e@boeringa.demon.nl> <3uiezmcbzueur7icgprzxgm7og3zu4lofxmebaryqzmm3kz2uv@5vssptglj7gf> Content-Language: en-GB From: Marco Boeringa In-Reply-To: <3uiezmcbzueur7icgprzxgm7og3zu4lofxmebaryqzmm3kz2uv@5vssptglj7gf> X-Antivirus: Norton (VPS 251008-6, 8-10-2025), Outbound message X-Antivirus-Status: Clean List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------qnQNdPQdM88cV7AwJPQcD6f7 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi Andres, > Even just knowing whether the "normal query plan" is the same one as we see in > profiles of "stuck" backends is valuable. Even if the query plan is perfectly > normal, it *still* is very important to know in which order the joins are > evaluated etc. But there also might be changes in the query plan between 17 > and 18 that trigger the issue... > > Without more details about what is expected to be run and what is actually > happening, it's just about impossible for us to debug this without a > reproducer that we can run and debug ourselves. I now encountered the auto_explain option in the PostgreSQL help. May sound stupid, but I hadn't been aware of this option. This might help in getting an explain during the actual execution of my tool, if I understand the option properly. This would be far more valuable - as being the "real" thing - than some contrived reproduction case. I will need to investigate this a bit more: https://www.postgresql.org/docs/current/auto-explain.html >>> Making vacuum more aggressive won't really help much if you have >> longrunning queries/sessions, since vacuum can't clean up row versions that >> are still visibile to some of the transactions. >> >> My code batches the updates in sets of 2000 records at a time and then >> COMMITs, so the transactions themselves are limited in time and size, which >> should allow vacuum to do its job. > > Are the "stuck" backends stuck within one 2000 record batch, or are they > "just" slower processing each batch? I can't tell. But to explain: each thread has its own set of jobs assigned, and each job will be batched in sets of 2000 records until COMMIT. So if one job has 100k records to process, 50 commits should occur for that job by one Python thread. I take care to avoid to process records totally randomly, which could cause conflicts and locking issues between threads attempting to access the same locked database page, significantly slowing down the processing. Records are assigned by database page (and depending on some other parameters), which has worked really well so far. Note that this is just a simplified version of the different processing modes I developed for different challenges and geoprocessing steps. >> 26.48% postgres postgres [.] LWLockAttemptLock >> | >> ---LWLockAttemptLock >> | >> |--23.15%--heapam_index_fetch_tuple.lto_priv.0 >> | index_fetch_heap >> | IndexNext >> | ExecScan >> | ExecNestLoop >> | ExecNestLoop >> | ExecModifyTable >> | standard_ExecutorRun >> | ProcessQuery > > So the query plan we have is a nested loop between at least three tables > (there are two joins, c.f. the two ExecNestLoop calls), where there presumably > are a lot of row [versions] on the inner side of the innermost join. > > In [1] you showed a query. Reformated that looks like this: > > UPDATE osm.landcover_scrubs_small_scale_2_ply AS t1 > SET area_geo = t2.area_geo, > perim_geo = t2.perim_geo, > compact_geo = CASE WHEN t2.area_geo > 0 THEN ((power(t2.perim_geo,2) / t2.area_geo) / (4 * pi())) ELSE 0 END, > npoints_geo = t2.npoints_geo, > comp_npoints_geo = CASE WHEN t2.npoints_geo > 0 THEN (CASE WHEN t2.area_geo > 0 THEN ((power(t2.perim_geo,2) / t2.area_geo) / (4 * pi())) ELSE 0 END / t2.npoints_geo) ELSE 0 END, > convex_ratio_geo = CASE WHEN ST_Area(ST_ConvexHull(way)::geography,true) > 0 THEN (t2.area_geo / ST_Area(ST_ConvexHull(way)::geography,true)) ELSE 1 END > FROM ( > SELECT > objectid, > ST_Area(way::geography,true) AS area_geo, > ST_Perimeter(way::geography,true) AS perim_geo, > ST_NPoints(way) AS npoints_geo > FROM osm.landcover_scrubs_small_scale_2_ply) AS t2 > WHERE (t2.objectid = t1.objectid) > AND t1.objectid IN (SELECT t3.objectid FROM mini_test.osm.osm_tmp_28128_ch5 AS t3) > > > Which certainly fits with two nested loops, although I don't think I can infer > which order it the joins are in. > > > Is osm.landcover_scrubs_small_scale_2_ply.object_id unique? Yes. > Can there be multiple rows for one object_id in > mini_test.osm.osm_tmp_28128_ch5? No. This table contains the records to process, which are unique. It is the job. It is a one-to-one join. > Are there indexes on mini_test.osm.osm_tmp_28128_ch5.unique_id and > osm.landcover_scrubs_small_scale_2_ply? Yes, the unique ids / objectid fields are indexed to allow an efficient join. --------------qnQNdPQdM88cV7AwJPQcD6f7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi Andres, > Even just knowing whether the "normal query plan" is the same one as we see in > profiles of "stuck" backends is valuable. Even if the query plan is perfectly > normal, it *still* is very important to know in which order the joins are > evaluated etc. But there also might be changes in the query plan between 17 > and 18 that trigger the issue... > > Without more details about what is expected to be run and what is actually > happening, it's just about impossible for us to debug this without a > reproducer that we can run and debug ourselves. I now encountered the auto_explain option in the PostgreSQL help. May sound stupid, but I hadn't been aware of this option. This might help in getting an explain during the actual execution of my tool, if I understand the option properly. This would be far more valuable - as being the "real" thing - than some contrived reproduction case. I will need to investigate this a bit more: https://www.postgresql.org/docs/current/auto-explain.html >>> Making vacuum more aggressive won't really help much if you have >> longrunning queries/sessions, since vacuum can't clean up row versions that >> are still visibile to some of the transactions. >> >> My code batches the updates in sets of 2000 records at a time and then >> COMMITs, so the transactions themselves are limited in time and size, which >> should allow vacuum to do its job. > > Are the "stuck" backends stuck within one 2000 record batch, or are they > "just" slower processing each batch? I can't tell. But to explain: each thread has its own set of jobs assigned, and each job will be batched in sets of 2000 records until COMMIT. So if one job has 100k records to process, 50 commits should occur for that job by one Python thread. I take care to avoid to process records totally randomly, which could cause conflicts and locking issues between threads attempting to access the same locked database page, significantly slowing down the processing. Records are assigned by database page (and depending on some other parameters), which has worked really well so far. Note that this is just a simplified version of the different processing modes I developed for different challenges and geoprocessing steps. >> 26.48% postgres postgres [.] LWLockAttemptLock >> | >> ---LWLockAttemptLock >> | >> |--23.15%--heapam_index_fetch_tuple.lto_priv.0 >> | index_fetch_heap >> | IndexNext >> | ExecScan >> | ExecNestLoop >> | ExecNestLoop >> | ExecModifyTable >> | standard_ExecutorRun >> | ProcessQuery > > So the query plan we have is a nested loop between at least three tables > (there are two joins, c.f. the two ExecNestLoop calls), where there presumably > are a lot of row [versions] on the inner side of the innermost join. > > In [1] you showed a query. Reformated that looks like this: > > UPDATE osm.landcover_scrubs_small_scale_2_ply AS t1 > SET area_geo = t2.area_geo, > perim_geo = t2.perim_geo, > compact_geo = CASE WHEN t2.area_geo > 0 THEN ((power(t2.perim_geo,2) / t2.area_geo) / (4 * pi())) ELSE 0 END, > npoints_geo = t2.npoints_geo, > comp_npoints_geo = CASE WHEN t2.npoints_geo > 0 THEN (CASE WHEN t2.area_geo > 0 THEN ((power(t2.perim_geo,2) / t2.area_geo) / (4 * pi())) ELSE 0 END / t2.npoints_geo) ELSE 0 END, > convex_ratio_geo = CASE WHEN ST_Area(ST_ConvexHull(way)::geography,true) > 0 THEN (t2.area_geo / ST_Area(ST_ConvexHull(way)::geography,true)) ELSE 1 END > FROM ( > SELECT > objectid, > ST_Area(way::geography,true) AS area_geo, > ST_Perimeter(way::geography,true) AS perim_geo, > ST_NPoints(way) AS npoints_geo > FROM osm.landcover_scrubs_small_scale_2_ply) AS t2 > WHERE (t2.objectid = t1.objectid) > AND t1.objectid IN (SELECT t3.objectid FROM mini_test.osm.osm_tmp_28128_ch5 AS t3) > > > Which certainly fits with two nested loops, although I don't think I can infer > which order it the joins are in. > > > Is osm.landcover_scrubs_small_scale_2_ply.object_id unique? Yes. > Can there be multiple rows for one object_id in > mini_test.osm.osm_tmp_28128_ch5? No. This table contains the records to process, which are unique. It is the job.

It is a one-to-one join.

> Are there indexes on mini_test.osm.osm_tmp_28128_ch5.unique_id and > osm.landcover_scrubs_small_scale_2_ply? Yes, the unique ids / objectid fields are indexed to allow an efficient join.

--------------qnQNdPQdM88cV7AwJPQcD6f7--