Subject: Re: Query much faster with enable_seqscan=0
From: "Joshua D. Drake" <jd@commandprompt.com>
Reply-To: jd@commandprompt.com
To: Ogden <lists@darkstatic.com>
Cc: Jesper Krogh <jesper@krogh.cc>, Kenneth Marshall <ktm@rice.edu>,
	pgsql-performance@postgresql.org
In-Reply-To: <3062DF4E-7460-4487-818A-BF77F176E501@darkstatic.com>
References: <0D5B9ED0-00C3-4EEB-8336-0B2F8A884620@darkstatic.com>
	<20100921180618.GI17152@aart.is.rice.edu>
	<D707045F-C689-403A-9729-8DBCC39F53A1@darkstatic.com>
	<4C98FEA2.8030607@krogh.cc>
	<3062DF4E-7460-4487-818A-BF77F176E501@darkstatic.com>
Content-Type: text/plain; charset="UTF-8"
Organization: Command Prompt, Inc.
Date: Tue, 21 Sep 2010 12:07:11 -0700
Message-ID: <1285096031.15919.68.camel@jd-desktop.unknown.charter.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2010-09-21 at 14:02 -0500, Ogden wrote:
> How odd, I set the following:
> 
> seq_page_cost = 1.0         
> random_page_cost = 2.0
> 
> And now the query runs in milliseconds as opposed to 14 seconds. Could this really be the change? I am running ANALYZE now - how often is it recommended to do this?

PostgreSQL's defaults are based on extremely small and some would say
(non production) size databases. As a matter of course I always
recommend bringing seq_page_cost and random_page_cost more in line.

However, you may want to try moving random_page_cost back to 4 and try
increasing cpu_tuple_cost instead.

Sincerely,

Joshua D. Drake

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt