DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=nS7OGBOEUKAPfFnTnCzFrHvejwG56TZeFXP+8+RwtFS/9r+STIjE8Tf2a/c0YGa5f/
	tFpcOIy3f+0TL/o8AdCAEyUSzggHd5QAzsinlR/fZQZnGwtBBeIhZzkUexzr8VfET2K6
	oC5tbFJ1sBInFvscYAohwiRRFJFHVCyyawB0Y=
MIME-Version: 1.0
In-Reply-To: <E56D1346-6E42-40D5-9CF1-239F5104F3A5@engineyard.com>
References: <000f01ca8d87$88e71f30$9ab55d90$@hu>
	<603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com>
	<E56D1346-6E42-40D5-9CF1-239F5104F3A5@engineyard.com>
Date: Mon, 4 Jan 2010 21:09:00 -0500
Message-ID: <603c8f071001041809g3b564310yb5cfb31ae7bf23b6@mail.gmail.com>
Subject: Re: forced sequential scan when condition has current_user
From: Robert Haas <robertmhaas@gmail.com>
To: Erik Jones <ejones@engineyard.com>
Cc: =?ISO-8859-1?Q?Keresztury_Bal=E1zs?= <balazs@gaslightmusic.hu>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

2010/1/4 Erik Jones <ejones@engineyard.com>:
> On Jan 4, 2010, at 1:59 PM, Robert Haas wrote:
>> The thing is, PostgreSQL doesn't know at planning time what the value of
>> current_user() will be, so the plan can't depend on that; the planner
>> just takes its best shot.
>
> current_user() is a stable function and the manual is explicit that the result of stable function can be used in an index scan:

That's true, but what I said is also true.  It CAN be used in an index
scan, and on a sufficiently large table it WILL be used in an index
scan (I tried it).  But the planner doesn't automatically use an index
just because there is one; it tries to gauge whether that's the right
strategy.  Unfortunately, in cases where it is comparing to a function
rather than a constant, its estimates are not always terribly
accurate.

One thing I notice is that the OP has not included any information on
how fast the seqscan or index-scan actually is.  If the seqscan is
slower than the index-scan, then the OP might want to consider
adjusting the page cost parameters - EXPLAIN ANALYZE output for both
plans (perhaps obtained by temporarily setting enable_seqscan to
false) would be helpful in understanding what is happening.

...Robert