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

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.