From: Jeremy Haile <jhaile@fastmail.fm>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: Tue, 16 Jan 2007 17:20:53 -0500
Message-ID: <1168986053.16393.1169598917@webmail.messagingengine.com> (raw)
In-Reply-To: <26412.1168983547@sss.pgh.pa.us>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
<26412.1168983547@sss.pgh.pa.us>
Thanks Tom! Reducing random_page_cost to 2 did the trick for this
query. It now favors the index scan.
Even if this is a cached situation, I wouldn't expect a difference of 3
min vs 3 seconds.
Even if unrelated, do you think disk fragmentation would have negative
effects? Is it worth trying to defragment the drive on a regular basis
in Windows?
Jeremy Haile
On Tue, 16 Jan 2007 16:39:07 -0500, "Tom Lane" <tgl@sss.pgh.pa.us> said:
> "Jeremy Haile" <jhaile@fastmail.fm> writes:
> > Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq
> > scan over index scan even though index scan is faster (as shown by
> > disabling seqscan). Table is recently analyzed and row count estimates
> > seem to be in the ballpark.
>
> Try reducing random_page_cost a bit. Keep in mind that you are probably
> measuring a fully-cached situation here, if you repeated the test case.
> If your database fits into memory reasonably well then that's fine and
> you want to optimize for that case ... but otherwise you may find
> yourself pessimizing the actual behavior.
>
> regards, tom lane
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-performance@postgresql.org
Cc: jhaile@fastmail.fm, tgl@sss.pgh.pa.us
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
In-Reply-To: <1168986053.16393.1169598917@webmail.messagingengine.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