Subject: Re: track_planning causing performance regression
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, Peter Geoghegan <pg@bowt.ie>,
 Julien Rouhaud <rjuju123@gmail.com>, "Tharakan, Robins" <tharar@amazon.com>,
 pgsql-hackers@postgresql.org
References: <2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com>
 <CAOBaU_ZNAjSQEdWK9ERsv6zqhdRqB_C0dVv97BrKETV4Rv7gig@mail.gmail.com>
 <d4a15deb-8a85-b78c-b447-bc5df7c4a926@oss.nttdata.com>
 <CAH2-WzmVP4ibDDSipQi_BamMrPeB72X8iW0R-T9gA-ZToG=m9g@mail.gmail.com>
 <CAH2-WznCT6rZYGYE_Zxf1oYDZVMyXqm+SWRx+R2b5-DX11pmpQ@mail.gmail.com>
 <42a13b4c-e60c-c6e7-3475-8eff8050bed4@oss.nttdata.com>
 <CAH2-WzmYXtaeCtTEmU_37Eiu5AuRM3etinKFKo=posnLtcxe3g@mail.gmail.com>
 <bc8125cb-264e-5a48-7d1f-17b84df831ed@oss.nttdata.com>
 <CAFj8pRC9Jxa8r5i0TNBWLb8mzuaYzEoLq3QOvip0jVpHPOLbVA@mail.gmail.com>
 <646e5cae-5e8b-feaa-43dc-014bf355bf08@oss.nttdata.com>
 <20210418233615.GB7256@telsasoft.com>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <d4cb31e2-d43f-0811-8f10-cf8e2f052e5e@oss.nttdata.com>
Date: Mon, 19 Apr 2021 23:44:05 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:78.0)
 Gecko/20100101 Thunderbird/78.9.1
MIME-Version: 1.0
In-Reply-To: <20210418233615.GB7256@telsasoft.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/d4cb31e2-d43f-0811-8f10-cf8e2f052e5e%40oss.nttdata.com>
Precedence: bulk


On 2021/04/19 8:36, Justin Pryzby wrote:
> Reviewing this change which was committed last year as
> 321fa6a4a26c9b649a0fbec9fc8b019f19e62289
> 
> On Fri, Jul 03, 2020 at 03:57:38PM +0900, Fujii Masao wrote:
>> On 2020/07/03 13:05, Pavel Stehule wrote:
>>> pá 3. 7. 2020 v 4:39 odesílatel Fujii Masao <masao.fujii@oss.nttdata.com> napsal:
>>>
>>> Maybe there can be documented so enabling this option can have a negative impact on performance.
>>
>> Yes. What about adding either of the followings into the doc?
>>
>>      Enabling this parameter may incur a noticeable performance penalty.
>>
>> or
>>
>>      Enabling this parameter may incur a noticeable performance penalty,
>>      especially when a fewer kinds of queries are executed on many
>>      concurrent connections.
> 
> Something seems is wrong with this sentence, and I'm not sure what it's trying
> to say.  Is this right ?

pg_stat_statements users different spinlock for each kind of query.
So fewer kinds of queries many sessions execute, fewer spinlocks
they try to acquire. This may lead to spinlock contention and
significant performance degration. This is what the statement is
trying to say.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION