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 <cje...@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 >
