To: Jona <jonanews@oismail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to enhance the chance that data is in disk cache 
In-reply-to: <42AD844F.3070700@oismail.com> 
References: <579bc853ef3957c736c2773f13c444f7@implements.be>
	<42ACF2DD.8090409@paradise.net.nz>
	<e61732b6bc4e763c947b526c2c099fbc@implements.be>
	<42AD49BF.7050706@paradise.net.nz> <42AD844F.3070700@oismail.com>
Comments: In-reply-to Jona <jonanews@oismail.com>
	message dated "Mon, 13 Jun 2005 15:04:15 +0200"
Date: Mon, 13 Jun 2005 10:51:39 -0400
Message-ID: <19818.1118674299@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jona <jonanews@oismail.com> writes:
> I have a query (please refer to 
> http://213.173.234.215:8080/get_content_plan.htm for the query as well 
> as query plan) that is slow when it's run the first time and fast(ish) 
> on all successive runs within a reasonable time period.

> This leads me to suspect that when the query is first run, all used data 
> have to be fetched from the disk where as once it has been run all data 
> is available in the OS's disk cache.

Sounds like that to me too.

> Is there anway to either enhance the chance that the data can be found 
> in the disk cache or allowing the database to fetch the data faster?

Run the query more often?

Also, that pile of INNER JOINs is forcing a probably-bad join order;
you need to think carefully about the order you want things joined in,
or else convert the query to non-JOIN syntax.  See the "Performance
Tips" chapter of the manual.

			regards, tom lane