Обсуждение: [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.

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

TEK_INSPECTION_LIST_MCM

TEST_PART_DETAILS_ALL_MCM_MID

I am still seeing unacceptable response time.

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?

 

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