Tom Lane wrote:
No, you'd still end up with a seqscan, because this WHERE clause offersSo, 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.
no chance of matching an index, and we don't do anything special with
stable functions beyond trying to match them to index conditions.
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 andI 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.
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.
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 dealSo righthand value can't evaluate per row, and the value type of the righthand expression can't be used as a index match.
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.
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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
