To: "Joel Fradkin" <jfradkin@wazagua.com>
Cc: "'Richard Huxton'" <dev@archonet.com>, <gsstark@mit.edu>,
	<pgsql-sql@postgresql.org>, <ac@wazagua.com>,
	"Steve Goldsmith" <SGoldsmith@fcci-group.com>
Subject: Re: same question little different test MSSQL vrs Postgres
References: <000001c503c4$816b1880$797ba8c0@jfradkin>
In-Reply-To: <000001c503c4$816b1880$797ba8c0@jfradkin>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 26 Jan 2005 11:50:06 -0500
Message-ID: <87r7k8yw1t.fsf@stark.xeocode.com>
Lines: 33
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Joel Fradkin" <jfradkin@wazagua.com> writes:

> I tried the SET ENABLE_SEQSCAN=FALSE;
> And the result took 29 secs instead of 117.
> 
> After playing around with the cache and buffers etc I see I am no longer
> doing any swapping (not sure how I got the 100 sec response might have been
> shared buffers set higher, been goofing around with it all morning).

If it's swapping you're definitely going to get bad results. You really want
the *majority* of RAM left free for the OS to cache disk data.

> My worry here is it should obviously use an index scan so something is not
> setup correctly yet. I don't want to second guess the analyzer (or is this a
> normal thing?)

No that's not obvious. 22k out of 344k is a selectivity of 6.6% which is
probably about borderline. The optimizer is estimating even worse at 10.9%
which isn't far off but puts it well out of the range for an index scan.

If you really want to get postgres using an index scan you'll have to a)
improve the estimate using "alter table tblcase alter column clientnum set
statistics" to raise the statistics target for that column and reanalyze. 

And b) lower random_page_cost. random_page_cost tells postgres how much slower
indexes are than table scans and at the default setting it accurately
represents most disk hardware. If your database fits within RAM and is often
cached then you might have to lower it to model that fact. But you shouldn't
do it based on a single query like this.


-- 
greg