From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Bo Lorentsen <bl(at)netgroup(dot)dk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index optimization ? |
Date: | 2005-01-16 16:48:08 |
Message-ID: | 20050116164727.GC12205@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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? Would it make sense do to do so?
>
> What you say is that PG can't see the difference between this
> "plus_random" and the "currval", right.
>
> But if I have a select (a quite strange one), like this :
>
> SELECT * FROM test_table WHERE id = plus_random( test_col );
>
> I don't understand the problem. The function always return an integer as
> specified in the function decl. so why not use the PK index for search,
> instead of using seq scan ? The value is totally unpredictable but it is
> still an integer and the pk index is still useful regarding performance !
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.
The above query can be interpreted as: for each row in test_table,
compare id against plus_random( test_col ). Now, in theory the
plus_random function needs to be evaluated for every row, each time
giving a different value, thus it may or may not match id.
You can see that with that interpretation an index on id doesn't help.
If you interpret the query so plus_random is evaluted only once, then
an index will help. If test_col is a column of the table then there is
no way an index can help you.
Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
From | Date | Subject | |
---|---|---|---|
Next Message | Ragnar Hafstað | 2005-01-16 16:55:41 | Re: Index optimization ? |
Previous Message | Bo Lorentsen | 2005-01-16 16:45:52 | Re: Index optimization ? |