Hi there
I have a query (please refer to 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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to