The following bug has been logged on the website:
Bug reference: 17999
Logged by: Serge Bellina
Email address: ess.bee59@gmail.com
PostgreSQL version: 14.8
Operating system: Ubuntu-2204-jammy-amd64-base
Description:
Hello,
Sorry, I reported last week my first bug to postgresql (using my guthub
account), but I do not find the way to edit the bug created (17990)
(could you please help me to become able to edit the bug?)
So below my response to thomas munro:
Hello Thomas, thank for your response.
1-The explain to the SQL
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=337741901772.50..337742103465.40 rows=80677160 width=1020)
Sort Key: ((st_area(st_intersection(m.way, st_union(q.way), '-1'::double
precision)) / st_area(m.way))) DESC
-> GroupAggregate (cost=302522177901.86..337695183065.46 rows=80677160
width=1020)
Group Key: m.osm_id, m.highway, m.way
-> Gather Merge (cost=302522177901.86..317079159018.69
rows=124988590885 width=1675)
Workers Planned: 2
-> Sort (cost=302522176901.84..302652373350.68
rows=52078579535 width=1675)
Sort Key: m.osm_id, m.highway, m.way
-> Nested Loop (cost=0.42..217422937427.56
rows=52078579535 width=1675)
-> Parallel Seq Scan on osm_poly_no_buf q
(cost=0.00..223957561.03 rows=939141 width=663)
Filter: (((landuse = ANY
('{forest,allotments,flowerbed,orchard,vineyard,recreation_ground,village_green}'::text[]))
OR (leisure = ANY ('{garden,park,nature_reserve}'::text[]))) AND
((st_area((st_transform(way, 4326))::geography, true) / '1000000'::double
precision) < '5000'::double precision))
-> Index Scan using osm_line_buf_50_idx on
osm_line_buf_50 m (cost=0.42..231193.41 rows=8068 width=1012)
Index Cond: (way && q.way)
Filter: ((highway IS NOT NULL) AND
st_intersects(way, q.way))
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming
true
(17 rows)
2-cancel request:
I do not exactly remember what the process did after my cancel request (I
had possibly to restart the DB)
But my problem is, the SQL did not end after hours!
The CPU in the last test was 100% (as seen in the top utility)
Sorry, I was not prepared to examine the situation, later I found some
commands to get the pg_stat_activity.
(I am not expert on Linux and prefer not to start debuger or such utilities)
So, I will start a retest this week.
regards