MIME-Version: 1.0
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> <7e0e289f-c92d-40cc-bf46-162d484a5df4@aklaver.com>
In-Reply-To: <7e0e289f-c92d-40cc-bf46-162d484a5df4@aklaver.com>
From: nikhil raj <nikhilraj474@gmail.com>
Date: Tue, 27 Aug 2024 04:11:36 +0530
Message-ID: <CAG1ps1zFv7gHc1-7dxXfFeNvoaENW5WDJ_DrJsZNJk72HvEABw@mail.gmail.com>
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4
 for Query on information_schema Tables.
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>, 
	NIKITA PATEL <patelnikita1411@gmail.com>, Patel Khushbu <patelkhushbu2067@gmail.com>
Content-Type: multipart/alternative; boundary="0000000000009252f906209dd415"
Archived-At: <https://www.postgresql.org/message-id/CAG1ps1zFv7gHc1-7dxXfFeNvoaENW5WDJ_DrJsZNJk72HvEABw%40mail.gmail.com>
Precedence: bulk

--0000000000009252f906209dd415
Content-Type: text/plain; charset="UTF-8"

Hi Adrian,

Thanks for the quick response.

I've already performed a vacuum, reindex, and analyze on the entire
database, but the issue persists. As you can see from the execution plan,
the time difference in PostgreSQL 16 is still significantly higher, even
after all maintenance activities have been completed.
It seems there might be a bug in PostgreSQL 16 where the performance of
queries on *information_schema* tables is degraded. As both the tables are
postgres system tables

https://explain.depesz.com/s/bdO6b  :-PG13
<https://explain.depesz.com/s/bdO6b>
https://explain.depesz.com/s/bpAU  :- PG16
<https://explain.depesz.com/s/bpAU>

On Tue 27 Aug, 2024, 3:40 AM Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

> 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
>
>

--0000000000009252f906209dd415
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><p dir=3D"ltr">Hi Adrian,</p><div style=3D"" dir=3D"auto"=
><p>Thanks for the quick response.</p><p>I&#39;ve already performed a vacuu=
m, reindex, and analyze on the entire database, but the issue persists. As =
you can see from the execution plan, the time difference in PostgreSQL 16 i=
s still significantly higher, even after all maintenance activities have be=
en completed.</p></div><div style=3D"" dir=3D"auto">It seems there might be=
 a bug in PostgreSQL 16 where the performance of queries on=C2=A0<b>informa=
tion_schema</b>=C2=A0tables is degraded. As both the tables are postgres sy=
stem tables=C2=A0</div><p dir=3D"ltr"><a href=3D"https://explain.depesz.com=
/s/bdO6b" style=3D"">https://explain.depesz.com/s/bdO6b</a>=C2=A0 :-<a href=
=3D"https://explain.depesz.com/s/bdO6b" style=3D"">PG13</a></p><div style=
=3D"" dir=3D"auto"><a href=3D"https://explain.depesz.com/s/bpAU" style=3D""=
>https://explain.depesz.com/s/bpAU</a>=C2=A0 :-=C2=A0<a href=3D"https://exp=
lain.depesz.com/s/bpAU" style=3D"">PG16</a></div></div><br><div class=3D"gm=
ail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue 27 Aug, 2024, 3:40 =
AM Adrian Klaver, &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.k=
laver@aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On=
 8/26/24 14:49, nikhil raj wrote:<br>
&gt; Hi All,<br>
&gt; <br>
&gt; I&#39;ve encountered a noticeable difference in execution time and que=
ry <br>
&gt; execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when=
 <br>
&gt; running a query on |information_schema| tables. Surprisingly, PostgreS=
QL <br>
&gt; 16 is performing slower than PostgreSQL 13.<br>
<br>
Did you run ANALYZE on the Postgres 16 instance?<br>
<br>
&gt; *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled =
by <br>
&gt; gcc 11.4.0, 64-bit)*<br>
&gt; Execution plan: PG13.14 Execution Plan <br>
&gt; &lt;<a href=3D"https://explain.dalibo.com/plan/ag1a62a9d47dg29d" rel=
=3D"noreferrer noreferrer" target=3D"_blank">https://explain.dalibo.com/pla=
n/ag1a62a9d47dg29d</a>&gt;<br>
&gt; <br>
&gt; *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by =
<br>
&gt; gcc 11.4.0, 64-bit)*<br>
&gt; Execution plan: PG16.4 Execution Plan <br>
&gt; &lt;<a href=3D"https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2" rel=
=3D"noreferrer noreferrer" target=3D"_blank">https://explain.dalibo.com/pla=
n/4c66fdfbf2hf9ed2</a>&gt;<br>
<br>
<br>
Use:<br>
<br>
<a href=3D"https://explain.depesz.com/" rel=3D"noreferrer noreferrer" targe=
t=3D"_blank">https://explain.depesz.com/</a><br>
<br>
It is easier to follow it&#39;s output.<br>
<br>
&gt; <br>
&gt; <br>
&gt; Has anyone else experienced similar behavior or could provide insights=
 <br>
&gt; into why PostgreSQL 16 might be slower for this query? Any advice or <=
br>
&gt; suggestions for optimization would be greatly appreciated.<br>
<br>
Yes when ANALYZE was not run on a new instance.<br>
<br>
&gt; <br>
&gt; Thank you!<br>
&gt; <br>
&gt; NOTE:-=C2=A0 PFA the raw file of explain and analyze below.<br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; <br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank" rel=3D"noref=
errer">adrian.klaver@aklaver.com</a><br>
<br>
</blockquote></div>

--0000000000009252f906209dd415--