Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qgnow-00HQ0L-Uq for pgsql-general@arkaria.postgresql.org; Thu, 14 Sep 2023 14:59:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qgnov-0096EK-Nz for pgsql-general@arkaria.postgresql.org; Thu, 14 Sep 2023 14:59:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qgnot-0096Dl-KS for pgsql-general@lists.postgresql.org; Thu, 14 Sep 2023 14:59:53 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qgnop-0053g7-2T for pgsql-general@lists.postgresql.org; Thu, 14 Sep 2023 14:59:50 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 83C2B5C0180; Thu, 14 Sep 2023 10:59:44 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Thu, 14 Sep 2023 10:59:44 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1694703584; x=1694789984; bh=DK06yxtMgxw6Uc/pnt12t4W4UBpeB9m6xJs FgLcZrwU=; b=popY1ldi/2rby0t9H2LaB0V9w5g/t2AbSFBl5vl1tgayquxsZak Tg5i3i/LSPWUzm1x0cWj6+EXR7OON1j9A7IXYfyehlf5ifFOA44KoUJ5xWsscg5P JC74/ZjT+/HZmqnYSaJblloFzeQGvNon8dFFxfgoS07WPXFEeqzAstsj1eUIW2sN YnE+1ckFAlOrAhKXsYAzn3/QiTobrqlXa8dN8bmxz44MLN4rSrT4RI9DRH46WJJ6 nq8uIY92CuzjDLVyLxIY3GDpvQHkRBYXefybbgMSR7KBlPCwPpAnuN61NZBA+L0G +GtCRH1qrmuRfBh07UUT9d8/cKTF36vjLVA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1694703584; x= 1694789984; bh=DK06yxtMgxw6Uc/pnt12t4W4UBpeB9m6xJsFgLcZrwU=; b=M toeuLCa7539Cphon5eqwmSIX9HMxU+RGNANeV43oHYODOW5ZDS6l019qrJJl75Ky Crib1Hp4F75GTnZKkJ9mNZ4GH0Vm3Y/YjSwMmJ8MOhQpI9XIcg+70h3sJ+CckAlD B5sJplUb3q9+6Ls9yYr+2psSsIXLhcgt9clFX4uD4pLkyFzF5TG7z6q/F2rxtSeY ZP+e5jhSkEjHfrCESml4QC7m4Ob7cNUfHbnIilOydeiYlL3tfrij8VMvmnVDhMAU 5ckGDugbqy0+9BsubJ1JBy9L7bHc+QX1ZFpRoJmcBlHwc5sjMbtTfFr+uIvbGCLi 1WyeQy0247G4i/JbegUOg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudejtddgkeduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthejredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeevvdekiefhudfhhfdtkeelgeffudeigfetjeegfffg hfetfeelledugfelleegveenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 14 Sep 2023 10:59:43 -0400 (EDT) Message-ID: <9da2d3e1-708d-ba92-cdec-96f3bddec397@aklaver.com> Date: Thu, 14 Sep 2023 07:59:42 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Subject: Re: Access plan selection logic PG9.2 -> PG14 To: "Ryo Yamaji (Fujitsu)" , "pgsql-general@lists.postgresql.org" References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 9/14/23 00:15, Ryo Yamaji (Fujitsu) wrote: > > Hi all > > I am planning to migrate from PostgreSQL9.2 to PostgreSQL14.7. > Running the same SQL with the same statistics as previous versions resulted in different access plans. > I have no knowledge of the PostgreSQL planner and would like to be informed. Please assist. > > Behavior: > I am using an environment in which the statistics have been manually updated using pg_dbms_stats. (*) Why not just run ANALYZE on the new 14.7 instance? > In PostgreSQL9.2, the primary key was selected when a SELECT was executed with all columns of the primary key specified as equals in the where clause. -- Adrian Klaver adrian.klaver@aklaver.com