From: Robert Haas <robertmhaas@gmail.com>
To: Keresztury Balázs <balazs@gaslightmusic.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: forced sequential scan when condition has current_user
Date: Mon, 4 Jan 2010 16:59:25 -0500
Message-ID: <603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com> (raw)
In-Reply-To: <000f01ca8d87$88e71f30$9ab55d90$@hu>
References: <000f01ca8d87$88e71f30$9ab55d90$@hu>
2010/1/4 Keresztury Balázs <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 = current_user;
>
> Explain analyze:
>
> "Seq Scan on projektszervezet psz (cost=0.00..255.07 rows=42 width=9)"
> " Filter: ((felhasznalo_id)::name = "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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, balazs@gaslightmusic.hu
Subject: Re: forced sequential scan when condition has current_user
In-Reply-To: <603c8f071001041359u58159c49k10587c7c46424672@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox