Re: Potential "AIO / io workers" inter-worker locking issue in PG18?

I noticed the formatting of the last email was totally screwed when 
displayed on postgresql.org's mail archive making it hard to read there, 
so a re-post of the last email, hopefully it will be better this time.

Answers are intermingled with all the quotes, read carefully.

Op 8-10-2025 om 22:09 schreef Marco Boeringa:
>
> 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.
>