Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E9365632BEC for ; Mon, 4 Jan 2010 17:59:38 -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 13106-01 for ; Mon, 4 Jan 2010 21:59:23 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.155]) by mail.postgresql.org (Postfix) with ESMTP id 7E5DB6322B5 for ; Mon, 4 Jan 2010 17:59:27 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 19so6566203fgg.1 for ; Mon, 04 Jan 2010 13:59:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=QnD/j3CK6ZHyjt6vkcKfu+XxEPRuPgyOWOn0Hogw8Fw=; b=r6jSQm19tft9bd8mEsWXueJFFeckKvZXFFuAYW8A/7HV8Q3JsqQOtfeN13tpZmOGw3 8Lvk4vode6z4xxwEBFt+OeO6OjyXSTkbORUYPvnBBmZSjP39+b4x1CMDIy9nUpYKrbRp 9wDsIHYBzgeRJfp5cMpgSF4jAUpD+is8WfZbY= 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:content-transfer-encoding; b=B/vkjUMRpHsrGoqJnKxXTGQNuRf84J3xKwbYbhfWIwa4RGNzsEkXhipXHXu30ADq9t kJf/WpzEPwbyUP0mCWH9ZkWZnZpEFYn6CDOdDkGgD3JY4D++jklRrlkmF8uACYUEW4zz 8S8ENIBIKamsXcCFdnTpu9bACm8DQI6Hi3tGk= MIME-Version: 1.0 Received: by 10.239.184.82 with SMTP id x18mr848692hbg.67.1262642365677; Mon, 04 Jan 2010 13:59:25 -0800 (PST) In-Reply-To: <000f01ca8d87$88e71f30$9ab55d90$@hu> References: <000f01ca8d87$88e71f30$9ab55d90$@hu> Date: Mon, 4 Jan 2010 16:59:25 -0500 Message-ID: <603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com> Subject: Re: forced sequential scan when condition has current_user From: Robert Haas To: =?ISO-8859-1?Q?Keresztury_Bal=E1zs?= Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.269 tagged_above=-10 required=5 tests=AWL=-2.734, BAYES_40=-0.185, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/24 X-Sequence-Number: 36904 2010/1/4 Keresztury Bal=E1zs : > just a small question: is it normal that PostgreSQL 8.4.1 always uses > sequential scanning on any table when there is a condition having the > constant "current_user"? Of course there is a btree index set on that tab= le, > but the DBMS just doesn't want to utilize it. When I replace current_user= to > any string, the planner uses the index normally. > > I can demonstrate it with the following simple query: > > SELECT psz.kotesszam FROM projekt.projektszervezet psz WHERE > psz.felhasznalo_id =3D current_user; > > Explain analyze: > > "Seq Scan on projektszervezet psz =A0(cost=3D0.00..255.07 rows=3D42 width= =3D9)" > " =A0Filter: ((felhasznalo_id)::name =3D "current_user"())" You've only got 42 rows in that table - PostgreSQL probably thinks a sequential scan will be faster. It might even be right. 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. But if you provide a particular value in the query then it will look at the stats and see what seems to make the most sense for that particular value. So using one of the more commonly-occuring value in the table might produce a sequential scan, while a less common value might lead to an index scan. ...Robert