DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=F6GEESUg4xHYNlUCSmV9SK0dGMvszWV0gDoWOlCsFrsMAa5Zjsjr2nz8fzu0Yd/YX2bkjoZ55iKepmOVlRKjLTAL6NX4tppMEUmpeMZdI1FPOIOVVxh00iDHc1VAJarUaTCe1z5+19mTzgeOtcJsrA8QY1fC8TFNJdpLCenIVHA=
Message-ID: <1d219a6f05072810341d67c525@mail.gmail.com>
Date: Thu, 28 Jul 2005 13:34:54 -0400
From: Chris Hoover <revoohc@gmail.com>
Reply-To: Chris Hoover <revoohc@gmail.com>
To: Dan Harris <fbsd@drivefaster.net>
Subject: Re: Fwd: Help with view performance problem
Cc: pgsql-performance@postgresql.org
In-Reply-To: <B0A387FA-9B74-4DB9-9C9F-C8291965A55F@drivefaster.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <1d219a6f05072708122cf6abeb@mail.gmail.com>
	<1d219a6f050727092927746db1@mail.gmail.com>
	<1d219a6f0507280738791f0ea3@mail.gmail.com>
	<B0A387FA-9B74-4DB9-9C9F-C8291965A55F@drivefaster.net>

I'm alreading running at 1.5.  It looks like if I drop the
random_page_cost t0 1.39, it starts using the indexes.  Are there any
unseen issues with dropping the random_page_cost this low?

Thanks,

Chris

On 7/28/05, Dan Harris <fbsd@drivefaster.net> wrote:
>=20
> On Jul 28, 2005, at 8:38 AM, Chris Hoover wrote:
> >
> >
> > I did some more testing, and ran the explain analyze on the problem.
> > In my session I did a set enable_hashjoin =3D false and then ran the
> > analyze.  This caused it to use the indexes as I have been expecting
> > it to do.
> >
> > Now, how can I get it to use the indexes w/o manipulating the
> > environment?  What make postgresql want to sequentially scan and use a
> > hash join?
> >
> > thanks,
> >
> > Chris
> >
> > explain analyze with set_hashjoin=3Dfalse;
> > prob_db=3D#explain analyze select * from clm_com;
> >
> >
>=20
> I had something similar to this happen recently.  The planner was
> choosing a merge join and seq scan because my 'random_page_cost' was
> set too high.  I had it at 3 , and ended up settling at 1.8 to get it
> to correctly use my indices.  Once that change was in place, the
> planner did the 'right' thing for me.
>=20
> Not sure if this will help you, but it sounds similar.
>=20
> -Dan
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>