From: Murthy Nunna <mnunna@fnal.gov>
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Query Spins
Thread-Topic: Query Spins
Thread-Index: Adwa88pT2Ll1dpVhQ1KKx+v6nQ5rww==
Date: Mon, 1 Sep 2025 03:52:10 +0000
Message-ID: 
 <DM8PR09MB6677D376F84879E730AAB223B807A@DM8PR09MB6677.namprd09.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?oV7iyNS/W3aA9kHDP6QWX6CEu+qkZcB7RavTpypM3pCnX/Z9/KDO0NFOO+FB?=
 =?us-ascii?Q?x20h+433RaTZz0+QkuzvKRSROdsYcGi9B5hYctAakjyoTqeqFz6YvlsqOvdF?=
 =?us-ascii?Q?gB1V9Qj0FuQflSnQThT5Q9rGnn3GN7iydfLVEaIpEm62VGLboSCBON12gAZL?=
 =?us-ascii?Q?x5/sweMVWQAkKktn3i5wejbGtFeyAEq+9opDVXcHx4hqicFgyXBQffp8eC75?=
 =?us-ascii?Q?qIxczpEKYZJitw+p26O9jMPF6lFs2855sdggX8NN/KERymKJCICOag/sS0N9?=
 =?us-ascii?Q?4G41qGiyR/LHSuq/t9rhEPehng5+GohdG4x73sfFQp4cFaTL5Od13ai7mvn3?=
 =?us-ascii?Q?dsAlG3JwvBEbOYnHDefbLQ58QL2Hf3cIa2vUgBIObP1fUyf8uDpWmltGUlVU?=
 =?us-ascii?Q?5A9xE31yOrvlEbZJSORUlEDQkgV5WCCxNWMmRkDf0tE3POcC4Hbi0aLfaucN?=
 =?us-ascii?Q?/5FXs6wzbzQJAck2wSYKWcwCSmSVWOv8PP0YA0FHze3w4dmFV36378hwTctB?=
 =?us-ascii?Q?Rkw4NA9jb1WRdzeXqn1BV/BSqPZADIMUAOK5reFF9Qx89xAX7uRgxE8Ti4tU?=
 =?us-ascii?Q?8PS7o3jPWMDrQoNvclvcpo1Fev4GOfBuVGlyK26U5dIAJWSehVQ8N5rOxyJM?=
 =?us-ascii?Q?ATrTYK98AcMHNkcLbg/DMZldOts8DxWs9Sr901f4vVY+lUzACzZ1hTzJ9Obp?=
 =?us-ascii?Q?jzptZKcS8x8GFHJ0xIELgbM45L82aAECNfNwlVRDpTC1/PqKwqfZzXlkeKRh?=
 =?us-ascii?Q?SrZWf/B4JFNM61XNo20i42qyQbU4Fi0ztT9QKZb2UKonyHxzTLduJgNiS2Aa?=
 =?us-ascii?Q?DMg0yvyd1Fh8Bd7Lfip0XnxfnfghUidAhPfTbvw9Gf+oW6hXkZque0oTZqGg?=
 =?us-ascii?Q?3+OKQMWYhzlHGLzorNKUiY7fg2/kM9RMOv8mWN2f2c4FnFELSbOhArBX3aVw?=
 =?us-ascii?Q?KG/Z3HAHwKM2kXdULbkQNGDMA6yB5DWnbC6/kJEAxc5O51b6LqURD6n6twbH?=
 =?us-ascii?Q?Hte4oLTfBc1RGXBuO/BlseAkpMAikoQPYTtlNqdWwAwPj9wK9Bdzw7rmjRUa?=
 =?us-ascii?Q?oXH60yNkC0A3vp9DEMg7dtuG5ttG4045kajP9oZB9yc4VUQzf3mMxZqPzT2X?=
 =?us-ascii?Q?DgURFI0IWrY/0iV4yZDd1nHib/SGDfpKWK1NGyXrT1obXlGoj32kg07Q30iO?=
 =?us-ascii?Q?+uDZVldyzmInGjXSh8PWnAYCW/kTx9kimFWcTzza5FhAkO536hHbOPVOsgQH?=
 =?us-ascii?Q?sSTp706OdO8qUqLcw+O5k1Z/fT7mFeDuohSxg0R1k/WRmDWyPoLwjY4IbUXK?=
 =?us-ascii?Q?tIzQyByYE+VQzsT1U086aAHeXX9VWdShG9JuWt10apA0mgOQo5K6BgSIpsHb?=
 =?us-ascii?Q?BGlvrjlAttrzBSDRlJtgJ/siCg4euauQ1cE33wGWZCnCwnIgfmi2XFRiADM0?=
 =?us-ascii?Q?a9gKAvPNwFRZ4qW5dXgScOjJtfZUIgXOQqjZ+9fP44SJBFJBgNqYYX3EXZtd?=
 =?us-ascii?Q?nqFwnQZWSmFxRCTsXck+d2nxl6rcLMovca4YLxlHAZKHCFVZNU7PEJPzxavu?=
 =?us-ascii?Q?1aoDyb6LAbTP4netb3if5SnSxDowGFMOv0y1eR2P?=
Content-Type: multipart/alternative;
	boundary="_000_DM8PR09MB6677D376F84879E730AAB223B807ADM8PR09MB6677namp_"
MIME-Version: 1.0
X-OriginatorOrg: fnal.gov
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DM8PR09MB6677.namprd09.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 ebd0dc60-6177-46b8-979d-08dde90aeda3
X-MS-Exchange-CrossTenant-originalarrivaltime: 01 Sep 2025 03:52:10.2291
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a
X-MS-Exchange-Transport-CrossTenantHeadersStamped: MW4PR09MB9329
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/DM8PR09MB6677D376F84879E730AAB223B807A%40DM8PR09MB6677.namprd09.prod.outlook.com>
Precedence: bulk

--_000_DM8PR09MB6677D376F84879E730AAB223B807ADM8PR09MB6677namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

Pg16.10


I have a query which runs fine most of the time. When it runs fine, it spaw=
ns parallel workers. In pg_stat_activity, wait_event is blank, state is act=
ive and backend_type =3D "client backend" for the main query. For parallel =
workers of this query I see wait_event =3D MessageQueueSend, state is activ=
e and backend_type =3D "parallel worker"

But some times, it has no parallel workers. wait_event is blank, state is a=
ctive and backend_type =3Dclient backend. And it never ends. It takes up lo=
t of CPU. The socket on both server and client server are in ESTABLISHED st=
ate (netstat -tulpa | grep <client_port>).

I appreciate any help you can provide.


--_000_DM8PR09MB6677D376F84879E730AAB223B807ADM8PR09MB6677namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:12.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
	{mso-style-priority:99;
	mso-style-link:"Plain Text Char";
	margin:0in;
	font-size:12.0pt;
	font-family:"Calibri",sans-serif;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;
	font-weight:normal;
	font-style:normal;}
span.PlainTextChar
	{mso-style-name:"Plain Text Char";
	mso-style-priority:99;
	mso-style-link:"Plain Text";
	font-family:"Calibri",sans-serif;
	mso-ligatures:none;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hi,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Pg16.10<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoPlainText">I have a query which runs fine most of the time. =
When it runs fine, it spawns parallel workers. In pg_stat_activity, wait_ev=
ent is blank, state is active and backend_type =3D &quot;client backend&quo=
t; for the main query. For parallel workers of
 this query I see wait_event =3D MessageQueueSend, state is active and back=
end_type =3D &quot;parallel worker&quot;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">But some times, it has no parallel workers. wait_eve=
nt is blank, state is active and backend_type =3Dclient backend. And it nev=
er ends. It takes up lot of CPU. The socket on both server and client serve=
r are in ESTABLISHED state (netstat
 -tulpa | grep &lt;client_port&gt;).<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I appreciate any help you can provide.<o:p></o:p></p=
>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_DM8PR09MB6677D376F84879E730AAB223B807ADM8PR09MB6677namp_--