Message-ID: <42AD844F.3070700@oismail.com>
Date: Mon, 13 Jun 2005 15:04:15 +0200
From: Jona <jonanews@oismail.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: How to enhance the chance that data is in disk cache
References: <579bc853ef3957c736c2773f13c444f7@implements.be>
	<42ACF2DD.8090409@paradise.net.nz>
	<e61732b6bc4e763c947b526c2c099fbc@implements.be>
	<42AD49BF.7050706@paradise.net.nz>
In-Reply-To: <42AD49BF.7050706@paradise.net.nz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi there
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.
That is, if the query is not run for like 30 min, execution time returns 
to the initial time.

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.
Comparing the execution times we're talking roughly a factor 35 in time 
difference, thus optimization would be handy.
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?
Is this what the CLUSTER command is for, if so, which tables would I 
need to cluster?
Or is my only option to de-normalize the table structure around this 
query to speed it up?

Furthermore, it seems the database spends the majority of its time in 
the loop marked with italic in the initial plan, any idea what it spends 
its time on there?

Database is PG 7.3.9 on RH ES 3.0, with Dual XEON 1.9GHz processors and 
2GB of RAM.
effective_cache_size = 100k
shared_buffers = 14k
random_page_cost = 3
default_statistics_target = 50
VACUUM ANALYZE runs every few hours, so statistics should be up to date.

Appreciate any input here.

Cheers
Jona