Message-ID: <27273ef8-e211-4ad9-ba55-d3947bd179b8@postgrespro.ru>
Date: Tue, 8 Oct 2024 18:20:17 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add parallel columns for seq scan and index scan on
 pg_stat_all_tables and _indexes
To: Michael Paquier <michael@paquier.xyz>
Cc: Guillaume Lelarge <guillaume@lelarge.info>,
 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CAECtzeXXuMkw-RVGTWvHGOJsmFdsRY+jK0ndQa80sw46y2uvVQ@mail.gmail.com>
 <Ztgejz8q7NBdcReD@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAECtzeUJ2y1yZcdcHfzkFX=CCYV7RwnHfwhpHyTe-hrzaUgm0g@mail.gmail.com>
 <ZthsUUUU+MTvtKRB@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAECtzeXi2b+hc-=xFUSN2phvmenivOz0CO9fi9pLaaObpAVWHA@mail.gmail.com>
 <ZtlDSbo1pxTrKXQv@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAECtzeVdwg1tA53RABSMs9wOCAn6ROWo4LfDstA=bistGLMZ2A@mail.gmail.com>
 <CAECtzeXUA-gv6Y5_+-y+LHkcYXuxSFHhKgHeMd9KWRnH+47Ttg@mail.gmail.com>
 <09424f5e-4ddf-4830-a21f-161e0427f112@postgrespro.ru>
 <ZwMuQHt42IyMDyxD@paquier.xyz>
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
In-Reply-To: <ZwMuQHt42IyMDyxD@paquier.xyz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/27273ef8-e211-4ad9-ba55-d3947bd179b8%40postgrespro.ru>
Precedence: bulk

On 07.10.2024 03:41, Michael Paquier wrote:
> On Mon, Oct 07, 2024 at 12:43:18AM +0300, Alena Rybakina wrote:
>> Maybe I'm not aware of the whole context of the thread and maybe my
>> questions will seem a bit stupid, but honestly
>> it's not entirely clear to me how this statistics will help to adjust the
>> number of parallel workers.
>> We may have situations when during overestimation of the cardinality during
>> query optimization a several number of parallel workers were unjustifiably
>> generated and vice versa -
>> due to a high workload only a few number of workers were generated.
>> How do we identify such cases so as not to increase or decrease the number
>> of parallel workers when it is not necessary?
> Well.  For spiky workloads, only these numbers are not going to help.
> If you can map them with the number of times a query related to these
> tables has been called, something that pg_stat_statements would be
> able to show more information about.
>
> FWIW, I have doubts that these numbers attached to this portion of the
> system are always useful.  For OLTP workloads, parallel workers would
> unlikely be spawned because even with JOINs we won't work with a high
> number of tuples that require them.  This could be interested with
> analytics, however complex query sequences mean that we'd still need
> to look at all the plans involving the relations where there is an
> unbalance of planned/spawned workers, because these can usually
> involve quite a few gather nodes.  At the end of the day, it seems to
> me that we would still need data that involves statements to track
> down specific plans that are starving.  If your application does not
> have that many statements, looking at individial plans is OK, but if
> you have hundreds of them to dig into, this is time-consuming and
> stats at table/index level don't offer data in terms of stuff that
> stands out and needs adjustments.
>
> And this is without the argument of bloating more the stats entries
> for each table, even if it matters less now that these stats are in
> shmem lately.

To be honest, it’s not entirely clear to me how these statistics will 
help in setting up parallel workers.

As I understand, we need additional tools for analytics, which are 
available in pg_stat_statements, but how then does it work? maybe you 
have the opportunity to demonstrate this?

-- 
Regards,
Alena Rybakina
Postgres Professional