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.
>