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