From: Robert Haas <robertmhaas@gmail.com>
To: Erik Jones <ejones@engineyard.com>
Cc: 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 21:09:00 -0500
Message-ID: <603c8f071001041809g3b564310yb5cfb31ae7bf23b6@mail.gmail.com> (raw)
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>
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
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, ejones@engineyard.com, balazs@gaslightmusic.hu
Subject: Re: forced sequential scan when condition has current_user
In-Reply-To: <603c8f071001041809g3b564310yb5cfb31ae7bf23b6@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