Slow 1st queries and RAM size

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Slow 1st queries and RAM size
Date: 2014-01-08 22:23:03
Message-ID: 52CDCFC7.6010907@pukkasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sfpug


Looking at pgfouine, I have slow queries each day, here's one that took
7s for which I just ran explain analyze:

QUERY PLAN
---------------------------------------------------
Sort (cost=32920.99..32922.31 rows=530 width=399) (actual
time=7133.321..7133.423 rows=637 loops=1)

Then, I run it again a min later and I get:

QUERY PLAN
--------------------------------------------------
Sort (cost=32920.99..32922.31 rows=530 width=399) (actual
time=236.774..236.864 rows=637 loops=1)

Is this the data not being in the filesystem cache on the first try and
it being so on the second?

How do I improve this? Right now, the database is just a little bit
bigger than RAM, so I could add more RAM, but that won't help with the
"first hit", will it?

I can improve my query, but will that help the order of magnitude
initial hit or is that purely a disk/hardware issue? The machine is a
dedicated Dell R410 with quad-core processors and 2 x 300GB RAID1
arrays, logs on one, data on the other, behind a Perc H700.

Brian

Responses

Browse sfpug by date

  From Date Subject
Next Message David Fetter 2014-01-08 22:39:11 Re: Slow 1st queries and RAM size
Previous Message Brian Ghidinelli 2014-01-08 04:15:36 Re: pg_upgrade on windows vs. linux