Re: Index optimization ?

On Sun, 2005-01-16 at 14:11 -0500, Tom Lane wrote:
> 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.

I should not have used the word 'indexscan'. I just meant that it would
be less effective to use an index to look up each result of the volatile
function than using a tablescan. It was clear that the function would
have to be called for each row, but the OP was asking (I think) why
the index was not used.

gnari