From: Greg Stark <gsstark@mit.edu>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: 'Richard Huxton' <dev@archonet.com>
Cc: gsstark@mit.edu
Cc: pgsql-sql@postgresql.org
Cc: ac@wazagua.com
Cc: Steve Goldsmith <SGoldsmith@fcci-group.com>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: 26 Jan 2005 11:50:06 -0500
Message-ID: <87r7k8yw1t.fsf@stark.xeocode.com> (raw)
In-Reply-To: <000001c503c4$816b1880$797ba8c0@jfradkin>
References: <000001c503c4$816b1880$797ba8c0@jfradkin>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, jfradkin@wazagua.com, dev@archonet.com, ac@wazagua.com, SGoldsmith@fcci-group.com
Subject: Re: same question little different test MSSQL vrs Postgres
In-Reply-To: <87r7k8yw1t.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox