How to optimize the execution of heavy queries?

Question

PostgreSQL

It is not the query correction itself and its refinement that is of interest, but the situation when you need to collect some statistics from the database and at the same time make sure that the system does not slow down too much.
Is it possible to make the query run longer, but use less CPU - maybe there are some ways to lower the priority of execution?

Are there any built-in cached mechanisms that can be said to request X to return data from the cache when calling it more than N minutes/hours?

CPU 6 cores.

Answer 1

Is it possible to make the query run longer, but use less CPU - maybe there are some ways to lower the priority of execution?

Normal nice. Process model, no problem to lower the priority of a particular process by means of the OS itself.

Are there built-in cached mechanisms that can be said to request X to return data from the cache when calling it more than N minutes/hours?

No, there is no query cache. And it won't.

a situation when you need to collect some statistics from the database and at the same time make sure that the system does not slow down too much

Don't mix OLAP and OLTP at all. If the base is important, then it will have a hot standby. There statistics and count.

Answer 2

Study parameters max_parallel_workers, max_parallel_workers_per_gather The
CPU isn't single-core, is it?