Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
