Re: Optimization Advice for High Resource Utilization PostgreSQL Query on OpenBSD

Hi Claudio,

Yes, I did run 'Explain Analyze' on the query to diagnose the performance issues. Based on the analysis, I created indexes on the relevant columns and ran a VACUUM on the tables. This resulted in an improvement, reducing CPU utilization from 40% to 30%.

I haven't yet delved into explicit Common Table Expressions (CTEs) or extensive query refactoring as this query is currently running in a production environment. While these optimizations are on my radar, implementing them requires careful consideration to avoid disrupting ongoing operations. I also considered creating indexes affecting views, but this requires creating materialized views first, which in turn necessitates extensive query refactoring. Given the production environment constraints, I opted to defer this step for now to avoid any disruptions.

However, I am not sure if these additional steps would bring the utilization significantly down to a point where more queries can be executed simultaneously.

Thank you for your input and assistance.

Best regards,

Kihaguru

On Tue, Jul 30, 2024 at 3:42 PM Claudio Jeker <cjeker@diehard.n-r-g.com> wrote:

On Tue, Jul 30, 2024 at 03:04:54PM +0300, Kihaguru Gathura wrote:
> Hi,
>
> I am seeking advice on optimizing a PostgreSQL query that is consuming a
> significant amount of CPU resources on my Dell PowerEdge T340 server. The
> server has an Intel Xeon E-2124 CPU @ 3.30GHz (4 cores, no Hyper-Threading)
> and 16GB RAM, running OpenBSD 7.3 (GENERIC.MP) #1125.
>
> The query in question occasionally utilizes around 40% of the CPU, with
> CPU1 and CPU2 being more utilized compared to CPU0 and CPU3. This suggests
> that the workload might not be well-balanced across all cores.
>
> I am monitoring the system to ensure that CPU usage by PostgreSQL does not
> lead to performance issues, especially if more queries are executed
> simultaneously. Other processes (such as httpd, relayd, etc.) are consuming
> minimal CPU time, indicating they are not contributing significantly to the
> overall load.
>
> The *top output* and *dmesg output* are included below. Given this setup,
> could someone please provide guidance on the best practices for optimizing
> PostgreSQL performance under OpenBSD? Are there specific kernel or
> PostgreSQL configurations that might help distribute the load more evenly
> across all CPU cores, or is this a case that requires a more powerful CPU?

Did you EXPLAIN ANALYZE your query to see why it performs so bad?
In other words: Are you using the right indexes?

--
:wq Claudio