From: | M Sarwar <sarwarmd02(at)outlook(dot)com> |
---|---|
To: | "Wetmore, Matthew (CTR)" <Matthew(dot)Wetmore(at)evernorth(dot)com>, Ron Johnson <ronljohnsonjr(at)gmail(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to tune SQL performance of function based columns of a view |
Date: | 2024-04-08 22:26:55 |
Message-ID: | DM4PR19MB59783B35AA3FF324DF1CB616D3002@DM4PR19MB5978.namprd19.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi Matthew,
I was thinking of if that is the only option.
At this time, I can not make any changes to of the existing database objects. I can keep in mind for the future point of view.
Thank you.
Sarwar
________________________________
From: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)evernorth(dot)com>
Sent: Monday, April 8, 2024 9:30 AM
To: M Sarwar <sarwarmd02(at)outlook(dot)com>; Ron Johnson <ronljohnsonjr(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: RE: How to tune SQL performance of function based columns of a view
Make it a Materialized View with storage and you can index on a column. You can index a MV unlike a regular view.
From: M Sarwar <sarwarmd02(at)outlook(dot)com>
Sent: Sunday, April 7, 2024 9:52 PM
To: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL] Re: How to tune SQL performance of function based columns of a view
Hi Ron,
I have analyzed and vacuumed following 2 tables which are used by the views.
TEK_INSPECTION_LIST_MCM
TEST_PART_DETAILS_ALL_MCM_MID
I am still seeing unacceptable response time.
Is there any other way to tune a SQL which is referring a view?
Thanks,
Sarwar
________________________________
From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com<mailto:ronljohnsonjr(at)gmail(dot)com>>
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org> <pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>>
Subject: Re: How to tune SQL performance of function based columns of a view
Postgresql does auto-vacuum and auto-analyze, so the table might have been analyzed and vacuumed.
Table pg_stat_user_tables records the last time that user tables were vacuumed and analyzed.
On Sat, Apr 6, 2024 at 4:44 AM M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>> wrote:
This is Pg 13.
It is running on Aws / Rds.
I am not doing any Vacuum/ Analyze manually.
Thanks,
Sarwar
Sent from my Galaxy
-------- Original message --------
From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com<mailto:ronljohnsonjr(at)gmail(dot)com>>
Date: 4/6/24 1:15 AM (GMT-05:00)
To: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: How to tune SQL performance of function based columns of a view
On Sat, Apr 6, 2024 at 12:33 AM M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>> wrote:
Hello,
I am working on SQL performance issue.
1. What PG version?
2. When did you last VACUUM and ANALYZE the base tables?
From | Date | Subject | |
---|---|---|---|
Next Message | M Sarwar | 2024-04-08 22:28:39 | Re: How to tune SQL performance of function based columns of a view |
Previous Message | Rajesh Kumar | 2024-04-08 17:56:53 | Re: Work_mem |