Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 10E5D633551 for ; Mon, 4 Jan 2010 19:21:07 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 27139-05 for ; Mon, 4 Jan 2010 23:20:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f192.google.com (mail-yx0-f192.google.com [209.85.210.192]) by mail.postgresql.org (Postfix) with ESMTP id 50A246333D2 for ; Mon, 4 Jan 2010 19:20:56 -0400 (AST) Received: by yxe30 with SMTP id 30so17828621yxe.29 for ; Mon, 04 Jan 2010 15:20:54 -0800 (PST) Received: by 10.90.17.31 with SMTP id 31mr4900173agq.2.1262646833903; Mon, 04 Jan 2010 15:13:53 -0800 (PST) Received: from ?10.0.1.9? (c-67-160-166-240.hsd1.or.comcast.net [67.160.166.240]) by mx.google.com with ESMTPS id 15sm7495845yxh.22.2010.01.04.15.13.45 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 04 Jan 2010 15:13:50 -0800 (PST) Subject: Re: forced sequential scan when condition has current_user Mime-Version: 1.0 (Apple Message framework v1077) Content-Type: text/plain; charset=us-ascii From: Erik Jones In-Reply-To: <603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com> Date: Mon, 4 Jan 2010 15:13:43 -0800 Cc: =?iso-8859-1?Q?Keresztury_Bal=E1zs?= , pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <000f01ca8d87$88e71f30$9ab55d90$@hu> <603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com> To: Robert Haas X-Mailer: Apple Mail (2.1077) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.164 tagged_above=-10 required=5 tests=AWL=-0.425, BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/30 X-Sequence-Number: 36910 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.=20 current_user() is a stable function and the manual is explicit that the = result of stable function can be used in an index scan: "A STABLE function cannot modify the database and is guaranteed to = return the same results given the same arguments for all rows within a = single statement. This category allows the optimizer to optimize = multiple calls of the function to a single call. In particular, it is = safe to use an expression containing such a function in an index scan = condition. (Since an index scan will evaluate the comparison value only = once, not once at each row, it is not valid to use a VOLATILE function = in an index scan condition.)" postgres=3D# select provolatile from pg_proc where proname =3D = 'current_user'; provolatile=20 ------------- s So, I think the OP's question is still valid. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k