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

Re: Query much faster with enable_seqscan=0

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Ogden <lists(at)darkstatic(dot)com>
Cc: Jesper Krogh <jesper(at)krogh(dot)cc>, Kenneth Marshall <ktm(at)rice(dot)edu>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query much faster with enable_seqscan=0
Date: 2010-09-21 19:07:11
Message-ID: 1285096031.15919.68.camel@jd-desktop.unknown.charter.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2010-09-21 19:16:57
Subject: Re: Query much faster with enable_seqscan=0
Previous:From: OgdenDate: 2010-09-21 19:02:11
Subject: Re: Query much faster with enable_seqscan=0

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