I been doing some testing on various postgres SQL queries.

Testing often involves changing the syntax of queries, altering table joins, or occasional completely re-writing the query.

I've noticed that I sometimes get an 'apparent' big performance increase. I'll run a query, it'll take (say) 60 seconds to run, I'll make a minor change and it'll then take (say) 5 seconds to run.

At first I though that this was because my minor tweak had improved the performance. I've since realised that actually there must be some caching going on, (to see this, try running a 60 second query, and then running it again a few seconds later - it will always run quicker the 2nd time), I assume this is because the data has been cached locally somewhere so when the data needs reading a 2nd time it's already to hand.

I'm sure this a useful performance feature, but it does make it very hard to spot genuine performance improvements when tweaking a query. Is it possible to flush the cache before each execution to ensure that each test starts from the same position?

Thanks