DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=sender:from:to:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:content-language:thread-index;
	b=krrYyqpGe1Vg8yBmLbeZAvfyBEgCNAxEwVPIyL3JhBqEmZ62i1vd+yu9NBuZxbNFCu
	jITQ2HAE7iprGmTTQMkO868YVA4QgpP7sHH3XbTy5Oek6UrFkJS27zKIk6dKR+mErc1w
	athHPhD7bJR+sxkc+Orkvx10o66w29Ko4EdTQ=
From: =?iso-8859-2?Q?Keresztury_Bal=E1zs?= <balazs@gaslightmusic.hu>
To: <pgsql-performance@postgresql.org>
References: <000f01ca8d87$88e71f30$9ab55d90$@hu>
	<603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com>
In-Reply-To: <603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com>
Subject: Re: forced sequential scan when condition has current_user
Date: Mon, 4 Jan 2010 23:49:24 +0100
Message-ID: <001101ca8d90$2a892b00$7f9b8100$@hu>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Content-Language: hu
Thread-Index: AcqNiTCq/mdmGbqiQo+k7Al6UQb6ugABStHA

Actually table itself has ~8000 records. I don't know why does it report =
42
rows, since there is not even a matching row in the table for this =
specific
condition.. But as we all know, the universal answer for every question =
is
42 ;) Autovacuum is on, and I also did some vacuuming before I started =
to
play with this query.

I could implement a function into my application to replace current_user =
to
the actual username, but it just doesn't worth it. By the way, replacing
current_user to a text constant reduces cost from 255->72, so there is a
significant difference. Don't you think this is actually a bug, not a
feature?

balazs

-----Original Message-----
From: Robert Haas [mailto:robertmhaas@gmail.com]=20
Sent: Monday, January 04, 2010 10:59 PM
To: Keresztury Bal=E1zs
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] forced sequential scan when condition has
current_user

2010/1/4 Keresztury Bal=E1zs <balazs@gaslightmusic.hu>:
> 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
table,
> 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