Horton

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

Adrian Klaver <adrian.klaver@aklaver.com>

View thread View on postgr.es
From: Adrian Klaver <adrian.klaver@aklaver.com>
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>
Date: 2024-08-26T22:10:06Z
Lists: pgsql-hackers, pgsql-general 
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

Commits

  1. Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not.

  2. Remove one memoize test case added by commit 069d0ff02.