Skip site navigation (1) Skip section navigation (2)

Re: PG8.2.1 choosing slow seqscan over idx scan

From: "Jeremy Haile" <jhaile(at)fastmail(dot)fm>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: 2007-01-16 22:20:53
Message-ID: 1168986053.16393.1169598917@webmail.messagingengine.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)sss(dot)pgh(dot)pa(dot)us> said:
> "Jeremy Haile" <jhaile(at)fastmail(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Chad WagnerDate: 2007-01-16 22:44:53
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Previous:From: Tom LaneDate: 2007-01-16 21:39:07
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group