Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is> writes:
> On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote:
>> Why not use the index scan for every row, is this a "limit" in the
>> planner ? I think there is something in the planner I don't understand :-)
> the planner will just use the plan it estimates will be fastest.
> because of how indexscans work in postgresql, in this case it would be
> slower than a tablescan (assuming the function really is volatile)
It has nothing to do with speed, it has to do with giving the correct
answer. We define "correct answer" as being the result you would get
from a naive interpretation of the SQL semantics --- that is, for every
row in the FROM table, actually execute the WHERE clause, and return the
rows where it produces TRUE.
As an example, a query like
SELECT * FROM mytable WHERE random() < 0.1;
should produce a random sampling of about one-tenth of the rows in mytable.
If we evaluated random() only once in this query, we would get either
all or none of the rows, clearly not the right answer.
An indexscan is a legal optimization only if the function(s) in the
WHERE clause are all STABLE or better. This is because the index access
code will only evaluate the righthand side of the "indexcol = something"
clause once, and then will use that value to descend the btree and
select matching index entries. We must be certain that this gives the
same result we would get from a seqscan.
The definition of STABLE that PostgreSQL uses was crafted specifically
to capture the property that a function is safe to use in an indexscan
qualification ...
regards, tom lane