From: "Dave Dutcher" <dave@tridecap.com>
To: "'cluster'" <skrald@amossen.dk>,
	<pgsql-performance@postgresql.org>
References: <fihgt1$1mmh$1@news.hub.org>
	<20071127165543.GY11978@crankycanuck.ca>
	<23999.1196184368@sss.pgh.pa.us> <fihn7h$2jrn$1@news.hub.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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcgxJi8sZf89/zRDRsm6anAoIx50ZQAADGDw
In-Reply-To: <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