On 8/26/24 15:41, nikhil raj wrote:
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 <https://explain.depesz.com/s/bdO6b>
:-PG13 <https://explain.depesz.com/s/bdO6b>
https://explain.depesz.com/s/bpAU <https://explain.depesz.com/s/bpAU>
:- PG16 <https://explain.depesz.com/s/bpAU>
What I see is Postgres 13:
Nested Loop (cost=9.54..119.02 rows=1 width=128) (actual
time=1.038..288.777 rows=1 loops=1)
Join Filter: (("*SELECT* 1".constraint_name)::name = "*SELECT*
1_1".conname)
Rows Removed by Join Filter: 935
Buffers: shared hit=34,675
vs Postgres 16
Nested Loop (cost=62.84..538.22 rows=1 width=128) (actual
time=1,905.153..14,006.921 rows=1 loops=1)
Join Filter: ("*SELECT* 1".conname = ("*SELECT*
1_1".constraint_name)::name)
Rows Removed by Join Filter: 997
Buffers: shared hit=5,153,054
So either switching this
("*SELECT* 1".constraint_name)::name = "*SELECT* 1_1".conname
to
"*SELECT* 1".conname = ("*SELECT* 1_1".constraint_name)::name
is more of a change then I would expect.
Or
Buffers: shared hit=34,675
vs
Buffers: shared hit=5,153,054
indicates a hardware/configuration difference.
Are both instances running on the same machine?
Is the configuration for both the same?
On Tue 27 Aug, 2024, 3:40 AM Adrian Klaver, <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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
<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
<https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2>>
Use:
https://explain.depesz.com/ <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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
