Message-ID: <361f63ed-2a1a-7b1f-65a2-d7cadee76937@garret.ru>
Date: Thu, 15 Jun 2023 22:49:23 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: Let's make PostgreSQL multi-threaded
Content-Language: en-US
To: Hannu Krosing <hannuk@google.com>, James Addison <jay@jp-hosting.net>
Cc: Pavel Borisov <pashkin.elfe@gmail.com>,
 Dilip Kumar <dilipbalaut@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi>
 <CAMT0RQR+NLJw6hmN88D7RPe=vUEr7DB52dKmS9tWPO_eX2Np0g@mail.gmail.com>
 <CAFiTN-vJqo4TSBpkQTJqhYz6CL0M=cPhQZUXnop1uDC47s2hBg@mail.gmail.com>
 <CALT9ZEH_ZT6Fv8KFmEPf2qM7g0Y0mdU-J4PQDVRN+vhn0CLSOQ@mail.gmail.com>
 <2c2665d2-c513-c12e-9097-9b1805bc2471@garret.ru>
 <CALDQ5NwfEA+s8O=G-frW6WfRA_yXj_AyhLf2Lb_ZgXWjH3nyEg@mail.gmail.com>
 <36f61a71-3bbb-b7b0-0d99-db5e69715af7@garret.ru>
 <CALDQ5NwotYMZtXA2z6EkBQ72jVBrfyrn-+a9xU8=w54VBQPOhg@mail.gmail.com>
 <CAMT0RQTRzDgVzj13zryKRCcwt5VvNwWcw_d+-dQQeh7GkF40Fg@mail.gmail.com>
From: Konstantin Knizhnik <knizhnik@garret.ru>
In-Reply-To: <CAMT0RQTRzDgVzj13zryKRCcwt5VvNwWcw_d+-dQQeh7GkF40Fg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/361f63ed-2a1a-7b1f-65a2-d7cadee76937%40garret.ru>
Precedence: bulk


On 15.06.2023 12:04 PM, Hannu Krosing wrote:
> So a fair bit of work but also a clearly defined benefits of
> 1) reduced memory usage
> 2) no need to rebuild caches for each new connection
> 3) no need to track PREPARE statements inside connection poolers.

Shared plan cache (not only prepared statements cache) also opens way to 
more sophisticated query optimizations.
Right now we are not performing some optimization (like constant 
expression folding) just because them increase time of processing normal 
queries.
This is why queries generated by ORMs or wizards, which can contain a 
lot of dumb stuff, are not well simplified  by Postgres.
With MS-Sql it is quite frequent that query execution time is much 
smaller than query optimization time.
Having shared plan cache allows us to spend more time in optimization 
without risk to degrade performance.