0

We have docker containers (12) running on-prem machines, one of them is a postgres DB, on the same query we sometimes get peaks of 30+ seconds for a query that most of the time takes 2 seconds. The graph below displays the table select duration (so we assume it is not network/server/pools latency) We are trying to eliminate options here when looking for the cause of this temporary failure, should i be checking something specific in postges ? we use the default configuration. Can it be that postgres chooses different query strategy once in a while that has such an effect on performance? (we see different results on the exact same data sometimes)

EDIT-1 query runs on view, ( not Materialized View)

enter image description here

Improve this question
7
  • It can depends from values in query, statistic and data cardinality. I suggest you to post the query plan to investigate. Commented Jul 17, 2024 at 11:52
  • A default configuration gives you default performance. Not high performance. Commented Jul 17, 2024 at 13:18
  • 2
    Could you please share the results from explain (analyze, verbose, buffers, settings) for this SQL statement, the statement itself and all ddl for the tables and indexes involved? All in plain text Commented Jul 17, 2024 at 13:20
  • Your db machine config (CPU, RAM, disk) config would be good also! Commented Jul 17, 2024 at 13:31
  • It isn't clear what your graph is showing. Is that latency measured inside the database or at the client or somewhere else? Setting up autoexplain in the db server to log the plans of slow queries can let you see if plan changes are the culprit. Commented Jul 17, 2024 at 16:04
Related questions
1252
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
790
psql: FATAL: role "postgres" does not exist
606
'password authentication failed for user "postgres"'
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.