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: 5011983D.20503@pukkasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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?

Brian

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

Responses

Browse sfpug by date

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