From: | nikhil raj <nikhilraj474(at)gmail(dot)com> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Cc: | NIKITA PATEL <patelnikita1411(at)gmail(dot)com>, Patel Khushbu <patelkhushbu2067(at)gmail(dot)com> |
Subject: | Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. |
Date: | 2024-08-26 21:49:04 |
Message-ID: | CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
The query executed on both versions is as follows:
SELECT DISTINCT "tc"."constraint_name" AS "ConstraintName",
"ccu"."column_name" AS "ColumnName"
FROM
information_schema.constraint_column_usage AS "ccu" right join
information_schema.table_constraints AS "tc"
ON "tc"."constraint_catalog" = "ccu"."constraint_catalog"
AND "tc"."constraint_name" = "ccu"."constraint_name"
WHERE "tc"."constraint_type" = 'PRIMARY KEY'
AND "ccu"."table_name" = 't_c56ng1_repository'
Here are the details of the PostgreSQL versions and the execution plans:
*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>
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.
Thank you!
NOTE:- PFA the raw file of explain and analyze below.
Attachment | Content-Type | Size |
---|---|---|
PG13_32644.txt | text/plain | 80.1 KB |
PG16_32644.txt | text/plain | 92.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Travers | 2024-08-26 22:09:30 | Code of Conduct Committee Volunteer Drive |
Previous Message | Chris Travers | 2024-08-26 21:04:24 | Postgresql Code of Conduct Committee Update |
From | Date | Subject | |
---|---|---|---|
Next Message | Matthias van de Meent | 2024-08-26 21:59:28 | Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM |
Previous Message | Craig Ringer | 2024-08-26 21:35:43 | Re: RFC: Additional Directory for Extensions |