Skip site navigation (1) Skip section navigation (2)

Occasionally slow queries - should I be concerned?

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Occasionally slow queries - should I be concerned?
Date: 2012-07-26 19:19:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: sfpug
I have a few queries that show up repeatedly in my pgsi/pg_fouine 
reports.  One of them is a 4-way join SELECT that is called hundreds of 
times per day.  When I run it myself, it executes in 18-40ms depending 
on the query parameters but the logs show a small percentage of those 
runs, say 10 times a day, the query runs two orders of magnitude slower 
in the 1100-2500ms range.

Is this normal?  The box has 16gb of RAM and the database base folder is 
12GB in size so I believe the database fits in memory.  The 
postgresql.conf settings are:

shared_buffers = 4GB
work_mem = 8MB
maintenance_work_mem = 256MB
effective_cache_size = 14GB

Normal behavior I should ignore or worth investigating?


PS - I do have logging enabled over 200ms and auto_explain enabled over 
500ms; not sure if that would factor in here?


sfpug by date

Next:From: Steve CrawfordDate: 2012-07-26 19:47:20
Subject: Re: Occasionally slow queries - should I be concerned?
Previous:From: Adrian KlaverDate: 2012-05-11 19:39:18
Subject: Re: [SQL] pg_dump: aborting because of server version mismatch

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group