From: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
To: M Sarwar <sarwarmd02@outlook.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
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
Date: Mon, 8 Apr 2024 13:30:33 +0000
Message-ID: <e4a9a13d0d3d4c838f073f0634bead83@evernorth.com> (raw)
In-Reply-To: <DM4PR19MB5978678C4388CCC691EB705CD3002@DM4PR19MB5978.namprd19.prod.outlook.com>
References: <PH2P110MB0922A59A7B8B36839227589CFF02A@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
<DM4PR19MB597881373C3C734258573A11D3022@DM4PR19MB5978.namprd19.prod.outlook.com>
<CANzqJaAGQ_QPt5HGPrKbpQSTqHep7oqS14v1Q+hT8nX4KAWTXA@mail.gmail.com>
<DM4PR19MB5978DFB2A7796FEE4DDE8A1AD3022@DM4PR19MB5978.namprd19.prod.outlook.com>
<CANzqJaAgBtJ7sFWR6oTF5Zdo-nHiZLcuC3GF-9xsG+xH6azCig@mail.gmail.com>
<DM4PR19MB5978678C4388CCC691EB705CD3002@DM4PR19MB5978.namprd19.prod.outlook.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@evernorth.com, sarwarmd02@outlook.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: RE: How to tune SQL performance of function based columns of a view
In-Reply-To: <e4a9a13d0d3d4c838f073f0634bead83@evernorth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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