Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmEgA-0004V5-3i for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 14:09:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmEg8-0007JF-V4 for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 14:09:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmEg8-0007J6-K5 for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 14:09:00 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pmEg6-002FCN-1w for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 14:09:00 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 33BE8taE143138; Tue, 11 Apr 2023 10:08:55 -0400 From: Tom Lane To: David Rowley cc: =?UTF-8?Q?Christian_Schr=C3=B6der?= , "pgsql-general@lists.postgresql.org" Subject: Re: Performance issue after migration from 9.4 to 15 In-reply-to: References: Comments: In-reply-to David Rowley message dated "Wed, 12 Apr 2023 01:13:55 +1200" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <143136.1681222135.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Tue, 11 Apr 2023 10:08:55 -0400 Message-ID: <143137.1681222135@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk David Rowley writes: > On Tue, 11 Apr 2023 at 23:03, Christian Schr=C3=B6der > wrote: >> Particularly interesting are the sequential scans. In the old plan, we = have node #21, which took 32 seconds. Almost all of the time goes into act= ual I/O. In the new plan, the corresponding node is #34. It took 55 second= s, but less than 1 second was actual I/O (because most of the data was alr= eady in memory). Why did this step still take about twice the time? > Perhaps your 15 server is under more load than 9.4 due to all > concurrent plans being slower from bad statistics? Load averages might > be a good indicator. (I assume the server is busy due to the "Workers > Launched": 0) I think the extra time is due to useless overhead from trying and failing to parallelize: the leader has to do all the work, but there's probably overhead added anyway. 9.4 of course knew nothing of parallelism. My guess is that the OP is trying to run with a large number of backends and has not raised the max number of parallel workers to match. It does look like the stats might need updating (since 9.4's rowcount estimate is OK and 15's less so) but that is not why we see "Workers planned: 2, Workers launched: 0". Either provision enough parallel workers to fix that, or disable parallelism. regards, tom lane