Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

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