Feedback-ID: i76984098:Fastmail
Message-ID: <7e0e289f-c92d-40cc-bf46-162d484a5df4@aklaver.com>
Date: Mon, 26 Aug 2024 15:10:06 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4
 for Query on information_schema Tables.
To: nikhil raj <nikhilraj474@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Cc: NIKITA PATEL <patelnikita1411@gmail.com>,
 Patel Khushbu <patelkhushbu2067@gmail.com>
References: <CALNXOfqDr5jVRZHThyXyNftDuezjxOJB1mqw=4x1T8YBibi6uw@mail.gmail.com>
 <CAG1ps1w61XD1LxUf3EBCZ=VR3S_orRj_dk6cxmO5KusBVyrgMg@mail.gmail.com>
 <CAAvG1pChk3W9MWkK-_JPyUdThSoJhv4MueH5i8cZS2T_HUP5dA@mail.gmail.com>
 <CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7e0e289f-c92d-40cc-bf46-162d484a5df4%40aklaver.com>
Precedence: bulk

On 8/26/24 14:49, nikhil raj wrote:
> Hi All,
> 
> I've encountered a noticeable difference in execution time and query 
> execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when 
> running a query on |information_schema| tables. Surprisingly, PostgreSQL 
> 16 is performing slower than PostgreSQL 13.

Did you run ANALYZE on the Postgres 16 instance?

> *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by 
> gcc 11.4.0, 64-bit)*
> Execution plan: PG13.14 Execution Plan 
> <https://explain.dalibo.com/plan/ag1a62a9d47dg29d>
> 
> *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by 
> gcc 11.4.0, 64-bit)*
> Execution plan: PG16.4 Execution Plan 
> <https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2>


Use:

https://explain.depesz.com/

It is easier to follow it's output.

> 
> 
> Has anyone else experienced similar behavior or could provide insights 
> into why PostgreSQL 16 might be slower for this query? Any advice or 
> suggestions for optimization would be greatly appreciated.

Yes when ANALYZE was not run on a new instance.

> 
> Thank you!
> 
> NOTE:-  PFA the raw file of explain and analyze below.
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com