From: Dave Dutcher <dave@tridecap.com>
To: 'cluster' <skrald@amossen.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Query only slow on first run
Date: Tue, 27 Nov 2007 13:04:28 -0600
Message-ID: <056501c83128$55d58a90$8e00a8c0@tridecap.com> (raw)
In-Reply-To: <fihn7h$2jrn$1@news.hub.org>
References: <fihgt1$1mmh$1@news.hub.org>
<20071127165543.GY11978@crankycanuck.ca>
<23999.1196184368@sss.pgh.pa.us>
<fihn7h$2jrn$1@news.hub.org>
> -----Original Message-----
> From: cluster
>
> >> Probably by buying much faster disk hardware.
> > Or buy more RAM, so that the data can stay cached.
>
> So the only problem here is lack of RAM and/or disk speed?
I don't think you can reach that conclusion yet. Like everybody said the
reason the query was faster the second time was that the disk pages were
cached in RAM, and pulling the data out of RAM is way faster than disk. If
I were you, I would try to optimize the query for when the disk pages aren't
in RAM. In order to test the query without having anything cached you need
to clear out Postgres's shared buffers and the OS cache. That can be
tricky, but it may be as easy as running a big select on another table.
As for optimizing the query, I noticed that all three joins are done by
nested loops. I wonder if another join method would be faster. Have you
analyzed all the tables? You aren't disabling hash joins or merge joins are
you? If you aren't, then as a test I would try disabling nested loops by
doing "set enable_nestloop=false" and see if the query is any faster for
you. If it is faster without nested loops, then you might need to look into
changing some settings.
Dave
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, skrald@amossen.dk
Subject: Re: Query only slow on first run
In-Reply-To: <056501c83128$55d58a90$8e00a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox