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

Op 8-10-2025 om 21:08 schreef Andres Freund:
> 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.
>
>
Hi Andres,

I have tried to get the auto_explain stuff to run, but that has not
succeeded not yet.

However, I realized that due to the extreme long "stall", it should be
possible to simply copy out the visible SQL statement from pgAdmin and
run it in a separate window, as the required (temporary) tables and
views to run the SQL statement would be there at that point all the
while the processing appears stuck, and run Explain simply from pgAdmin.

This resulted in the plan I pasted below in JSON format.

Any insights you gain from this in combination with the other stuff I
shared and the answers I gave to your last questions?

Marco

"[
  {
    ""Plan"": {
      ""Node Type"": ""ModifyTable"",
      ""Operation"": ""Update"",
      ""Parallel Aware"": false,
      ""Async Capable"": false,
      ""Relation Name"": ""landcover_grassy_small_scale_2_ply"",
      ""Schema"": ""osm"",
      ""Alias"": ""t1"",
      ""Startup Cost"": 1.14,
      ""Total Cost"": 9129.99,
      ""Plan Rows"": 0,
      ""Plan Width"": 0,
      ""Disabled"": false,
      ""Plans"": [
        {
          ""Node Type"": ""Nested Loop"",
          ""Parent Relationship"": ""Outer"",
          ""Parallel Aware"": false,
          ""Async Capable"": false,
          ""Join Type"": ""Inner"",
          ""Startup Cost"": 1.14,
          ""Total Cost"": 9129.99,
          ""Plan Rows"": 70,
          ""Plan Width"": 62,
          ""Disabled"": false,
          ""Output"":
[""st_area((landcover_grassy_small_scale_2_ply.way)::geography, true)"",
""st_perimeter((landcover_grassy_small_scale_2_ply.way)::geography,
true)"", ""CASE WHEN
(st_area((landcover_grassy_small_scale_2_ply.way)::geography, true) >
'0'::double precision) THEN
((power(st_perimeter((landcover_grassy_small_scale_2_ply.way)::geography,
true), '2'::double precision) /
st_area((landcover_grassy_small_scale_2_ply.way)::geography, true)) /
'12.566370614359172'::double precision) ELSE '0'::double precision
END"", ""st_npoints(landcover_grassy_small_scale_2_ply.way)"", ""CASE
WHEN (st_npoints(landcover_grassy_small_scale_2_ply.way) > 0) THEN (CASE
WHEN (st_area((landcover_grassy_small_scale_2_ply.way)::geography, true)
> '0'::double precision) THEN
((power(st_perimeter((landcover_grassy_small_scale_2_ply.way)::geography,
true), '2'::double precision) /
st_area((landcover_grassy_small_scale_2_ply.way)::geography, true)) /
'12.566370614359172'::double precision) ELSE '0'::double precision END /
(st_npoints(landcover_grassy_small_scale_2_ply.way))::double precision)
ELSE '0'::double precision END"", ""CASE WHEN
(st_area((st_convexhull(t1.way))::geography, true) > '0'::double
precision) THEN
(st_area((landcover_grassy_small_scale_2_ply.way)::geography, true) /
st_area((st_convexhull(t1.way))::geography, true)) ELSE '1'::double
precision END"", ""t1.ctid"",
""landcover_grassy_small_scale_2_ply.ctid"",
""landcover_grassy_small_scale_2_ply_pg.ctid""],
          ""Inner Unique"": true,
          ""Plans"": [
            {
              ""Node Type"": ""Nested Loop"",
              ""Parent Relationship"": ""Outer"",
              ""Parallel Aware"": false,
              ""Async Capable"": false,
              ""Join Type"": ""Inner"",
              ""Startup Cost"": 0.72,
              ""Total Cost"": 173.50,
              ""Plan Rows"": 70,
              ""Plan Width"": 828,
              ""Disabled"": false,
              ""Output"": [""t1.way"", ""t1.ctid"", ""t1.objectid"",
""landcover_grassy_small_scale_2_ply_pg.ctid"",
""landcover_grassy_small_scale_2_ply_pg.objectid""],
              ""Inner Unique"": true,
              ""Plans"": [
                {
                  ""Node Type"": ""Index Scan"",
                  ""Parent Relationship"": ""Outer"",
                  ""Parallel Aware"": false,
                  ""Async Capable"": false,
                  ""Scan Direction"": ""Forward"",
                  ""Index Name"": ""idx_osm_35"",
                  ""Relation Name"":
""landcover_grassy_small_scale_2_ply_pg"",
                  ""Schema"": ""osm"",
                  ""Alias"": ""landcover_grassy_small_scale_2_ply_pg"",
                  ""Startup Cost"": 0.29,
                  ""Total Cost"": 3.70,
                  ""Plan Rows"": 70,
                  ""Plan Width"": 14,
                  ""Disabled"": false,
                  ""Output"":
[""landcover_grassy_small_scale_2_ply_pg.ctid"",
""landcover_grassy_small_scale_2_ply_pg.objectid""],
                  ""Index Cond"":
""((landcover_grassy_small_scale_2_ply_pg.page_number >= 28873) AND
(landcover_grassy_small_scale_2_ply_pg.page_number < 29373))""
                },
                {
                  ""Node Type"": ""Index Scan"",
                  ""Parent Relationship"": ""Inner"",
                  ""Parallel Aware"": false,
                  ""Async Capable"": false,
                  ""Scan Direction"": ""Forward"",
                  ""Index Name"":
""landcover_grassy_small_scale_2_ply_pkey"",
                  ""Relation Name"":
""landcover_grassy_small_scale_2_ply"",
                  ""Schema"": ""osm"",
                  ""Alias"": ""t1"",
                  ""Startup Cost"": 0.42,
                  ""Total Cost"": 2.43,
                  ""Plan Rows"": 1,
                  ""Plan Width"": 814,
                  ""Disabled"": false,
                  ""Output"": [""t1.way"", ""t1.ctid"", ""t1.objectid""],
                  ""Index Cond"": ""(t1.objectid =
landcover_grassy_small_scale_2_ply_pg.objectid)""
                }
              ]
            },
            {
              ""Node Type"": ""Index Scan"",
              ""Parent Relationship"": ""Inner"",
              ""Parallel Aware"": false,
              ""Async Capable"": false,
              ""Scan Direction"": ""Forward"",
              ""Index Name"": ""landcover_grassy_small_scale_2_ply_pkey"",
              ""Relation Name"": ""landcover_grassy_small_scale_2_ply"",
              ""Schema"": ""osm"",
              ""Alias"": ""landcover_grassy_small_scale_2_ply"",
              ""Startup Cost"": 0.42,
              ""Total Cost"": 0.64,
              ""Plan Rows"": 1,
              ""Plan Width"": 814,
              ""Disabled"": false,
              ""Output"": [""landcover_grassy_small_scale_2_ply.way"",
""landcover_grassy_small_scale_2_ply.ctid"",
""landcover_grassy_small_scale_2_ply.objectid""],
              ""Index Cond"":
""(landcover_grassy_small_scale_2_ply.objectid = t1.objectid)""
            }
          ]
        }
      ]
    }
  }
]"