X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D7AC152C2A
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  1 Aug 2005 20:30:29 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00446-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  1 Aug 2005 23:30:24 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.205])
	by svr1.postgresql.org (Postfix) with ESMTP id 1F80B529E1
	for <pgsql-performance@postgresql.org>;
	Mon,  1 Aug 2005 20:30:23 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id i4so1110508wra
	for <pgsql-performance@postgresql.org>;
	Mon, 01 Aug 2005 16:30:27 -0700 (PDT)
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:references;
	b=m4BqNLZ3iSOshYiEm5uKTke3ymmdGumSz2y8iZGcPJibEXrkHHNBwcaAN0qpJEYkIwpM77MHnfZ/IE+EX+f4wo8s8Uo1HO/qpEO44an0Bi+6tYVEDf2QMy3FVoN8PNc2H6eoiuZyG0RakgnHwOBVVyfjY2dU/322g43BmELMa9E=
Received: by 10.54.129.7 with SMTP id b7mr3320848wrd;
	Mon, 01 Aug 2005 16:30:26 -0700 (PDT)
Received: by 10.54.42.25 with HTTP; Mon, 1 Aug 2005 16:30:26 -0700 (PDT)
Message-ID: <fc5b04ca0508011630e745176@mail.gmail.com>
Date: Tue, 2 Aug 2005 01:30:26 +0200
From: Meetesh Karia <meetesh.karia@gmail.com>
Reply-To: meetesh.karia@alumni.duke.edu
To: Tobias Brox <tobias@nordicbet.com>
Subject: Re: Planner incorrectly choosing seq scan over index scan
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20050801223708.GE17649@tobias.lan>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3553_30794248.1122939026870"
References: <fc5b04ca05080115195d15c456@mail.gmail.com>
	<20050801223708.GE17649@tobias.lan>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.196 tagged_above=0 required=5 tests=AWL, HTML_30_40,
	HTML_MESSAGE, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200508/15
X-Sequence-Number: 13762

------=_Part_3553_30794248.1122939026870
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Are you referring to the statistics gathering target for ANALYZE? Based on=
=20
your email, I just tried the following and then re-ran the explain analyze=
=20
but got the same "incorrect" plan:

alter table candidates617004
alter column sourceId set statistics 1000,
alter column targetId set statistics 1000;
analyze candidates617004;

alter table lte_user
alter column user_id set statistics 1000;
analyze lte_user;

Thanks for your suggestion,
Meetesh

On 8/2/05, Tobias Brox <tobias@nordicbet.com> wrote:
>=20
> [Meetesh Karia - Tue at 12:19:27AM +0200]
> > We're using 8.0.3 and we're seeing a problem where the planner is=20
> choosing a
> > seq scan and hash join over an index scan. If I set enable_hashjoin to=
=20
> off,
> > then I get the plan I'm expecting and the query runs a lot faster. I've=
=20
> also
> > tried lowering the random page cost (even to 1) but the planner still
> > chooses to use the hash join.
>=20
> Have you tried increasing the statistics collection?
>=20
> --
> Tobias Brox, +47-91700050
> Nordicbet, IT dept
>

------=_Part_3553_30794248.1122939026870
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Are you referring to the statistics gathering target for ANALYZE?&nbsp;
Based on your email, I just tried the following and then re-ran the
explain analyze but got the same &quot;incorrect&quot; plan:<br>
<br>
alter table candidates617004<br>
&nbsp;&nbsp;&nbsp; alter column sourceId set statistics 1000,<br>
&nbsp;&nbsp;&nbsp; alter column targetId set statistics 1000;<br>
analyze candidates617004;<br>
<br>
alter table lte_user<br>
&nbsp;&nbsp;&nbsp; alter column user_id set statistics 1000;<br>
analyze lte_user;<br>
<br>Thanks for your suggestion,<br>
Meetesh<br>
<br><div><span class=3D"gmail_quote">On 8/2/05, <b class=3D"gmail_sendernam=
e">Tobias Brox</b> &lt;<a href=3D"mailto:tobias@nordicbet.com">tobias@nordi=
cbet.com</a>&gt; wrote:</span><blockquote class=3D"gmail_quote" style=3D"bo=
rder-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding=
-left: 1ex;">
[Meetesh Karia - Tue at 12:19:27AM +0200]<br>&gt; We're using 8.0.3 and we'=
re seeing a problem where the planner is choosing a<br>&gt; seq scan and ha=
sh join over an index scan. If I set enable_hashjoin to off,<br>&gt; then I=
 get the plan I'm expecting and the query runs a lot faster. I've also
<br>&gt; tried lowering the random page cost (even to 1) but the planner st=
ill<br>&gt; chooses to use the hash join.<br><br>Have you tried increasing =
the statistics collection?<br><br>--<br>Tobias Brox, +47-91700050<br>Nordic=
bet, IT dept
<br></blockquote></div><br>

------=_Part_3553_30794248.1122939026870--