pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to tune SQL performance of function based columns of a view
11+ messages / 5 participants
[nested] [flat]
* How to tune SQL performance of function based columns of a view
@ 2024-04-06 04:33 M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-08 09:56 ` Re: How to tune SQL performance of function based columns of a view Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 11+ messages in thread
From: M Sarwar @ 2024-04-06 04:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
1. I can not create indexes on view columns which is a restriction on the views.
2. 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.
3. 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
4. I am attaching Explain Analyze results for this SQL in an excel file, Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx
5.
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
Attachments:
[application/octet-stream] vw_tab_mcm_net_temp_yield_mid.SQL (5.6K, ../../DM4PR19MB597881373C3C734258573A11D3022@DM4PR19MB5978.namprd19.prod.outlook.com/3-vw_tab_mcm_net_temp_yield_mid.SQL)
download
[application/octet-stream] vw_tab_mcm_test_results_mid.SQL (1.9K, ../../DM4PR19MB597881373C3C734258573A11D3022@DM4PR19MB5978.namprd19.prod.outlook.com/4-vw_tab_mcm_test_results_mid.SQL)
download
[application/vnd.openxmlformats-officedocument.spreadsheetml.sheet] Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx (12.2K, ../../DM4PR19MB597881373C3C734258573A11D3022@DM4PR19MB5978.namprd19.prod.outlook.com/5-Explain-Analyze-Net-Temps-Slow-Response-Bao.xlsx)
download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
@ 2024-04-06 05:14 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2024-04-06 05:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-04-06 08:44 ` M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: M Sarwar @ 2024-04-06 08:44 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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)
To: pgsql-admin@lists.postgresql.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@outlook.com<mailto: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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
@ 2024-04-06 13:58 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 20:25 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-08 04:51 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
0 siblings, 2 replies; 11+ messages in thread
From: Ron Johnson @ 2024-04-06 13:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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)
> To: pgsql-admin@lists.postgresql.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@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?
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-04-06 20:25 ` M Sarwar <sarwarmd02@outlook.com>
1 sibling, 0 replies; 11+ messages in thread
From: M Sarwar @ 2024-04-06 20:25 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Ron,
Yes, it is identified that it was missing stats due to missing analysis. Last_analyzed column was null.
Default parameters Postgres are analyzing but they are slow. I need to step up at least analyzing if not Vacuuming.
We do have scheduled loading and cleaning jobs which are going to impact the analysis of the tables.
Thank you!
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<mailto: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<mailto:ronljohnsonjr@gmail.com>>
Date: 4/6/24 1:15 AM (GMT-05:00)
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.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@outlook.com<mailto: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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-04-08 04:51 ` M Sarwar <sarwarmd02@outlook.com>
2024-04-08 06:38 ` Re: How to tune SQL performance of function based columns of a view Sai Sadashiva Kundurmutt <saisadashiva2000@gmail.com>
2024-04-08 13:30 ` RE: How to tune SQL performance of function based columns of a view Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
1 sibling, 2 replies; 11+ messages in thread
From: M Sarwar @ 2024-04-08 04:51 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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<mailto:ronljohnsonjr@gmail.com>>
Date: 4/6/24 1:15 AM (GMT-05:00)
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.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@outlook.com<mailto: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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-08 04:51 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
@ 2024-04-08 06:38 ` Sai Sadashiva Kundurmutt <saisadashiva2000@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Sai Sadashiva Kundurmutt @ 2024-04-08 06:38 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello,
I am relatively new to Postgres but this is my suggestion: Try to add an
index on the column *substring"(p.SERIAL_NUMBER::TEXT, '[A-Z][0-9]+'::TEXT)
*?
Also, analyze the table after adding the index. If index scan is chosen
after this performance might improve(I tried to experiment by creating a
table with a text field in it and running a similar query on it).
Thank you,
Warm regards,
Sai Sadashiva JK
On Mon, 8 Apr 2024 at 10:22, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> 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)
> To: pgsql-admin@lists.postgresql.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@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?
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-08 04:51 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
@ 2024-04-08 13:30 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
2024-04-08 22:26 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 11+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-04-08 13:30 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:ronljohnsonjr@gmail.com>>
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto: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<mailto: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<mailto:ronljohnsonjr@gmail.com>>
Date: 4/6/24 1:15 AM (GMT-05:00)
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.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@outlook.com<mailto: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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-08 04:51 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-08 13:30 ` RE: How to tune SQL performance of function based columns of a view Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
@ 2024-04-08 22:26 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 11+ messages in thread
From: M Sarwar @ 2024-04-08 22:26 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>; Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.Wetmore@evernorth.com>
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<mailto:ronljohnsonjr@gmail.com>>
Sent: Saturday, April 6, 2024 9:58 AM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto: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<mailto: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<mailto:ronljohnsonjr@gmail.com>>
Date: 4/6/24 1:15 AM (GMT-05:00)
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.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@outlook.com<mailto: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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
@ 2024-04-08 09:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-08 22:28 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2024-04-08 09:56 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to tune SQL performance of function based columns of a view
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-08 09:56 ` Re: How to tune SQL performance of function based columns of a view Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-04-08 22:28 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 11+ messages in thread
From: M Sarwar @ 2024-04-08 22:28 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Lauren,
I thought that I have tried this option but I am try this option another time.
Thanks,
Sarwar
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
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
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-04-08 22:28 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-06 04:33 How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 05:14 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 08:44 ` RE: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-06 13:58 ` Re: How to tune SQL performance of function based columns of a view Ron Johnson <ronljohnsonjr@gmail.com>
2024-04-06 20:25 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-08 04:51 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-08 06:38 ` Re: How to tune SQL performance of function based columns of a view Sai Sadashiva Kundurmutt <saisadashiva2000@gmail.com>
2024-04-08 13:30 ` RE: How to tune SQL performance of function based columns of a view Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
2024-04-08 22:26 ` Re: How to tune SQL performance of function based columns of a view M Sarwar <sarwarmd02@outlook.com>
2024-04-08 09:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-08 22:28 ` M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox