Re: Index optimization ?

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:

> Lets say, you have an query "select * from table where field = function()".

Maybe this would be clearer with a more egregious example of volatility.

Say you had a function odd() that returns 1 and 0 alternating. That is, it
returns 1 the first time it's called, 0 the second time it's called, then 1,
then 0, etc.

If you did "select * from tab where col = odd()" you would expect to get half
of the rows where col=0 or col=1. Of course since the order is unpredictable
there's no way to know which ones but you should still be pretty sure it'll be
half of the rows.

If Postgres used an index it would call odd(), which would return 1 because
it's the first time, and then Postgres would go look up the rows where col is
1 and return all of them. That's a very different behaviour from if the index
isn't used. If all the records have col=1 then you're getting all of the
records instead of half of them. If col=0 then you're getting none of them
instead of half of them.

--
greg