Bo Lorentsen wrote:
Greg Stark wrote:
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
On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
You can, howevery, accelerate something like where f in (1,2,3,4). You
just scan the index 4 times, each time for a different value. Of course,
if the number of values becomes larger and larger, there is a point
where it's
On Tue, Jan 18, 2005 at 07:33:51PM -0600, Jim C. Nasby wrote:
On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
You can, howevery, accelerate something like where f in (1,2,3,4). You
just scan the index 4 times, each time for a different value. Of course,
if the number of
On Tue, Jan 18, 2005 at 11:03:22PM -0300, Alvaro Herrera wrote:
On Tue, Jan 18, 2005 at 07:33:51PM -0600, Jim C. Nasby wrote:
On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
You can, howevery, accelerate something like where f in (1,2,3,4). You
just scan the index 4
Florian G. Pflug wrote:
Because the _whole_ _point_ of an index is to find matching rows
_without_ scanning the whole table. IF you have to look at every row
anyway, then just might as well to an sequential scan.
I am sorry it took me this long to understand this, but I think I got it
now thanks
Greg Stark wrote:
I understand that, I just can't see why an index lookup can't be used on per
row basis.
Well, how would that work?
Well, good point, the per row is a set of data selected as a product
of the static part os the query (non volatile parts), the only thing
you can do with
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
Florian G. Pflug wrote:
Lets say, you have an query select * from table where field =
function(). Now, according to the sql-spec, you would have to
scan each row in table, call the function functio(), and compare the
result. If the result of the call to function() matches the value in
field, the
Greg Stark wrote:
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
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Okay, let's look at this a different way.
When you look at a volatile function or variable, let's say
CURRENT_TIMESTAMP (which returns the current date and time as of the
beginning of the transaction), you see a function or variable whose
value
Bo Lorentsen [EMAIL PROTECTED] writes:
To me it sounds like an index lookup is a one time a query (not per row)
thing, but I don't understand why.
I can't explain it any more clearly than Florian did:
http://archives.postgresql.org/pgsql-general/2005-01/msg00769.php
Bo Lorentsen [EMAIL PROTECTED] writes:
I understand that, I just can't see why an index lookup can't be used on per
row basis.
Well, how would that work?
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Tom Lane wrote:
http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
Ok, thanks I see why there is these three differant function types, but
I don't quite understand why the value from a volatile function, can't
be used as a index key. Is this because there is no return type
On Jan 17, 2005, at 0:25, Bo Lorentsen wrote:
Tom Lane wrote:
http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
Ok, thanks I see why there is these three differant function types,
but I don't quite understand why the value from a volatile function,
can't be used as a index key.
Michael Glaesemann wrote:
I don't believe it has necessarily anything to do with the return
type, but rather the return value. An index only works if you know
what the value is, and the return value for a volatile function is not
guaranteed to be the same for given parameters. Here's a
On Sun, 2005-01-16 at 16:25 +0100, Bo Lorentsen wrote:
[about a volatile function in a where clause not generating index scan]
Will the only possible way to fix this be to make a volatile function
with a return type (I know this is not possible now, but in theory) ?
this has nothing to do
Ragnar Hafstað wrote:
this has nothing to do with the return type. a volatile function is a
function that is not garanteed to return the same value given same
input parameters, (such as currval()).
when a volatile function is used thus:
SELECT * FROM mytable WHERE col=myvolatilefunc();
the
On Sun, Jan 16, 2005 at 05:30:22PM +0100, Bo Lorentsen wrote:
One could conceivably attempt to make a functional index using
plus_random(), but the result it gives every time is indeterminant.
How would you be able to usefully search for values in an index that
is based on this function?
On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote:
Ragnar Hafstað wrote:
when a volatile function is used thus:
SELECT * FROM mytable WHERE col=myvolatilefunc();
the planner must call the function once per table row, and assume
possibly different return values each time, so an indexscan
# kleptog@svana.org / 2005-01-16 17:48:08 +0100:
On Sun, Jan 16, 2005 at 05:30:22PM +0100, Bo Lorentsen wrote:
One could conceivably attempt to make a functional index using
plus_random(), but the result it gives every time is indeterminant.
How would you be able to usefully search for
Ragnar =?ISO-8859-1?Q?Hafsta=F0?= [EMAIL PROTECTED] 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
Martijn van Oosterhout wrote:
No, it depends on your interpretation of the query. Note, I'm not up
with the SQL standard so maybe it doesn't work like this, but this is
what I think the problem is.
I just try to learn, so that is ok :-) Tom gave me a solution that
works, so now I struggle to
Tom Lane wrote:
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
On Sun, 2005-01-16 at 14:11 -0500, Tom Lane wrote:
Ragnar =?ISO-8859-1?Q?Hafsta=F0?= [EMAIL PROTECTED] 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
Bo Lorentsen wrote:
So if the random function was stable, you either get all or none, as et
gets executed only ones ?
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
Bo Lorentsen [EMAIL PROTECTED] writes:
Tom Lane wrote:
SELECT * FROM mytable WHERE random() 0.1;
If we evaluated random() only once in this query, we would get either
all or none of the rows, clearly not the right answer.
So if the random function was stable, you either get all or
Florian G. Pflug [EMAIL PROTECTED] 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
Hi ...
In my quest to get rid of the oid dependency, i have made some new low
level code with the help from many nice people from this community
(thanks for that), but I still have one somewhat big problem.
I am running PG 7.4.6, btw.
I have a sale table that have a BIGSERIAL as primary key,
Bo Lorentsen [EMAIL PROTECTED] writes:
select * from sale where id = currval( 'sale_id_seq' );
This is not legally optimizable into an indexscan, because currval() is
a volatile function. (It's easy to construct cases where its value
actually does change from row to row --- just use a nextval()
On Sat, Jan 15, 2005 at 07:03:43PM +0100, Bo Lorentsen wrote:
select * from sale where id = currval( 'sale_id_seq' )::bigint;
But this still did not work (still using seq scan) :-(
currval() is volatile. See Function Volatility Categories in the
Extending SQL chapter of the documentation
On Sat, Jan 15, 2005 at 01:27:49PM -0500, Tom Lane wrote:
Bo Lorentsen [EMAIL PROTECTED] writes:
select * from sale where id = currval( 'sale_id_seq' );
This is not legally optimizable into an indexscan, because currval() is
a volatile function. (It's easy to construct cases where its
On Sat, Jan 15, 2005 at 03:11:22PM -0600, Jim C. Nasby wrote:
Would it make sense to have a version of currval that will only return
one value in a statement/transaction? So the first time it's called it
remembers what currval for that sequence is and always returns the same
value?
What
Tom Lane wrote:
This is not legally optimizable into an indexscan, because currval() is
a volatile function. (It's easy to construct cases where its value
actually does change from row to row --- just use a nextval() as well.)
I am not sure what you mean by a volatile function, and how this
Michael Fuhr wrote:
currval() is volatile. See Function Volatility Categories in the
Extending SQL chapter of the documentation and search the list
archives for past discussion of currval()'s volatility.
Hmm, I can't find that chapter in the 7.4 manual, or am I looking the
wrong place ? I
Bo Lorentsen [EMAIL PROTECTED] writes:
Do you have any idea to how I may learn more about function types, or is
this a read the source, luke thing (I am not sure I have time for that
right now) ?
http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
On Sat, Jan 15, 2005 at 11:28:08PM +0100, Bo Lorentsen wrote:
Michael Fuhr wrote:
currval() is volatile. See Function Volatility Categories in the
Extending SQL chapter of the documentation and search the list
archives for past discussion of currval()'s volatility.
Hmm, I can't find that
On Sat, Jan 15, 2005 at 06:34:11PM -0300, Alvaro Herrera wrote:
On Sat, Jan 15, 2005 at 03:11:22PM -0600, Jim C. Nasby wrote:
Would it make sense to have a version of currval that will only return
one value in a statement/transaction? So the first time it's called it
remembers what
37 matches
Mail list logo
