Thread: [MASSMAIL]How to tune SQL performance of function based columns of a view
Hello,
I am working on SQL performance issue.
Here is my SQL.
SELECT STAGE AS STAGE__C,
NET_TEMP_YIELD AS NET_TEMP_YIELD__C,
LATEST_SYSTEM_ID_PER_STAGE AS LATEST_SYSTEM_ID__C,
HIGHEST_TEMP_TESTED AS HIGHEST_TEMP_TESTED__C,
PASSED_ALL_TEMPS AS PASSED_ALL_TEMPS__C,
NUM_TEMPS_TESTED AS NUM_TEMPS_TESTED__C,
NUM_REQUIRED_TEMPS AS NUM_REQUIRED_TEMPS__C,
MCM_ID AS MCM_ID__C
FROM BRONX.VW_TAB_MCM_NET_TEMP_YIELD_MID
where MCM_ID in
(
'B70725Z2','B7072Z76','B7072Z80','B7072Z81'
)
;
BRONX.VW_TAB_MCM_NET_TEMP_YIELD_MID is a view. I am attaching the code of BRONX.VW_TAB_MCM_NET_TEMP_YIELD_MID if anyone can take a look.
View, VW_TAB_MCM_NET_TEMP_YIELD_MID calls another view called VW_TAB_MCM_TEST_RESULTS_MID.SQL. I am attaching this code as well.
- I can not create indexes on view columns which is a restriction on the views.
- When I am using constant values for MCM_IDs as stated in the SQL, query performance is going down by 20 times which is unacceptable.
- Column MCM_ID is a function column "substring"(p.SERIAL_NUMBER::TEXT, '[A-Z][0-9]+'::TEXT) AS MCM_ID from the view, VW_TAB_MCM_TEST_RESULTS_MID.SQL
- I am attaching Explain Analyze results for this SQL in an excel file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx
After analyzing the results from the file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx, I tried to create several indexes one by one based on the possibility from Explain analyze results and none of them are helping in improving the performance of SQL.
I am just trying to know what are the other approaches which I can use to resolve this performance issue.
Thank you,
Sarwar
Attachment
Hello,
I am working on SQL performance issue.
Hello,
I am working on SQL performance issue.
This is Pg 13.It is running on Aws / Rds.I am not doing any Vacuum/ Analyze manually.Thanks,SarwarSent from my Galaxy-------- Original message --------From: Ron Johnson <ronljohnsonjr@gmail.com>Date: 4/6/24 1:15 AM (GMT-05:00)Subject: Re: How to tune SQL performance of function based columns of a viewOn Sat, Apr 6, 2024 at 12:33 AM M Sarwar <sarwarmd02@outlook.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?
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to tune SQL performance of function based columns of a view
This is Pg 13.It is running on Aws / Rds.I am not doing any Vacuum/ Analyze manually.Thanks,SarwarSent from my Galaxy-------- Original message --------From: Ron Johnson <ronljohnsonjr@gmail.com>Date: 4/6/24 1:15 AM (GMT-05:00)Subject: Re: How to tune SQL performance of function based columns of a viewOn Sat, Apr 6, 2024 at 12:33 AM M Sarwar <sarwarmd02@outlook.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?
TEK_INSPECTION_LIST_MCM
TEST_PART_DETAILS_ALL_MCM_MID
I am still seeing unacceptable response time.
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to tune SQL performance of function based columns of a view
This is Pg 13.It is running on Aws / Rds.I am not doing any Vacuum/ Analyze manually.Thanks,SarwarSent from my Galaxy-------- Original message --------From: Ron Johnson <ronljohnsonjr@gmail.com>Date: 4/6/24 1:15 AM (GMT-05:00)Subject: Re: How to tune SQL performance of function based columns of a viewOn Sat, Apr 6, 2024 at 12:33 AM M Sarwar <sarwarmd02@outlook.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?
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@gmail.com>
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to tune SQL performance of function based columns of a viewPostgresql 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@outlook.com> wrote:This is Pg 13.It is running on Aws / Rds.I am not doing any Vacuum/ Analyze manually.Thanks,SarwarSent from my Galaxy-------- Original message --------From: Ron Johnson <ronljohnsonjr@gmail.com>Date: 4/6/24 1:15 AM (GMT-05:00)Subject: Re: How to tune SQL performance of function based columns of a viewOn Sat, Apr 6, 2024 at 12:33 AM M Sarwar <sarwarmd02@outlook.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?
On Sat, 2024-04-06 at 04:33 +0000, M Sarwar wrote: > I am working on SQL performance issue. > > After analyzing the results from the file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx, > I tried to create several indexes one by one based on the possibility from Explain analyze > results and none of them are helping in improving the performance of SQL. > I am just trying to know what are the other approaches which I can use to resolve this performance issue. According to my reading of the execution plan, the following index should help a lot: CREATE INDEX ON test_part_details_all_mcm_mid (serial_number, stage, temperature); There might be more gains in rewriting whatever caused this subquery. Yours, Laurenz Albe
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@outlook.com>
Sent: Sunday, April 7, 2024 9:52 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.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@gmail.com>
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.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@outlook.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@gmail.com>
Date: 4/6/24 1:15 AM (GMT-05:00)
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@outlook.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?
Sent: Monday, April 8, 2024 9:30 AM
To: M Sarwar <sarwarmd02@outlook.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.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@outlook.com>
Sent: Sunday, April 7, 2024 9:52 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.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@gmail.com>
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.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@outlook.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@gmail.com>
Date: 4/6/24 1:15 AM (GMT-05:00)
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@outlook.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?
Sent: Monday, April 8, 2024 5:56 AM
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to tune SQL performance of function based columns of a view
> I am working on SQL performance issue.
>
> After analyzing the results from the file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx,
> I tried to create several indexes one by one based on the possibility from Explain analyze
> results and none of them are helping in improving the performance of SQL.
> I am just trying to know what are the other approaches which I can use to resolve this performance issue.
According to my reading of the execution plan, the following index should help a lot:
CREATE INDEX ON test_part_details_all_mcm_mid (serial_number, stage, temperature);
There might be more gains in rewriting whatever caused this subquery.
Yours,
Laurenz Albe