From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: ess.bee59@gmail.com
Subject: BUG #17999: comment to BUG 17990 (PSQL Process hangs in parallel mode)
Date: Mon, 26 Jun 2023 07:06:07 +0000
Message-ID: <17999-a83f0f7060beabda@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ess.bee59@gmail.com
Subject: Re: BUG #17999: comment to BUG 17990 (PSQL Process hangs in parallel mode)
In-Reply-To: <17999-a83f0f7060beabda@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox