Le lun. 7 oct. 2024 à 02:41, Michael Paquier <michael@paquier.xyz> a écrit :
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.
We need granularity because we have granularity in the config. There is pg_stat_database because it gives the whole picture and it is easier to monitor. And then, there is pg_stat_statements to analyze problematic statements. And finally there is pg_stat_all* because you can set parallel_workers on a specific table.
Anyway, offering various ways of getting the same information is not unheard of. Pretty much like temp_files/temp_bytes in pg_stat_database, temp_blks_read/temp_blks_written in pg_stat_statements and log_temp_files in log files if you ask me :)
