Re: Index optimization ? - Mailing list pgsql-general

Tom Lane wrote:

>No, you'd still end up with a seqscan, because this WHERE clause offers
>no chance of matching an index, and we don't do anything special with
>stable functions beyond trying to match them to index conditions.
>
>
So, the executer uses the (first) value to find the index to use for ALL
rows, and if this value change on each row, this can't be optimized and
a seq scan is initiated.

Is this not a problem for joins ?

>But consider something like
>
>    SELECT * FROM mytable WHERE keycol = int(random() * 1000);
>
>where keycol is indexed and contains integers 0..1000; let's say each
>such value appears ten times.  With a seqscan implementation (which I
>consider is what SQL defines the semantics to be) random() would be
>recomputed at each row and there would be about a 1/1000 chance of
>selecting each row.
>
This would demand a new index lookup for each row, right ?

>You might get more or less than exactly ten result
>rows, and they'd almost certainly contain different values of keycol.
>
>
This much i do understand :-)

>Now if random() were marked stable (and of course both multiply and
>int() are immutable), then the planner would consider an indexscan on
>keycol to be a valid optimization.  But that would produce
>distinguishably different results, because random() would be evaluated
>only once: you would always get exactly ten rows and they'd always all
>have the same keycol value.
>
>
I know why random (and currval) is not stabel, but I just don't
understand why a variable righthand result in seq scan, and not an index
scan, even when the data types match an index type.

To me it sounds like an index lookup is a one time a query (not per row)
thing, but I don't understand why. This can be because, this is the way
it turned up, but there is more possibly an aspect of SQL that I don't
know too much about.

>An index can basically implement conditions like "WHERE indexedcol =
>constant" --- it takes the constant value and searches the index for
>matches.  (Btrees can also do things like WHERE indexedcol <= constant,
>but let's just think about equality to keep things simple.)
>
:-)

>We can deal
>with a nonconstant righthand side, so long as it's okay to evaluate the
>value just once before the index starts to do its thing.  That
>assumption is what STABLE is all about.
>
>
So righthand value can't evaluate per row, and the value type of the
righthand expression can't be used as a index match.

I just hoped for the executer to work like this :

find indexedcol indexs

evaluate the righthand expression, and find its type (not value)

match the righthand value type and match it on index types (is both
sides integer)

if index is found use this together with the per row righthand value

or just use seq scan (I don't understand why, this works if indexes don't)

This is what I thought PG was doing :-)

Hope, I did not miss any important points.

/BL