From: Jona <jonanews@oismail.com>
To: pgsql-performance@postgresql.org
Subject: How to enhance the chance that data is in disk cache
Date: Mon, 13 Jun 2005 15:04:15 +0200
Message-ID: <42AD844F.3070700@oismail.com> (raw)
In-Reply-To: <42AD49BF.7050706@paradise.net.nz>
References: <579bc853ef3957c736c2773f13c444f7@implements.be>
<42ACF2DD.8090409@paradise.net.nz>
<e61732b6bc4e763c947b526c2c099fbc@implements.be>
<42AD49BF.7050706@paradise.net.nz>
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
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: jonanews@oismail.com
Subject: Re: How to enhance the chance that data is in disk cache
In-Reply-To: <42AD844F.3070700@oismail.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