Date: Fri, 26 Oct 2012 09:55:13 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: =?iso-8859-1?Q?B=F6ckler?= Andreas <andy@boeckler.org>
Cc: Kevin Grittner <kgrittn@mail.com>, Jeff Janes <jeff.janes@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Query-Planer from 6seconds TO DAYS
Message-ID: <20121026145513.GB2872@aart.rice.edu>
References: <20121025182226.306900@gmx.com>
	<A5F9B84E-0EEB-4D8F-A107-C4D5AB36A4EA@boeckler.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <A5F9B84E-0EEB-4D8F-A107-C4D5AB36A4EA@boeckler.org>
User-Agent: Mutt/1.5.20 (2009-12-10)
Content-Transfer-Encoding: quoted-printable

On Fri, Oct 26, 2012 at 04:37:33PM +0200, B=C3=B6ckler Andreas wrote:
> Hi,
>=20
>=20
> Am 25.10.2012 um 20:22 schrieb Kevin Grittner:
>=20
> >=20
> > 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.
> Sure my effective_cache_size 10 GB
> But my right Table has the size of 1.2 TB (yeah Terra) at the moment (p=
artitioned a 40GB slices) and has 3 * 10^9 records
>=20
> My left table has only the size of 227MB and 1million records. Peanuts.
> > I would see how the workload behaves with the following settings:
> >=20
> > effective_cache_size =3D <your shared_buffers setting plus what the O=
S
> >                        shows as cached pages>
> > seq_page_cost =3D 1
> > random_page_cost =3D 2
> > cpu_tuple_cost =3D 0.05
> >=20
> > 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.
>=20
>  i've played with that already =E2=80=A6.
>=20
> NESTED LOOP -> GOOD
> SEQSCAN -> VERY BAD
>=20
> SET random_page_cost =3D 4;
> 2012-08-14' AND '2012-08-30' -> NESTED LOOP
> 2012-08-13' AND '2012-08-30' -> SEQSCAN
> SET random_page_cost =3D 2;
> 2012-08-14' AND '2012-08-30' -> NESTED LOOP
> 2012-08-07' AND '2012-08-30' -> NESTED LOOP
> 2012-08-06' AND '2012-08-30' -> SEQSCAN
> SET random_page_cost =3D 1;
> 2012-08-14' AND '2012-08-30' -> NESTED LOOP
> 2012-08-07' AND '2012-08-30' -> NESTED LOOP
> 2012-07-07' AND '2012-08-30' -> NESTED LOOP
> 2012-07-06' AND '2012-08-30' -> SEQSCAN
>=20
> The thing is ..
> - You can alter what you want. The planner will switch at a certain tim=
e range.
> - There is not one case, where the SEQSCAN-Method will be better .. It'=
s not possible.
>=20
> So the only way to tell the planner that he's doomed is=20
> SET enable_seqscan=3D0
> which is not very elegant. (Query Hints would be BTW jehovah!)
>=20
> You would be forced to write something like this:
> var lastValueEnable_seqscan =3D "SHOw enable_seqscan"
> SET enable_seqscan=3D0;
> SELECT ...
> SET enable_seqscan=3DlastValueEnable_seqscan;
>=20
> Kind regards
>=20
> Andy
>=20

Hi Andy,

You have the sequential_page_cost =3D 1 which is better than or equal to
the random_page_cost in all of your examples. It sounds like you need
a sequential_page_cost of 5, 10, 20 or more.

Regards,
Ken