Message-ID: <44C25FDD.8070003@modgraph-usa.com>
Date: Sat, 22 Jul 2006 10:26:53 -0700
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.4 (X11/20060516)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: robin.c.smith@bt.com, pgsql-performance@postgresql.org
Subject: Re: Forcing using index instead of sequential scan?
References: 
 <D012B9600FFB984AB18C0D53C475A4A081F51B@E03MVZ3-UKDY.domain1.systemhost.net>
	<26827.1153585341@sss.pgh.pa.us>
In-Reply-To: <26827.1153585341@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> The real issue here is caching across successive queries, an effect that
> Postgres doesn't deal with very well at the moment.  If you run these
> queries from a standing start (freshly booted machine) you'll likely
> find that the indexscan plan is indeed slower than the seqscan/hash
> plan, just like the planner thinks.

Here's a little trick I learned to speed up this test.

   find / -type f -exec grep foobar {} \;

This causes massive file-system activity and flushes all files that the kernel has cached.  If you run this between each Postgres test (let it run for a couple minutes), it gives you an apples-to-apples comparison between successive benchmarks, and eliminates the effects of caching.

If you run this as a regular user (NOT super-user or 'postgres'), you won't have permission to access your Postgres files, so you're guaranteed they'll be flushed from the cache.

Craig