| From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
|---|---|
| To: | nikhil raj <nikhilraj474(at)gmail(dot)com>, "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: | Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. |
| Date: | 2024-08-26 22:10:06 |
| Message-ID: | 7e0e289f-c92d-40cc-bf46-162d484a5df4@aklaver.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general pgsql-hackers |
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:
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(dot)klaver(at)aklaver(dot)com
| From | Date | Subject | |
|---|---|---|---|
| Next Message | nikhil raj | 2024-08-26 22:41:36 | Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. |
| Previous Message | Chris Travers | 2024-08-26 22:09:30 | Code of Conduct Committee Volunteer Drive |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | nikhil raj | 2024-08-26 22:41:36 | Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. |
| Previous 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 |