From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Erik Jones <ejones(at)engineyard(dot)com> |
Cc: | Keresztury Balázs <balazs(at)gaslightmusic(dot)hu>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: forced sequential scan when condition has current_user |
Date: | 2010-01-05 02:09:00 |
Message-ID: | 603c8f071001041809g3b564310yb5cfb31ae7bf23b6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2010/1/4 Erik Jones <ejones(at)engineyard(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitri Girski | 2010-01-05 02:12:53 | pg_connect takes 3.0 seconds |
Previous Message | Steve Crawford | 2010-01-05 01:10:47 | Re: DB is slow until DB is reloaded |