Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kgrittn@mail.com>) id 1TRSms-00043b-8X
	for pgsql-performance@postgresql.org; Thu, 25 Oct 2012 19:08:14 +0000
Received: from mailout-us.gmx.com ([74.208.5.67])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <kgrittn@mail.com>) id 1TRSmk-0004ik-Nr
	for pgsql-performance@postgresql.org; Thu, 25 Oct 2012 19:08:13 +0000
Received: (qmail 28239 invoked by uid 0); 25 Oct 2012 18:22:28 -0000
Received: from 76.255.19.161 by rms-us006 with HTTP
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset="utf-8"
Date: Thu, 25 Oct 2012 14:22:26 -0400
From: "Kevin Grittner" <kgrittn@mail.com>
Message-ID: <20121025182226.306900@gmx.com>
MIME-Version: 1.0
Subject: Re: Query-Planer from 6seconds TO DAYS
To: =?iso-8859-1?Q?=22B=F6ckler_Andreas=22?= <andy@boeckler.org>, "Jeff Janes"
	<jeff.janes@gmail.com>
X-Authenticated: #138763884
X-Flags: 0001
X-Mailer: GMX.com Web Mailer
x-registered: 0
Content-Transfer-Encoding: 8bit
X-GMX-UID: Neb7cIYW3zOlOQ09LnwhK0l+IGRvb4Bb
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201210/306
X-Sequence-Number: 48265

Böckler Andreas wrote:

> I've played with seq_page_cost and enable_seqscan already, but you
> have to know the right values before SELECT to get good results ;)

The idea is to model actual costs on your system.  You don't show
your configuration or describe your hardware, but you show an
estimate of retrieving over 4000 rows through an index and describe a
response time of 4 seconds, so you must have some significant part of
the data cached.

I would see how the workload behaves with the following settings:

effective_cache_size = <your shared_buffers setting plus what the OS
                        shows as cached pages>
seq_page_cost = 1
random_page_cost = 2
cpu_tuple_cost = 0.05

You can set these in a session and check the plan with EXPLAIN. Try
various other important important queries with these settings and
variations on them. Once you hit the right factors to model your
actual costs, the optimizaer will make better choices without needing
to tinker with it each time.

-Kevin