Re: How to tune SQL performance of function based columns of a view

Поиск
Список
Период
Сортировка
От Sai Sadashiva Kundurmutt
Тема Re: How to tune SQL performance of function based columns of a view
Дата
Msg-id CAH6hFGLbt4OCqzJB0M5fgx30FFQC4=1pCEHF5JLapn8J1ROavw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: How to tune SQL performance of function based columns of a view  (M Sarwar <sarwarmd02@outlook.com>)
Список pgsql-admin
Дерево обсуждения
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)
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?
 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Siraj G
Дата:
Сообщение: Enable DB level audit trail
Следующее
От: Rajesh Kumar
Дата:
Сообщение: [MASSMAIL]Work_mem